SQL & PL/SQL

I have several clobs in a single row of data and have created a procedure where I attempt to display the first 4k of each clob.

using a for loop cursor I select all of the clobs in a row at the same time and then I use dbms_lob.read to get the first 4k of data.

For the first lob of each row everything works fine but when I attempt to dbms_lob.read the second one I get an "ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275" and I'm not sure why?

does anyone know of an example where two or more clobs are selected and then converted to 4K varchars?


sample columns for the table:
SPECIALREQUIREMENTSS CLOB
REFERENCERANGE CLOB
ALERTVALUE CLOB
REFERENCELAB VARCHAR2(50 CHAR)
TESTINCLUDES CLOB
LASTUPDATE DATE

sample code (I removed some code for clairity - this produces the same error):
create or replace
procedure test_lobs as
v_lab_blob blob;
v_lobamt binary_integer := 32767;
v_lobpos pls_integer := 1;
v_labrec labman.lab_testingdata%rowtype;
v_buffer varchar2(32767) :='';
v_start_left number;
begin
-- ***
-- *** housekeeping
dbms_lob.createtemporary(v_labrec.testincludes,TRUE);
dbms_lob.createtemporary(v_labrec.specialrequirementss,TRUE);
Plpdf.init;
Plpdf.NewPage;
Plpdf.SetColor4Drawing(66,66,66);
Plpdf.SetColor4Filling(180,180,180);
v_start_left := Plpdf.GetPageWidth-Plpdf.GetPageRightSpace;
-- ***
-- *** select the test name for shaded printing
for x in (select indexnumber, test from labman.lab_testingdata where test like 'A%') loop
-- ***
-- *** select the test data for printing
select * into v_labrec from labman.lab_testingdata where indexnumber = x.indexnumber;
-- ***
-- *** test includes
if dbms_lob.getlength(v_labrec.testincludes) > 0 or v_labrec.testincludes is not null then
Plpdf.SetPrintFont('Arial','B',8);
Plpdf.LineBreak(.1);
Plpdf.PrintCell(25,5,'Test Includes:',0,0,'R',0,null,0);
Plpdf.SetPrintFont('Arial',null,8);
if dbms_lob.getlength(v_labrec.testincludes) > 32767 then
v_buffer := '*** error - text is greater than 32k ***';
else
v_lobamt := 32767;
v_buffer := '';
dbms_lob.read(v_labrec.testincludes,v_lobamt,v_lobpos,v_buffer);
end if;
Plpdf.SetLeftMargin(Plpdf.GetCurrentX);
Plpdf.PrintFlowingText(5,v_buffer,null,0);
Plpdf.SetLeftMargin(v_start_left);
Plpdf.lineBreak;
end if;
-- ***
-- *** special requirements
Plpdf.SetPrintFont('Arial','B',8);
Plpdf.LineBreak(.1);
Plpdf.PrintCell(25,5,'Special Requirements:',0,0,'R',0,null,0);
Plpdf.SetPrintFont('Arial',null,8);
if dbms_lob.getlength(v_labrec.specialrequirementss) > 32767 then
v_buffer := '*** error - text is greater than 32k ***';
elsif dbms_lob.getlength(v_labrec.specialrequirementss) = 0 then
v_buffer := '';
else
v_lobamt := 32767;
v_buffer := '';
dbms_lob.read(v_labrec.specialrequirementss,v_lobamt,v_lobpos,v_buffer);
end if;
Plpdf.SetLeftMargin(Plpdf.GetCurrentX);
Plpdf.PrintFlowingText(5,v_buffer,null,0);
Plpdf.SetLeftMargin(v_start_left);
Plpdf.lineBreak;
commit;
end loop;
Plpdf.SendDoc(v_lab_blob);
-- store the PDF document
delete from store_blob;
commit;
INSERT INTO STORE_BLOB (blob_file, created_date)
VALUES (v_lab_blob, SYSDATE);
COMMIT;
end test_lobs;