Home » SQL & PL/SQL » SQL & PL/SQL » how to convert clob to long
how to convert clob to long [message #225808] Wed, 21 March 2007 10:09 Go to next message
donind
Messages: 95
Registered: February 2007
Member
Hi,

Is there any way to convert clob column to long.

Here below is my scenario..

Instead of using substr function

CREATE OR REPLACE PROCEDURE proc AS
sql2 clob := '';
sqlstring1 LONG;
sqlstring2 LONG;
sqlstring3 LONG;
sqlstring4 LONG;
sqlstring5 LONG;
sqlstring6 LONG;
sqlstring7 LONG;
sqlstring8 LONG;
sqlstring9 LONG;
sqlstring10 LONG;


BEGIN

FOR sql1 IN (SELECT info FROM emp)
LOOP

sql2 := sql1.sql_string;
sqlString1 := dbms_lob.SUBSTR(sql2, 8000, 1);
sqlString2 := dbms_lob.SUBSTR(sql2, 8000, 8001);
sqlString3 := dbms_lob.SUBSTR(sql2, 8000, 16001);
sqlString4 := dbms_lob.SUBSTR(sql2, 8000, 24001);
sqlString5 := dbms_lob.SUBSTR(sql2, 8000, 32001);
sqlString6 := dbms_lob.SUBSTR(sql2, 8000, 40001);
sqlString7 := dbms_lob.SUBSTR(sql2, 8000, 48001);
sqlString8 := dbms_lob.SUBSTR(sql2, 8000, 56001);
sqlString9 := dbms_lob.SUBSTR(sql2, 8000, 64001);
sqlString10 := dbms_lob.SUBSTR(sql2, 8000, 72001);
EXECUTE IMMEDIATE sqlString1 || sqlString2 || sqlString3 ||
sqlString4 || sqlString5 || sqlString6 ||
sqlString7 || sqlString8 || sqlstring9 ||
sqlstring10;
END LOOP;
COMMIT;

END proc;

Any help really appreciated

Thanks
Re: how to convert clob to long [message #225879 is a reply to message #225808] Wed, 21 March 2007 16:03 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
out of curiosity may i know why do you want to convert a clob to long ? I thought long datatype has been deprecated since release 8i.
Previous Topic: Populating 200 column values an existing row to a new row via trigger
Next Topic: Urgent: creating a record in RAC
Goto Forum:
  


Current Time: Tue Aug 22 06:20:18 CDT 2017

Total time taken to generate the page: 0.14922 seconds