Since 2010, OraERP is a Oracle Forums, Community of Oracle Professionals including Fusion/Cloud Application Consultants, Enterprise Architects, ERP Cloud, HCM Cloud, CX Cloud and OCI Experts, ERP Experts, Oracle Apps Functional Consultants, Apps DBAs, DBAs, Cloud DBAs, Digital Architect, PaaS Experts, IaaS, OCI Architects, Technical Consultants, Fusion Middleware Experts, SQL, PL/SQL Developers and Project Managers. Welcome to OraERP.com Social Community, a friendly and active community of Oracle Technology Professionals who believe that technology can ‘make the world a better place’. By joining Oracle ERP Community you will have the ability to Post Topics, Receive our Newsletter, subscribe to threads and access many other special features. Registration is Quick and Simple. Get unlimited access to Oracle Tutorials, Articles, eBooks, Tools and Tips .
Thread Rating:
  • 20 Vote(s) - 2.65 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Bulk and reference cursor
12-25-2010, 01:16 AM,
#1
Bulk and reference cursor
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;
Reply




Users browsing this thread: 2 Guest(s)