ORACLE - Dynamic Sql using CLOB

Question

I have a table as described below

SQL> desc tab_script
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SRLNO                                              NUMBER
 INSERT_SCRIPT                                      CLOB

insert_script is populated with insert statement. I want to execute insert script dynamically. I have written the following code, but does not work.

DECLARE
  ln_type_id NUMBER:= 1;
  lcl_sql    clob;
BEGIN
 FOR rec IN ( SELECT * FROM tab_script )
 LOOP
   lcl_sql:= rec.insert_script;
   EXECUTE IMMEDIATE lcl_sql ;
 END LOOP;
 COMMIT;
END;

My oracle version Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

"*does not work*" is neither a valid Oracle error message nor an acceptable problem description. — , Apr 20 '16 at 14:42

score 1 · Answer 1 · answered Apr 21 '16 at 02:11

1.Always a good practice to print the SQL query using dbms_output.put_line and verify the SQL.

2.In case if you have multiple insert statements then you can use BEGIN and END

DECLARE
v_sql CLOB;
BEGIN
For i in (select * from tab_script)
LOOP
v_sql:= i.insert_script ;
-- Use below line ony if you have multiple insert statements seperated with semi-column
--v_sql := ' BEGIN ' || v_sql || ' END; ' ; 
 dbms_output.put_line('SQL STMNT - ' || v_sql); -- This is to check SQL 
execute immediate  v_sql ;
commit;
END LOOP;
EXCEPTION 
WHEN OTHERS 
THEN 
--dbms_output.put_line('' ); 
--Calling procedure to log/insert the exceptions, if any
LOG_PROC_ERRORS('Error with statement :  ' || v_sql ||
 ' ' || ' Error message : ' || ' ' || sqlerrm || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ); 
END;

Output:

SQL STMNT - insert into emp(ID,NAME) values(7,'KLM');

score 0 · Answer 2 · answered Apr 20 '16 at 15:08

0

I guess, you´ve stored multiple INSERTs in you clob-column, right? But EXECUTE IMMEDIATE only works with one operation per cycle:

CREATE TABLE TEST(ID INTEGER, NAME VARCHAR2(50));

BEGIN
   EXECUTE IMMEDIATE 'insert into test (id, name) values (1, ''first'')'; 
   EXECUTE IMMEDIATE 'insert into test (id, name) values (2, ''second'')'; 
--   EXECUTE IMMEDIATE 'insert into test (id, name) values (3, ''third'');insert into test (id, name) values (4, ''fourth'');'; 
   COMMIT;
END;
/

SELECT * FROM TEST;

DROP TABLE TEST;

In the sample above, everything works fine and the result is as expected:

ID  NAME    
1   first
2   second

But if you uncomment the third EXECUTE-IMMEDIATE, then an error is thrown.

If that´s your problem, you´ll have to split your INPUT-Statements at the semcicolon and execute each by each.

answered Apr 20 '16 at 15:08

oratom

271
1
5

`Execute immediate` can be used to execute multiple statements using `BEGIN`and `END`, see above example. – mahi_0707 Apr 21 '16 at 02:12
My sincere apologies.. my insert statement is throwing error when executed in sql or dynamic sql. – Chandrashekhar BC Apr 21 '16 at 05:09
my insert statement executed fine when executed in toad.. by fails when executed in sqlplus or pl/sql.. The reason being the format.. – Chandrashekhar BC Apr 21 '16 at 05:10
null,null,null,null,null,null,null,to_date('', 'dd/mm/rrrr hh24:mi:ss'),to_date('', 'dd/mm/rrrr hh24:mi:ss'),'E','GHPLPAS',to_date('06/03/2009 00:00:00', – Chandrashekhar BC Apr 21 '16 at 05:11
sqlplus and pl/sql is not able to format the insert script .. like above pl/sql expectc the column values be in one line.. i have generated these insert script and stored in clob.now i want to execute this dynamically.. – Chandrashekhar BC Apr 21 '16 at 05:13
Can you add the insert statement and error message in the question – mahi_0707 Apr 22 '16 at 00:01

ORACLE - Dynamic Sql using CLOB

2 Answers2