Home » SQL & PL/SQL » SQL & PL/SQL » Problem in reading CLOB
icon5.gif  Problem in reading CLOB [message #211304] Wed, 27 December 2006 21:27 Go to next message
yugamore
Messages: 23
Registered: December 2006
Junior Member
Hi All,

I need to read data from the column "DESCRIPTION" which is of the type "CLOB". I have a code for the same but it runs fine for first 121 rows of data and then gives following error:

begin read_clob; end;

ORA-21560: argument 2 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 759
ORA-06512: at "MCD.READ_CLOB", line 39
ORA-06512: at line 1

Following is the code i'm using:

create or replace procedure read_clob as
clobColumn clob;
iClobSize integer;
rBuffer raw(32767);
vBuffer varchar2(32767);
nOffset number := 1;
iMaxSize integer := 32767;
iChunkSize integer;
iLeftOver integer;
v_id NUMBER;
v_name varchar2(50);
v_sector varchar2(50);
v_country varchar2(50);
CURSOR C1 is
select company_id,company_name,sector,country,description
from pli2_t_company_master
where company_id between 1 and 179;
BEGIN
open c1;
loop
fetch c1 into v_id,v_name,v_sector,v_country,clobColumn;
exit when c1%notfound;
iClobSize := dbms_lob.getlength(clobColumn);
if iclobsize = 0
then
dbms_output.put_line(v_id ||'|'||v_name ||'|'||v_sector||'|'||v_country||'|'||'EMPTY DESCRIPTION');
END IF;
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, vBuffer);
--vBuffer := utl_raw.cast_to_varchar2(rBuffer);
dbms_output.put_line(v_id ||'|'||v_name ||'|'||v_sector||'|'||v_country||'|'||vBuffer);
nOffset := nOffset + iMaxSize;
if nOffset > iClobSize
then
exit;
end if;
end loop;
end if;
END LOOP;
close c1;
END;

Can anyone help me to detect the problem, actually i'm not sure what do the parameters in dbms_lob.read() signify, what is the significance of iChunkSize, nOffset parameters in this procedure?

Kindly revert ASAP.

Regards
Yugandhara

[Updated on: Wed, 27 December 2006 21:30]

Report message to a moderator

Re: Problem in reading CLOB [message #211330 is a reply to message #211304] Thu, 28 December 2006 00:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think you should reset nOffset in each iteration of you cursor loop.
Re: Problem in reading CLOB [message #211339 is a reply to message #211330] Thu, 28 December 2006 01:09 Go to previous message
yugamore
Messages: 23
Registered: December 2006
Junior Member
Hi Frank,
Thank you for pointing that out , It worked fine , could you please help me out with one more thing, I need to spool this out put in a text file , could you please let me know how should i do it?
Regards
Yugandhara
Previous Topic: sending large email using oracle
Next Topic: how to get difference between 2 date columns in SQL Server or sybase
Goto Forum:
  


Current Time: Sat Dec 10 08:57:30 CST 2016

Total time taken to generate the page: 0.08312 seconds