Insert Using ODP.Net Does Not Compress Data In A Compressed Table

(Doc ID 459234.1)

Last updated on JULY 05, 2017

Applies to:

Oracle Data Provider for .NET - Version: 9.2.0.1.0 to 10.2.0.3.0
Information in this document applies to any platform.

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

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms