Home » SQL & PL/SQL » SQL & PL/SQL » clob and ref cursor (Oracle 10 g)
clob and ref cursor [message #387573] Thu, 19 February 2009 23:04 Go to next message
ammuthomas1
Messages: 1
Registered: February 2009
Junior Member
Hi all,

could any one tell me how to work with clob and ref cursor?
I am working with oracle 10g
I have to use the clog data type in the refcursor.
My Procedure was having the code like as follows

a) PROCEDURE test(id pnumber,pDate Date)
IS
.
.
.

sSql VARCHAR2(32000);
.
.
.
Begin
OPEN Test_Cursor FOR sSql USING pDate
,pnumber;

LOOP
FETCH Test_Cursor INTO test_Value;
EXIT WHEN Test_Cursor%notfound;

Insert into Test_Table (...........);

.
.
.
.
END LOOP;
Test_Cursor ;

End;


Variable sSql is forming dynamically.At times the length of the sSql is morethan 32k.
In such case the Procedure run is not successful.

This is the reason why I thought of using the CLOB DATA Type.
But I really don't know how to use it along with the cursor.

Actually these procedures are in a package.

Some procedures inserts a value is a table(Eg a) and some just return the cursor to ASP.Net(Eg.b).

b) PROCEDURE test(id pnumber,pDate Date)
IS
.
.
.

sSql VARCHAR2(32000);
.
.
.
Begin
OPEN Test_Cursor FOR sSql USING pDate
,pnumber;


End;

Please let me know if you require more information.

Regards,
Ammu.
Re: clob and ref cursor [message #387629 is a reply to message #387573] Fri, 20 February 2009 01:53 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
11g has native support for execute immediate with clobs, but prior to that you are somewhat limited. Tom Kyte (who else?) has given an example of using DBMS_SQL here.

MHE
Previous Topic: Converting columns into rows
Next Topic: duplicate column name
Goto Forum:
  


Current Time: Sat Dec 03 22:05:21 CST 2016

Total time taken to generate the page: 0.06187 seconds