Home » SQL & PL/SQL » SQL & PL/SQL » Execution of Clob through dynamic sql
Execution of Clob through dynamic sql [message #292958] Thu, 10 January 2008 05:00 Go to next message
donind
Messages: 95
Registered: February 2007
Member

Hi,

Here in below code.I am trying to execute the CLOB dynamically.Its giving an error as
ORA-6502
Numeric or value error.

SQL3        CLOB := '';
SQLSTRING1  LONG;
SQLSTRING2  LONG;
SQLSTRING3  LONG;
Begin
SQL3 := GENUPD(RECTABLE => 'REC_TARGET_TABLE',
                       TGTTABLE     =>'TARGET_TABLE',
                       AUDITTABLE   => 'DATA_AUDIT_DETAILS');

SQLSTRING1  := DBMS_LOB.SUBSTR(SQL3, 8000, 1);
SQLSTRING2  := DBMS_LOB.SUBSTR(SQL3, 8000, 8001);
SQLSTRING3  := DBMS_LOB.SUBSTR(SQL3, 8000, 16001);
EXECUTE IMMEDIATE SQLSTRING1 || SQLSTRING2 || SQLSTRING3 ;

/*

When I am doing the same in below way its giving an above error


for i in 0 .. 2 loop
SQLSTRING1 := SQLSTRING1 ||
DBMS_LOB.SUBSTR(SQL3, 8000, 8000 * i + 
end loop;
EXECUTE IMMEDIATE SQLSTRING1;

*/
end;








Is there any way to write the aboive code in optimized manner.
Any help really appreciated.

Thanks in Advance
Re: Execution of Clob through dynamic sql [message #292962 is a reply to message #292958] Thu, 10 January 2008 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are limited to 32K.
You have to use dbms_sql with an array of strings (or upgrade to 11g).

Regards
Michel
Re: Execution of Clob through dynamic sql [message #312175 is a reply to message #292958] Tue, 08 April 2008 02:39 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi.

As Michel wrote - you are limited to 32K, however, if your SQL statement is shorter then 32K then try:

DECLARE
  SQL3        CLOB := '';
  stmt        VARCHAR2(32700);
Begin
  SQL3 := GENUPD(RECTABLE => 'REC_TARGET_TABLE',
                       TGTTABLE     =>'TARGET_TABLE',
                       AUDITTABLE   => 'DATA_AUDIT_DETAILS');

  stmt  := DBMS_LOB.SUBSTR(SQL3, 24000, 1);
  EXECUTE IMMEDIATE stmt;
END;


HTH.
Michael
Previous Topic: how to calculate table partition size which is growing every month
Next Topic: Minute Differences
Goto Forum:
  


Current Time: Sat Dec 03 08:00:12 CST 2016

Total time taken to generate the page: 0.16500 seconds