Re: looping cursor on a query hangs.

From: William Robertson <williamr2019_at_googlemail.com>
Date: Sat, 2 May 2009 16:06:13 -0700 (PDT)
Message-ID: <21caa102-0a42-43c9-898a-2765e487d87f_at_q14g2000vbn.googlegroups.com>

On May 1, 9:03 pm, Joyce <joyce.sz.c..._at_gmail.com> wrote:
> Is it reasonable for Oracle to hang on a query like this?  Basically,
> it's a cursor on a query which splits csv entries, and using the
> splitted up values to insert into another table.  Does it hang because
> my table is too large?  what is a better way that I can accomplish
> what I'm trying to do here?  thank you, Joyce
>
> DECLARE
>    CURSOR photo_cursor IS
>       select occurrence_num, graphic from (
>         select occurrence_num, trim(substr(str, instr(str, ',', 1, level) +
> 1,
>         instr(str, ',', 1, level + 1) - instr(str, ',', 1, level) - 1)
> ) graphic, level lv
>         ,  lag(level, 1, 0) over (partition by occurrence_num order by level)
> lg
>         from (select occurrence_num, ','||graphics||',' str from supp)
>         connect by instr(str, ',', 1, level) > 0)
>         where graphic is not null and lv != lg and rownum <=10;
>    photo_record photo_cursor%ROWTYPE;
> BEGIN
>    OPEN photo_cursor;
>    LOOP
>       FETCH photo_cursor INTO photo_record;
>       EXIT WHEN photo_cursor%NOTFOUND;
>    -- insert into image (photo_id,report_id, filename)
>    -- values (PHOTO_key_seq.nextval,r.occurrence_num,
> r.graphic);
>    END LOOP;
>    CLOSE photo_cursor;
> END;
Assuming you are using Oracle 10g, the simpler "FOR recordname IN cursorname LOOP" syntax (or equivalent "FOR recordname IN (query) LOOP") will be converted by the compiler to a more efficient bulkcollect  construction. Whether this addresses your main problem here I can't say. Received on Sat May 02 2009 - 18:06:13 CDT