+- 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;