Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Reading CLOBs

Re: Reading CLOBs

From: <artmt_at_hotmail.com>
Date: 31 Oct 2006 13:25:16 -0800
Message-ID: <1162329916.103381.8070@i42g2000cwa.googlegroups.com>

Steve Howard wrote:
> 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

I got morginal improvements getting rid of the SELECT inside the loop. There is one phisical read either way.
I got additional improvements fetching each row into a collection and then doing FORALL INSERT from the collection.

I wonder if there is a way to fetch the entire CLOB into a collection with a single operation - without having to re-read the entire CLOB to find each line.

Art Received on Tue Oct 31 2006 - 15:25:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US