SQL statement selecting string > 4000 characters long fails

I wish to retrieve a row from a table that includes a CLOB column.

Due to our configuration of generic java beans that we use, I wish to: a) Return the row as a cursor, and
b) Return the CONTENTS of the clob column, rather than the clob pointer itself.
I do this using:

OPEN myCur FOR
SELECT rec.col1,
rec.col2,
...

DBMS_LOB.SUBSTR(rec.clobcol, 10000, 1) clobtext
...

FROM DUAL; This generates a NUMERIC OR VALUE ERROR. Reducing it to the simplest case, which is:

DECLARE
str VARCHAR2(32767);
BEGIN
SELECT DBMS_LOB.SUBSTR(rec.clobcol, 10000, 1) INTO str
FROM mytab
WHERE col1 = 1;
END; still fails with the same error.

Is it possible to achieve the extraction of a chunk of text > 4000 chars within a SELECT? We have
workarounds, but being able to select every column in the exact format we require on the Java side
is far more elegant. We do not wish to use CLOB access from the bean itself, hence returning the
contents to the bean instead.

Please email any responses to me as well, as my news access is sporadic. Received on Thu Feb 24 2000 - 22:44:46 CST