Accessing Vararray LIMIT in SQL within PL/SQL

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Wed, 28 Feb 2007 09:54:11 +0100
Message-ID: <54ku91F21gggmU1@mid.individual.net>

Hi,

I try to reduce redundancy. If you change the line with comment "-- ERROR:" to refer to the vararray's LIMIT you get ORA-00904:

LINE/COL ERROR

-------- -----------------------------------------------------------------
22/5     PL/SQL: SQL Statement ignored
28/32    PL/SQL: ORA-00904: "COMPRESS_STATS"."IND_COLS"."LIMIT": invalid
          identifier

Using a constant for the max varray length doesn't help, since it cannot be used in the declaration of the varray type. I tried also BULK COLLECT with LIMIT but this is only allowed for FETCH. I can get it to work by using "last_index" but this seems clunky. Is there any other way to get Oracle accept the reference to "COMPRESS_STATS"."IND_COLS"."LIMIT" in the query?

Any ideas? Thank you!

Kind regards

        robert

PS: If somebody wonders what this function does: it generates an SQL statement which will output counts of unique occurrences of all combinations of leading columns from an index to help in determining whether compressing this index is a good idea.

CREATE OR REPLACE FUNCTION COMPRESS_STATS(

     table_name  IN VARCHAR2,
     index_name  IN VARCHAR2,
     max_columns IN NUMBER   DEFAULT NULL,
     owner       IN VARCHAR2 DEFAULT USER

)
RETURN VARCHAR2
AS

• index_limit CONSTANT NUMBER := 1000;

     TYPE column_list IS VARRAY(1000) OF VARCHAR2(100) NOT NULL;

     ind_cols    column_list;
     sql_code    VARCHAR2(4000);
     i1          NUMBER;
     i2          NUMBER;
     last_index  NUMBER;
BEGIN


     -- get all column names of this index

     -- OK: last_index := ind_cols.LIMIT;


     SELECT column_name
         BULK COLLECT INTO COMPRESS_STATS.ind_cols
         FROM all_ind_columns
         WHERE index_owner = COMPRESS_STATS.owner
         AND index_name = COMPRESS_STATS.index_name
         AND table_name = COMPRESS_STATS.table_name
         AND column_position <= 1000
             -- ERROR: COMPRESS_STATS.ind_cols.LIMIT
             -- OK: AND column_position <= COMPRESS_STATS.last_index
         ORDER BY index_name ASC, column_position ASC;



     -- did we limit the number of columns to output?

     IF max_columns IS NOT NULL AND ind_cols.COUNT > max_columns THEN
         last_index := max_columns;
     ELSE
         last_index := ind_cols.COUNT;
     END IF;



     -- create the SQL statement that will yield stats

     -- start with SELECT

     sql_code := 'SELECT COUNT(*) "Records"';



     -- add all leading lists of column names

     FOR i1 IN ind_cols.FIRST .. last_index -- ind_cols.LAST
     LOOP
         sql_code := sql_code || ', COUNT(DISTINCT ';

         FOR i2 IN ind_cols.FIRST .. i1
         LOOP
             IF i2 > ind_cols.FIRST THEN
                 sql_code := sql_code || ' || ''-'' || ';
             END IF;

             sql_code := sql_code || '"' || ind_cols(i2) || '"';
         END LOOP;

         sql_code := sql_code || ') "COMPRESS ' || i1 || '"';
     END LOOP;



     -- add FROM clause

     sql_code := sql_code || ' FROM "' ||
         owner || '"."' || table_name || '"';

     RETURN sql_code;

END COMPRESS_STATS; Received on Wed Feb 28 2007 - 02:54:11 CST