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
BEGIN
OPEN photo_cursor;
LOOP
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;