How do I execute clob in execute immediate-CodePudding

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;

CodePudding user response：

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;