OraFAQ Forum: SQL & PL/SQL » retrieve data from clob column

Home » SQL & PL/SQL » SQL & PL/SQL » retrieve data from clob column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

retrieve data from clob column [message #2016]

Mon, 17 June 2002 00:13

karuna
Messages: 7
Registered: February 2002

Junior Member

How to retrieve data from clob column.

Report message to a moderator

Re: retrieve data from clob column [message #2017 is a reply to message #2016]

Mon, 17 June 2002 02:14

Maaher
Messages: 7065
Registered: December 2001

Senior Member

You use the package DBMS_LOB. Write a stored procedure that reads the LOB bit by bit. There's a procedure called getchunksize This can be used to increase the offset of where to start reading.

Here's an example: http://www.webspedite.com/oracle/view_clob_example.htm

HTH,
MHE

Report message to a moderator

Re: retrieve data from clob column [message #2132 is a reply to message #2016]

Sun, 23 June 2002 21:31

Afif
Messages: 2
Registered: June 2002

Junior Member

CREATE OR REPLACE PROCEDURE Test_CLOB
IS
/* Variable Declaration */

string2 CLOB ;
v_oraerr_code VARCHAR2(100) ;
v_oraerr_mesg VARCHAR2(100) ;

Buffer VARCHAR2(32767);
Amount BINARY_INTEGER := 32767;
Position INTEGER := 1;
len_lob INTEGER;
BEGIN
Proc_return_CLOB('500413806',string2);

len_lob := dbms_lob.GETLENGTH(string2);
Amount := 200;
dbms_output.put_line('v_status : ' ||v_status);
LOOP
dbms_lob.READ(string2,Amount,position,sqlstring);
dbms_output.put_line(sqlstring);
position := position + Amount;

IF ((position + 200) >= len_lob ) THEN
amount := len_lob - position;
END IF;
EXIT WHEN position >= len_lob;
END LOOP;

/*EXCEPTION

WHEN OTHERS THEN
v_oraerr_code := SQLCODE;
v_oraerr_mesg := SQLERRM;
dbms_output.put_line(v_oraerr_code);
dbms_output.put_line(v_oraerr_mesg);*/

END;
/

Execute thsi test procedure and you will see the O/p on SQL plus.
But execute SET SERVER OUT ON SIZE 1000000;
before.

Report message to a moderator

Previous Topic:	Procedures failing
Next Topic:	Help: Why ORA-01037: maximum cursor memory exceeded occured?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 10 10:33:00 CDT 2024