How to use dbms_compression.get_compression_ratio in 11g???

** Ensure you set compatible parameter to current version. i.e. atleast 11.*.*.*

create or replace procedure get_esti_comp(level number)
is
begin
declare BLKCNT_CMP BINARY_INTEGER;
BLKCNT_UNCMP BINARY_INTEGER;
ROW_CMP BINARY_INTEGER;
ROW_UNCMP BINARY_INTEGER;
CMP_RATIO NUMBER;
COMPTYPE_STR VARCHAR2(100);
ORG_TYPE NUMBER;
begin
case level
when 1 then org_type := DBMS_COMPRESSION.COMP_FOR_QUERY_LOW;
when 2 then org_type := DBMS_COMPRESSION.COMP_FOR_QUERY_HIGH;
when 3 then org_type := DBMS_COMPRESSION.COMP_FOR_ARCHIVE_LOW;
when 4 then org_type := DBMS_COMPRESSION.COMP_FOR_ARCHIVE_HIGH;
else org_type := DBMS_COMPRESSION.COMP_FOR_QUERY_LOW;
end case;
dbms_compression.get_compression_ratio('TABLESPACE_TBS','SCOTT','INVENTORY','',org_type, BLKCNT_CMP,BLKCNT_UNCMP,ROW_CMP,ROW_UNCMP, CMP_RATIO, COMPTYPE_STR);
dbms_output.put_line('COMPRESSED_TYPE = '||COMPTYPE_STR);
dbms_output.put_line('COMPRESSED_BLOCKS = '||BLKCNT_CMP);
dbms_output.put_line('UNCOMPRESSED_BLOCKS = '||BLKCNT_UNCMP);
dbms_output.put_line('COMPRESSED_ROWS = '||ROW_CMP);
dbms_output.put_line('UNCOMPRESSED_ROWS = '||ROW_UNCMP);
dbms_output.put_line('COMPRESSION_RATIO = '||CMP_RATIO);
end;
end;
/
SQL> execute get_ehcc_cr(1);
SQL> execute get_ehcc_cr(2);
SQL> execute get_ehcc_cr(3);
SQL> execute get_ehcc_cr(4);
Sample output :-

Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
COMPRESSED_TYPE = "Compress For Query Low"
COMPRESSED_BLOCKS = 8606
UNCOMPRESSED_BLOCKS = 27595
COMPRESSED_ROWS = 116
UNCOMPRESSED_ROWS = 39
COMPRESSION_RATIO = 3.2
PL/SQL procedure successfully completed.