Re: Executing long ddls using dynamic sql
- From: "Juan Carlos Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 27 Jul 2004 18:52:09 -0400
Here is how, I got the structe from Tom site and adecuate to a clob
declare
dbms_sql.varchar2s;
l_cursor integer default dbms_sql.open_cursor;
l_rows number default 0;
ELLOB clob;
nPos number ;
I number := 1;
nPosAnt number := 1;
begin
SELECT A INTO ELLOB FROM CTB.TESTE;
WHILE TRUE LOOP
DBMS_OUTPUT.PUT_LINE(1);
nPos := instr( ELLOB,CHR(10),1,i);
-- DBMS_OUTPUT.PUT_LINE(nPos||':pos-i:'||i);
DBMS_OUTPUT.PUT_LINE(2);
if nPos = 0 THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE(substr(ellob,nPosAnt,nPos-nPosAnt));
DBMS_OUTPUT.PUT_LINE(3);
l_stmt(i) := substr(ellob,nPosAnt,nPos-nPosAnt);
DBMS_OUTPUT.PUT_LINE(4);
i:=i+1;
DBMS_OUTPUT.PUT_LINE(5);
nPosAnt := nPos;
END LOOP;
dbms_sql.parse( c => l_cursor,
statement => l_stmt,
lb => l_stmt.first,
ub => l_stmt.last,
lfflg => TRUE,
language_flag => dbms_sql.native );
l_rows := dbms_sql.execute(l_cursor);
dbms_sql.close_cursor( l_cursor );
exception
when others then
if dbms_sql.is_open(l_cursor) then
dbms_sql.close_cursor(l_cursor);
end if;
raise;
end;
/
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
