Hello Tom,

I am faced with the challenge of dealing with CLOB values that are
greater than 32767 characters in length. For example, I have to read a record's CLOB value which has a length = 946056k. When I test the
dbms_lob.read package it fails when the amount is > 32767. If this is
the case, how can one use this function to read the chunks of a CLOB
which has a length > 32767k.

Any help on this will be greatly appreciated.

Thanks,

Dean

DBMS_LOB defines the following constants:
-------------------------------------------------------------------
file_readonly CONSTANT BINARY_INTEGER := 0;
lob_readonly CONSTANT BINARY_INTEGER := 0;

lob_readwrite CONSTANT BINARY_INTEGER := 1;
lobmaxsize CONSTANT INTEGER := 4294967295;
call CONSTANT PLS_INTEGER := 12;
session CONSTANT PLS_INTEGER := 10;
-------------------------------------------------------------------

DBMS_LOB uses the following Parameters:


Parameter Descrption:
-----------------------------------------
lob_loc: Locator for the LOB to be read.
amount: Number of bytes (for BLOBs) or characters (for CLOBs) to read,
or number that were read.
offset: Offset in bytes (for BLOBs) or characters (for CLOBs) from
the start of the LOB (origin: 1).
buffer: Output buffer for the read operation.


Please Note: The following block of code errors out because the amount
is > 32767.

declare
c_text_long varchar2(32767);
lobloc CLOB;
buffer VARCHAR2(32767);
amount number := 4294967295; -- clob max size in
bytes!
offset INTEGER := 100;
lob_length integer := 0;
begin
select text_long
into lobloc
from scopus.notes_clob
where note_id = 147787;

dbms_lob.read(lobloc,amount,offset,buffer);

exception
when others then
dbms_output.put_line('SQLCODE:'||SQLCODE);
dbms_output.put_line('SQLERRM:'||SQLERRM);
end;
/