Re: How to indicate end of clob from pl/sql?

bclark76_at_gmail.com wrote:
> I have a temporary clob that I am creating in a pl/sql stored
> procedure using DBMS_LOB.CREATETEMPORARY and then I am passing the
> clob into a Java stored procedure which is supposed to read all the
> data from that clob ( doing stuff with it ) and then return when there
> is no more data to read.
>
>
> I get the oracle.sql.CLOB's InputStream with getAsciiInputStream and
> then use read to read from it.
>
> I am able to read all the data from the clob, but my procedure then
> blocks waiting for eof ( or end of clob since there iis no file
> involved ) that it never recieves.
>
> Is there any way, from PL/SQL to tell oracle that the clob is finished
> being written so that when it's passed and read from, eof will happen
> when the end is reached?
>
> Here's my PL/SQL code:
>
> DECLARE
> v_what VARCHAR2(1000) := 'This is a line of text, there are many
> like it, but this one is mine
> ';
>
> v_in CLOB := NULL;
> v_out CLOB := NULL;
> v_err CLOB := NULL;
> BEGIN
> DBMS_LOB.CREATETEMPORARY( v_in, TRUE, DBMS_LOB.CALL );
>
> DBMS_LOB.OPEN ( v_in, DBMS_LOB.lob_readwrite );
> DBMS_LOB.WRITEAPPEND( v_in, LENGTH( v_what ), v_what );
> DBMS_LOB.CLOSE ( v_in );
>
> my_java_stored_proc( v_in ); -- I never get to the next line
>
> DBMS_LOB.FREETEMPORARY( v_in );
> EXCEPTION
> WHEN OTHERS THEN
> BEGIN
> DBMS_LOB.FREETEMPORARY( v_in );
> RAISE;
> END;
> END;
>
>
>
> My java code looks basically like this:
>
>
> // This code is from the innards of a function
> // data is the oracle.sql.CLOB that is passed in as a parameter
> InputStream is = data.getAsciiStream();
> InputStreamReader isr = new InputStreamReader( is, cs )
> BufferedReader br = new BufferedReader( isr );
>
> int charsRead = 0;
> int offset = 0;
> char[] buf = new char[bufSize];
>
> while ( ( charsRead = br.read( buf, offset, bufSize ) ) >= 0 ) {
> // do something
> }
>
> // The something happens, but then the function never returns.
> // It blocks waiting for an 'eof' that never happens.

Why don't you determine the size using DBMS_LOB?

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org