Insert Using ODP.Net Does Not Compress Data In A Compressed Table (Doc ID 459234.1)

Last updated on FEBRUARY 23, 2025

Applies to:

Symptoms

Data inserted into a COMPRESSed table by a stored procedure called from ODP.Net, inserts the data uncompressed.

When the procedure is called from SQL*Plus; data is correctly compressed.
When the procedure is called from ODP.net; data is NOT compressed.

Replacing the stored procedure call with just the INSERT statement also
gives the same results.

simple example.

1. Create table:
CREATE TABLE LEE COMPRESS AS SELECT * FROM ALL_OBJECTS WHERE 1=0;

2.Create stored procedure
CREATE PROCEDURE P_LEE AS
BEGIN
INSERT /*+APPEND*/ INTO LEE SELECT * FROM ALL_OBJECTS;
COMMIT;
END;

3.Use SQL*Plus To execute the SP
4. Check the blocks used

SQL> SELECT BLOCKS FROM USER_SEGMENTS WHERE SEGMENT_NAME='LEE';

BLOCKS
256

5. Then invoke same SP from ODP.NET.
(truncate the table first)

SQL> SELECT BLOCKS FROM USER_SEGMENTS WHERE SEGMENT_NAME='LEE';

BLOCKS
640

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.