looping cursor on a query hangs.

Giganews Newsgroups
Subject: looping cursor on a query hangs.
Posted by:  Joyce (joyce.sz.ch…@gmail.com)
Date: Fri, 1 May 2009

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;

Replies