create table test1 as select 1 id,'create table test2(id number,name varchar2(200));insert into test2 values(1,''a'');' sqltext from dual
2 union all select 2,'truncate table test2;' from dual
3 union all select 3,'insert into test2 select rownum,dbms_random.string(''l'',3) from dual connect by rownum<10;delete from test2 where id between 3 and 5;' from dual;
Table created
SQL>
SQL> create or replace function exc_sql(str in varchar2)return varchar2
2 as
3 pragma autonomous_transaction;
4 v_error varchar2(200);
5 begin
6 for i in 1..length(str)-length(replace(str,';')) loop
7 execute immediate substr(str,instr(';'||str,';',1,i),instr(str,';',1,i)-instr(';'||str,';',1,i));
8 end loop;
9 commit;
10 return 'ok';
11 exception
12 when others then
13 v_error:=sqlerrm;
14 return v_error;
15 end;
16 /
Function created
SQL> select test1.*,exc_sql(sqltext) from test1 where id=1;
ID SQLTEXT EXC_SQL(SQLTEXT)
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 create table test2(id number,name varchar2(200));insert into test2 values(1,'a') ok
SQL> select * from test2;
&nb
