4 Replies Latest reply: Apr 23, 2013 3:26 PM by Peter Gjelstrup RSS

    PLS-00221: 'COMPRESSION_RATIO' is not a procedure or is undefined

    user9338712
      Dear all,

      10.2.0.5 on Solaris 10

      I created a function and when am executing , am getting the error.. PLS-00221: 'COMPRESSION_RATIO' is not a procedure or is undefined
      SQL> create or replace function compression_ratio (tabname varchar2)
        2  return number is
        3  pragma autonomous_transaction;
        4  -- sample percentage
        5  pct number := 0.000099;
        6  -- original block count (should be less than 10k)
      blkcnt number := 0;
        7    8  -- compressed block count
        9  blkcntc number;
       10  begin
       11  execute immediate ' create table TEMP_UNCOMPRESSED pctfree 0
       12  as select * from ' || tabname ||
       13  ' where rownum < 1';
       14  while ((pct < 100) and (blkcnt < 1000)) loop
       15  execute immediate 'truncate table TEMP_UNCOMPRESSED';
       16  execute immediate 'insert into TEMP_UNCOMPRESSED select * from ' ||
       17  tabname || ' sample block (' || pct || ',10)';
       18  execute immediate 'select
       19  count(distinct(dbms_rowid.rowid_block_number(rowid)))
       20  from TEMP_UNCOMPRESSED' into blkcnt;
       21  pct := pct * 10;
       22  end loop;
       23  execute immediate 'create table TEMP_COMPRESSED compress as
       24  select * from TEMP_UNCOMPRESSED';
       25  execute immediate 'select
       26  count(distinct(dbms_rowid.rowid_block_number(rowid)))
      from TEMP_COMPRESSED' into blkcntc;
       27   28  execute immediate 'drop table TEMP_COMPRESSED';
       29  execute immediate 'drop table TEMP_UNCOMPRESSED';
       30  return (blkcnt/blkcntc);
       31  end;
       32  /
      
      Function created.
      
      SQL>  set serveroutput on
      SQL> exec compression_ratio('IA_LOG');
      BEGIN compression_ratio('IA_LOG'); END;
      
            *
      ERROR at line 1:
      ORA-06550: line 1, column 7:
      PLS-00221: 'COMPRESSION_RATIO' is not a procedure or is undefined
      ORA-06550: line 1, column 7:
      PL/SQL: Statement ignored
      Any idea what am missing

      Edited by: user9338712 on Apr 23, 2013 1:02 PM