Oracle Forum - The Knowledge Center for Oracle Professionals - Looking Beyond the Possibilities

Full Version: Bulk and reference cursor
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
I simulated a sample procedure for my requirement. When i try to compile procedure it throws error 'cannot mix single and multiple rows ( bulk) into'...I have to pass a table as dynamic in a cursor ,collect the data and process it using and forall ...Please help me
create or replace PROCEDURE proc_1(t_name varchar2) IS
TYPE parent_rec IS RECORD (part_num dynamic.emp_name%type,part_name dynamic.emp_id%type,part_id dynamic.tel_no%type) ;
p_rec parent_rec;

rec_array SYS_REFCURSOR;

BEGIN
OPEN rec_array FOR 'select EMP_NAME, EMP_ID,TEL_NO FROM '||t_name ||' WHERE EMP_ID = ''1''' ;
LOOP
FETCH rec_array BULK COLLECT INTO p_rec.part_num, p_rec.part_name, p_rec.part_id LIMIT 500;
FORALL i IN p_rec.first..p_rec.last
INSERT INTO dynamic_1(emp_name_1,emp_id_1,tel_no_1) VALUES (p_rec.part_num(i), p_rec.part_name(i),p_rec.part_id(i));

EXIT WHEN rec_array%NOTFOUND;
END LOOP;
COMMIT;
CLOSE rec_array;
END proc_1;