looping cursor on a query hangs.

From: Joyce <joyce.sz.chan_at_gmail.com>
Date: Fri, 1 May 2009 13:03:28 -0700 (PDT)
Message-ID: <93df0a2c-0c76-438e-b248-103bf375e766_at_k38g2000yqh.googlegroups.com>



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;
Received on Fri May 01 2009 - 15:03:28 CDT

Original text of this message