reading a CLOB with a PL/SQL Procedure

Hi folks,

I insert a dataset into a table using the following procedure:

<CODE>

CREATE OR REPLACE PROCEDURE WRITE_KATALOGTEXT(IN_DOKUID CHAR, IN_ARTNR
NUMBER, IN_TEXT VARCHAR2, IN_DATUM DATE) IS LOB_LOC CLOB;
BEGIN
INSERT INTO TEXTCASTOR (DOKUID, ARTNR, TEXT, DATUM) VALUES (IN_DOKUID,
IN_ARTNR, EMPTY_CLOB(), IN_DATUM);
COMMIT;
SELECT TEXT INTO LOB_LOC FROM TEXTCASTOR WHERE DOKUID=IN_DOKUID AND ARTNR=IN_ARTNR AND DATUM=IN_DATUM FOR UPDATE;
DBMS_LOB.WRITE (LOB_LOC, length(IN_TEXT), 1, IN_TEXT); COMMIT;
END;
/
</CODE>

After this insert I try to read from the table using the following Procedure

<CODE>

CREATE OR REPLACE FUNCTION GET_KATALOGTEXT(IN_ARTNR NUMBER) RETURN VARCHAR2 IS
BUFFER CLOB;
MY_RETURNVALUE VARCHAR2(32767);
MY_ARTNR NUMBER(6):=IN_ARTNR;
BEGIN
SELECT TEXT INTO BUFFER FROM WORKFLOWOWNER.TEXTCASTOR WHERE ARTNR=MY_ARTNR;
MY_RETURNVALUE:=DBMS_LOB.SUBSTR(BUFFER, 32767, 1); RETURN MY_RETURNVALUE;
END;
/
</CODE>

My problem is, that I get the Oracle Error ORA-06502. The Version of the DB is 8.1.5 on Solaris. It says, that the String buffer is too small if the length of the CLOB exceeds 4k.
Writing works with values having a length of over fourthousand characters.
Reading only works with values having a length of less than fourthousand characters.
Has anyone a clue how to handle this?

Greetings
Markus

-- 
Markus Böhmer
Datenbankentwickler
SSI Schäfer Shop GmbH
mailto:markus.boehmer_at_schaefer-shop.de