How do I execute clob in execute immediate

Question

I have a table with one row and clob as column (whose size is 5239). This column contains a table script extracted with the help of dbms_metadata.get_ddl. Along with table ddl it also extracts the primary key constraint and unique index script.

When I try to execute the script in my plsql block with execute immediate, it throws the following error:

ora-00922: missing or invalid option

Can somebody help me with what might be going wrong?

DECLARE lr_ddl CLOB;

BEGIN
    SELECT TEXT INTO lr_ddl FROM DUMMY_1;
    EXECUTE IMMEDIATE lr_nvm_ddl;

    EXCEPTION WHEN OTHERS 
              THEN raise_application_error(-20001, chr(10)||' Failed ' || SQLERRM);
END;

Below is the script that I have in clob and I am trying to execute with execute immediate in my anonymous block.

 CREATE TABLE abc_PROD.travel_hist
   (  travel_NO NUMBER NOT NULL ENABLE, 
  OBJECT_ID VARCHAR2(32) NOT NULL ENABLE, 
  flight_NO NUMBER, 
  LIFT_ACCOUNT VARCHAR2(32), 
  CARR_id VARCHAR2(32), 
  V_1 NUMBER, 
  V_2 NUMBER, 
  V_3 NUMBER, 
  V_4 NUMBER, 
  V_5 NUMBER, 
  V_6 NUMBER, 
  V_7 NUMBER, 
  V_8 NUMBER, 
  V_9 NUMBER, 
  V_10 NUMBER, 
  V_11 NUMBER, 
  V_12 NUMBER, 
  V_13 NUMBER, 
  V_14 NUMBER, 
  V_15 NUMBER, 
  V_16 NUMBER, 
  V_17 NUMBER, 
  V_18 NUMBER, 
  V_19 NUMBER, 
  V_20 NUMBER, 
  V_21 NUMBER, 
  V_22 NUMBER, 
  V_23 NUMBER, 
  V_24 NUMBER, 
  V_25 NUMBER, 
  V_26 NUMBER, 
  V_27 NUMBER, 
  V_28 NUMBER, 
  V_29 NUMBER, 
  V_30 NUMBER, 
  T_1 VARCHAR2(16), 
  T_2 VARCHAR2(32), 
  T_3 VARCHAR2(240), 
  T_4 VARCHAR2(2000), 
  T_5 VARCHAR2(240), 
  T_6 VARCHAR2(240), 
  T_7 VARCHAR2(240), 
  T_8 VARCHAR2(240), 
  T_9 VARCHAR2(240), 
  T_10 VARCHAR2(240), 
  T_11 VARCHAR2(2000), 
  T_12 VARCHAR2(2000), 
  T_13 VARCHAR2(2000), 
  T_14 VARCHAR2(2000), 
  T_15 VARCHAR2(2000), 
  T_16 VARCHAR2(2000), 
  T_17 VARCHAR2(2000), 
  T_18 VARCHAR2(2000), 
  T_19 VARCHAR2(2000), 
  T_20 VARCHAR2(2000), 
  DATE_1 DATE, 
  DATE_2 DATE, 
  DATE_3 DATE, 
  DATE_4 DATE, 
  DATE_5 DATE, 
  DATE_6 DATE, 
  DATE_7 DATE
   ) ;
  CREATE UNIQUE INDEX abc_PROD.PK_TRAVEL ON abc_PROD.travel_hist (travel_NO) 
  ;
ALTER TABLE abc_PROD.travel_hist ADD CONSTRAINT PK_TRAVEL PRIMARY KEY (travel_NO)
  USING INDEX abc_PROD.PK_TRAVEL  ENABLE;

It would help to show the dynamic statement you're trying to execute. But from your description it's actually multiple statements, which will be a problem even if they have suitable separators/terminators. `execute immediate` expects a single statement. And as you're doing DDL you can't even wrap it in an anonymous block. You would need to split it into multiple statements (or store them separately) and execute them one by one. — Alex Poole, May 13 '22 at 12:41
Hi @AlexPoole, I have modified the question and added what script I have in clob and trying to execute with execute immediate. I hope that helps. Thanks.! — kiran, May 13 '22 at 12:52
Right, and that has multiple statements - so you'd need to split it out to three separate statements and execute those. Which isn't always simple - it will get difficult if you have to deal with PL/SQL, including triggers. — Alex Poole, May 13 '22 at 12:53
Yeah, Actually I have a procedure that executes this code block in PL/SQL. But If I have to split it when I retrieve it using dbms_metadata.get_ddl, How can I do it? because dbms_metadata.get_ddl gets the script as a whole along with the index and primary key constraint script. — kiran, May 13 '22 at 13:14
What problem are you trying to solve by storing this in a CLOB? You just said you used dbms_metadata.get_ddl to get the data so why store it? — Beefstu, May 13 '22 at 14:30
Beefstu I am getting the DDL of the table before dropping it. replacing that table with another table that has almost the same column names with some additional one and this activity is part of the migration and getting and storing DDL in one table is part of the backup. So in case migration gets failed, I need to restore the original table. — kiran, May 16 '22 at 05:05

d r · Accepted Answer · 2022-05-14T13:54:04.297

You could execute those commands one by one in a LOOP. Something like in the code here. I just printed the commands, but you could execute them:

    SET SERVEROUTPUT ON
    DECLARE
        mySQL  VarChar2(8000);
        mySQLexecute  VarChar2(8000);
    BEGIN
       SELECT TEXT INTO mySQL FROM DUMMY_1;
       --
        WHILE InStr(mySQL, ';') > 0 LOOP
          mySQLexecute := SubStr(mySQL, 1, INSTR(mySQL, ';'));
-- OR mySQLexecute := SubStr(mySQL, 1, INSTR(mySQL, ';')-1);  if you want a command without semicolon (;)
          DBMS_OUTPUT.PUT_LINE(mySQLexecute);
          --EXECUTE IMMEDIATE mySQLexecute;
          mySQL := SubStr(mySQL, INSTR(mySQL, ';') + 1);
        END LOOP;
    EXCEPTION WHEN OTHERS THEN
      raise_application_error (-20001, chr(10)||' Failed ' || SQLERRM);
    END;

This is how you could split the text by semicolon as delimiter. Regards... — d r, May 14 '22 at 13:56

How do I execute clob in execute immediate

1 Answers1