Cursor with multiple inserts

Select allOpen in new window

l_sOwner VARCHAR2(30);

I need to insert into multiple tables reusing the same cursor and wondered if anyone could provide the best approach something similar to this. I am unable to use temporary tables.

l_sName VARCHAR2(30);
l_sType VARCHAR2(19);
CURSOR cur IS SELECT owner, object_name name, object_type type FROM all_objects;
BEGIN
dbms_output.put_line('Before CURSOR OPEN: ' || systimestamp);
OPEN cur;
dbms_output.put_line('Before LOOP: ' || systimestamp);
LOOP
FETCH cur INTO l_sOwner, l_sName, l_sType;
IF cur%NOTFOUND THEN
EXIT;
END IF;
INSERT INTO temp values (l_sOwner, l_sName, l_sType);
END LOOP;
LOOP
FETCH cur INTO l_sOwner, l_sName, l_sType;
IF cur%NOTFOUND THEN
EXIT;
END IF;
INSERT INTO temp22 values (l_sOwner, l_sName, l_sType);
END LOOP;
 
LOOP
FETCH cur INTO l_sOwner, l_sName, l_sType;
IF cur%NOTFOUND THEN
EXIT;
END IF;
INSERT INTO temp33 values (l_sOwner, l_sName, l_sType);
END LOOP;
 
 
 
 
CLOSE cur;
dbms_output.put_line('After CURSOR CLOSE: ' || systimestamp);

COMMIT;