Bulk and reference cursor - Printable Version +- Oracle Forum - The Knowledge Center for Oracle Professionals - Looking Beyond the Possibilities (http://www.oraerp.com) +-- Forum: Platform as a Service (PaaS) (http://www.oraerp.com/forum-40.html) +--- Forum: Oracle Technology (http://www.oraerp.com/forum-16.html) +---- Forum: SQL & PL/SQL (http://www.oraerp.com/forum-26.html) +---- Thread: Bulk and reference cursor (/thread-65.html) |
Bulk and reference cursor - admin - 12-25-2010 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; |