Hi Team
My use case is to store table metadata - PK, Unique, FK constraints and Indexes DDLs in a temp table.
Perform some operation on table, and at the end restore the stored metadata on the table.
When executing the PL/SQL block, i receive the following ORA exception:
Error report -
ORA-01735: invalid ALTER TABLE option
ORA-06512: at line 26
01735. 00000 - "invalid ALTER TABLE option"
*Cause:
*Action:
How to work around this issue?
Thanks in advance..
Thanks for the complete test case - makes life easy for us.
EXECUTE IMMEDIATE will not work with multiple ALTER statements. Because as you've no doubt discovered, the DDL is actually just a single row (with the 2 statements), eg I've prefixed the output with "*" so you can see its just a single 'row' (with carriage returns)
SQL> CREATE TABLE temp_store(table_name VARCHAR2(50), cons_details VARCHAR2(4000));
Table created.
SQL> CREATE TABLE tab1(id NUMBER, name VARCHAR2(100), CONSTRAINT pk_tab1_id PRIMARY KEY(id));
Table created.
SQL> CREATE TABLE tab2(id NUMBER, name VARCHAR2(100), CONSTRAINT pk_tab2_id PRIMARY KEY(id));
Table created.
SQL> CREATE TABLE tab3(id NUMBER, name VARCHAR2(100), int_id NUMBER, CONSTRAINT pk_tab3_id PRIMARY KEY(id), CONSTRAINT fk_tab1_id FOREIGN KEY(int_id) REFERENCES tab1(id), CONSTRAINT fk_tab2_id FOREIGN KEY(int_id) REFERENCES tab2(id));
Table created.
SQL>
SQL> set serverout on
SQL> DECLARE
2 v_strSQL VARCHAR2(32767);
3 TYPE t_arFKConsDDL IS TABLE OF VARCHAR2(32767);
4 v_arFKConsDDL t_arFKConsDDL;
5 BEGIN
6 delete temp_store;
7
8 INSERT INTO temp_store
9 SELECT t.table_name,
10 dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name)
11 FROM user_tables t
12 WHERE table_name = 'TAB3'
13 AND EXISTS (SELECT 1
14 FROM user_constraints
15 WHERE table_name = t.table_name
16 AND constraint_type = 'R');
17
18 SELECT cons_details
19 BULK COLLECT INTO v_arFKConsDDL
20 FROM temp_store;
21
22 dbms_output.put_line(v_arFKConsDDL.COUNT);
23
24 IF (v_arFKConsDDL.COUNT>0) THEN
25 FOR i IN v_arFKConsDDL.FIRST..v_arFKConsDDL.LAST
26 LOOP
27 v_strSQL := v_arFKConsDDL(i);
28 dbms_output.put_line('*'||v_strSQL);
29 --EXECUTE IMMEDIATE v_strSQL;
30 END LOOP;
31 END IF;
32 END;
33 /
1
*
ALTER TABLE "MCDONAC"."TAB3" ADD CONSTRAINT "FK_TAB1_ID" FOREIGN KEY ("INT_ID")
REFERENCES "MCDONAC"."TAB1" ("ID") ENABLE
ALTER TABLE "MCDONAC"."TAB3" ADD CONSTRAINT "FK_TAB2_ID" FOREIGN KEY ("INT_ID")
REFERENCES "MCDONAC"."TAB2" ("ID") ENABLE
PL/SQL procedure successfully completed.
SQL>
So you have a couple of options here. You could parse out the ALTER's from the existing output using some string analysis
SQL> set serverout on
SQL> DECLARE
2 v_strSQL VARCHAR2(32767);
3 TYPE t_arFKConsDDL IS TABLE OF VARCHAR2(32767);
4 v_arFKConsDDL t_arFKConsDDL;
5 l_idx int;
6 l_chunk varchar2(32000);
7 BEGIN
8 delete temp_store;
9
10 INSERT INTO temp_store
11 SELECT t.table_name,
12 dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name)
13 FROM user_tables t
14 WHERE table_name = 'TAB3'
15 AND EXISTS (SELECT 1
16 FROM user_constraints
17 WHERE table_name = t.table_name
18 AND constraint_type = 'R');
19
20 SELECT cons_details
21 BULK COLLECT INTO v_arFKConsDDL
22 FROM temp_store;
23
24 dbms_output.put_line(v_arFKConsDDL.COUNT);
25
26 IF (v_arFKConsDDL.COUNT>0) THEN
27 FOR i IN v_arFKConsDDL.FIRST..v_arFKConsDDL.LAST
28 LOOP
29 v_strSQL := v_arFKConsDDL(i);
30
31 l_chunk := v_strSQL;
32 loop
33 l_idx := instr(l_chunk,'ALTER ',1,2);
34 exit when l_idx = 0;
35 dbms_output.put_line('*'||substr(l_chunk,1,l_idx-1));
36 l_chunk := substr(l_chunk,l_idx);
37 end loop;
38 dbms_output.put_line('*'||l_chunk);
39 --EXECUTE IMMEDIATE v_strSQL;
40 END LOOP;
41 END IF;
42 END;
43 /
1
*
ALTER TABLE "MCDONAC"."TAB3" ADD CONSTRAINT "FK_TAB1_ID" FOREIGN KEY ("INT_ID")
REFERENCES "MCDONAC"."TAB1" ("ID") ENABLE
*ALTER TABLE "MCDONAC"."TAB3" ADD CONSTRAINT "FK_TAB2_ID" FOREIGN KEY ("INT_ID")
REFERENCES "MCDONAC"."TAB2" ("ID") ENABLE
PL/SQL procedure successfully completed.
or you could change the way you get the constraint DDL so that you get a row per DDL - see the *different* call to DBMS_METADATA
SQL> set serverout on
SQL> DECLARE
2 v_strSQL VARCHAR2(32767);
3 TYPE t_arFKConsDDL IS TABLE OF VARCHAR2(32767);
4 v_arFKConsDDL t_arFKConsDDL;
5 BEGIN
6 delete temp_store;
7 INSERT INTO temp_store
8 SELECT t.table_name,
9 dbms_metadata.get_ddl('REF_CONSTRAINT', constraint_name)
10 FROM user_constraints t
11 WHERE table_name = 'TAB3'
12 and constraint_type = 'R';
13
14 SELECT cons_details
15 BULK COLLECT INTO v_arFKConsDDL
16 FROM temp_store;
17
18 dbms_output.put_line(v_arFKConsDDL.COUNT);
19
20 IF (v_arFKConsDDL.COUNT>0) THEN
21 FOR i IN v_arFKConsDDL.FIRST..v_arFKConsDDL.LAST
22 LOOP
23 v_strSQL := v_arFKConsDDL(i);
24 dbms_output.put_line('*'||v_strSQL);
25 --EXECUTE IMMEDIATE v_strSQL;
26 END LOOP;
27 END IF;
28 END;
29 /
2
*
ALTER TABLE "MCDONAC"."TAB3" ADD CONSTRAINT "FK_TAB1_ID" FOREIGN KEY ("INT_ID")
REFERENCES "MCDONAC"."TAB1" ("ID") ENABLE
*
ALTER TABLE "MCDONAC"."TAB3" ADD CONSTRAINT "FK_TAB2_ID" FOREIGN KEY ("INT_ID")
REFERENCES "MCDONAC"."TAB2" ("ID") ENABLE
PL/SQL procedure successfully completed.