Re: Reading CLOBs

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 31 Oct 2006 11:33:09 -0800
Message-ID: <1162323189.664910.182820@m7g2000cwm.googlegroups.com>

artmt_at_hotmail.com wrote:
> Steve Howard wrote:
> > artmt_at_hotmail.com wrote:
> > > Valentin Minzatu wrote:
> > > > >From a bird's eye view point, you could read, parse and write using
> > > > PL/SQL. You could optimize the processing using arrays if the table is
> > > > big for performance reasons as well.
> > > >
> > > > I am not very sure what makes the task different than anything else you
> > > > could do in PL/SQL.
> > > >
> > > > Am I missing the question?
> > >
> > > Suppose my data looks like this:
> > >
> > > 1111|Smith|John|H|19750101
> > > 1112|Jones|Anna||19690810
> > >
> > > My understanding is that with DBMS_LOB I can read chunks specified in
> > > bytes or in characters. How do I detect where the line ends and the new
> > > line begins?
> >
> > SQL> set serveroutput on
> > SQL> declare
> > 2 l_clob clob;
> > 3 begin
> > 4 l_clob := '1111|Smith|John|H|19750101' || chr(13) ||
> > '1112|Jones|Anna||19690810';
> > 5 dbms_output.put_line(dbms_lob.instr(l_clob,chr(13)));
> > 6 end;
> > 7 /
> > 27
> >
> > PL/SQL procedure successfully completed.
> >

>

> Thanks.
> That cerainly works, but is very slow.
> The loop below takes 8 minutes to process 50K rows.

>

> LOOP
> SELECT
> TRIM(SUBSTR(MY_LOB,v_START,INSTR(MY_LOB,CHR(13),v_START)-v_START)),
> INSTR(MY_LOB,CHR(13),v_START)+2
> INTO v_CLOB_ROW, v_START
> FROM TEST_LOB;
> INSERT INTO TEST_FETCH VALUES(v_CLOB_ROW);
> EXIT WHEN LENGTH(v_CLOB_ROW) IS NULL;
> END LOOP;

>

> It must be reading the entire CLOB for each iteration.
>
> Is there a better way to do this?

One thing would be I wouldn't read the CLOB to process the next chunk of it for every chunk. It looks like you are selecting the lob each time from the table to process the next chunk of it. Why not get the lob value once into a variable and just process it in PLSQL?

HTH, Steve Received on Tue Oct 31 2006 - 13:33:09 CST