Skip to Main Content
  • Questions
  • Execute multiple ALTER statements inside EXECUTE IMMEDIATE

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sagar.

Asked: March 11, 2018 - 2:42 pm UTC

Last updated: March 13, 2018 - 2:11 am UTC

Version: 12.2.0.1.0

Viewed 1000+ times

You Asked

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..


with LiveSQL Test Case:

and Connor said...

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.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks!

Sagar, March 12, 2018 - 6:58 am UTC

Got your point.

Thanks Connor!!
Connor McDonald
March 13, 2018 - 2:11 am UTC

glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library