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: how to transfer data from sql-server to oracle ?
Next Topic: query help
Goto Forum:
  


Current Time: Sat Dec 03 20:03:58 CST 2016

Total time taken to generate the page: 0.09433 seconds