ORA-02158: invalid CREATE INDEX option - when executing Collection in PL/SQL

Viewed 19

My goal in the following proc is to accomplish the following:

  1. Get index syntax of specific table
  2. Drop and create table with new data
  3. Create index for new table

I've been able to store index syntax in Collection variable IX_L with the help of BULK COLLECT INTO.

But I'm struggling with executing the CREATE INDEX that's stored there. When I loop through EXECUTE IMMEDIATE IX_L(i); I get an error ORA-02158: invalid CREATE INDEX option. But, when I execute the same exact text explicitly and not from withing the Collection variable it works fine, so it doesn't seem like there is a problem with my CREATE INDEX syntax.

I would appreciate any help.

CREATE OR REPLACE PROCEDURE MyProc 

AS



  V_SQL      CLOB;

  TYPE INDEX_TBL IS TABLE OF CLOB;

  IX_L   INDEX_TBL;

 
BEGIN

  V_SQL := '

    select

    ''CREATE '' || IX.UNIQUENESS ||'' INDEX '' || IX.OWNER || ''.'' || IX.INDEX_NAME

    || '' ON DWH.TBL ('' || IX_COLS.COLS || '');''

    from All_Indexes IX

    JOIN

         (SELECT TABLE_NAME,INDEX_NAME,listagg (COLUMN_NAME,'','') WITHIN GROUP (ORDER BY COLUMN_NAME) AS COLS

          FROM DBA_IND_COLUMNS T

          where 1=1

          AND t.TABLE_OWNER = ''DWH''

          AND t.TABLE_NAME = ''TBL''

          GROUP BY TABLE_NAME,INDEX_NAME)  IX_COLS

          ON IX.INDEX_NAME = IX_COLS.INDEX_NAME AND IX.TABLE_NAME = IX_COLS.TABLE_NAME

    where 1=1

    AND IX.OWNER = ''DWH''

    AND IX.TABLE_NAME = ''TBL''

    ';

    EXECUTE IMMEDIATE V_SQL BULK COLLECT INTO IX_L;

END;


 

BEGIN

   FOR i IN 1 .. IX_L.COUNT

   LOOP

        DBMS_OUTPUT.put_line(IX_L(i));

        EXECUTE IMMEDIATE IX_L(i);

   END LOOP;

   COMMIT;

END;

 

 

/*

BEGIN

  EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX DWH.I3032K_PK ON DWH.SH3032K_BANK_ACNT_DOCUMENTS (OBJECT_ID,SOURCE_ETL)';

END;

*/
1 Answers

Found the answer:

Needed to delete the ; at the SELECT, right before the from All_Indexes.

This would be correct:

select

''CREATE '' || IX.UNIQUENESS ||'' INDEX '' || IX.OWNER || ''.'' || IX.INDEX_NAME

|| '' ON DWH.TBL ('' || IX_COLS.COLS || '')''

from All_Indexes IX`
Related