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

On Sep 12, 9:42 pm, DA Morgan <damor..._at_psoug.org> wrote:
> bclar..._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
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

I agree. Grab the length of the CLOB and use that to control processing. It doesn't make sense to try to process the CLOB any other way when you need to process to the end.

IMHO -- Mark D Powell -- Received on Sat Sep 13 2008 - 08:54:33 CDT