Re: Reading CLOBs

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? Received on Tue Oct 31 2006 - 12:57:36 CST