My goal in the following proc is to accomplish the following:
- Get index syntax of specific table
- Drop and create table with new data
- 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;
*/