asked on

Reading the content of a CLOB column

hi all, I uploaded a file to a CLOB column , i would need:

1. to know how I can know if the file has been actually inserted into the column: where / what I have to go / do in order to see if there is some content?

2. I need procedure code to read the content of the CLOB column

Please send just code that actually works and please indicate steps by steps what I have to do to launch the procedure as, even tought I use it, I am not an Oracle procedure and programmig expert ...

Thank you and regards

seazodiac

This is an excellant link for dealing with Oracle clob:

http://www.psoug.org/reference/dbms_lob.html

SOLUTION

crimson117

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

konektor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jytr

ASKER

hi konektor, please try your procedure as I get an error...at the line of the if ...it tells me 'ORA-06550: line 7, column 3:
PL/SQL: ORA-00933: SQL command not properly ended'

thanks and regards

oratim

because you need to put in your own where clause, and not just cut and paste the procedure.

select lob_col into v_lob_col from your_table where condition...

is not a valid SQL statement, because there is no condition for the where clause. you need something like

primary_key_col = your_primary_key instead of "condition..."

pfeddis

create procedure read_clob
is
clobColumn clob;
iClobSize integer;
rBuffer raw(32767);
vBuffer varchar2(32767);
nOffset number := 1;
iMaxSize integer := 32767;
iChunkSize integer;
iLeftOver integer;
begin
dbms_lob.createTemporary(clobColumn, true);
--select lob_col into clobColumn from your_table where....
iClobSize := dbms_lob.getlength(clobColumn);
if iClobSize > 0
then
loop
iLeftOver := (iClobSize - nOffset + 1);
if iLeftOver <= iMaxSize
then
iChunkSize := iLeftOver;
else
iChunkSize := iMaxSize;
end if;
dbms_lob.read(clobColumn, iChunkSize, nOffset, rBuffer);
vBuffer := utl_raw.cast_to_varchar2(rBuffer);
--processing....
nOffset := nOffset + iMaxSize;
if nOffset > iClobSize
then
exit;
end if;
end loop;
end if;
dbms_lob.freeTemporary(clobColumn);
end read_clob;
/