How to combine insertion of data from ref cursor into temporary table in PL / SQL

Question

Can someone tell me how to massage insert data from ref cursor into temporary table in PL / SQL? I have a procedure in which one of its parameters stores the result set, this result set will be inserted into the temporary table in another stored procedure.

This is my sample code.

CREATE OR REPLACE PROCEDURE get_account_list ( type_id in account_type.account_type_id%type, acc_list out sys_refcursor ) is begin open acc_list for select account_id, account_name, balance from account where account_type_id = type_id; end get_account_list; CREATE OR REPLACE PROCEDURE proc1 ( ... ) is accounts sys_refcursor; begin get_account_list(1, accounts); --How to bulk insert data in accounts to a temporary table? end proc1; 

In SQL Server, I can write the code below

 CREATE PROCEDURE get_account_list type_id int as select account_id, account_name, balance from account where account_type_id = type_id; CREATE PROCEDURE proc1 ( ... ) as ... insert into #tmp_data(account_id, account_name, balance) exec get_account_list 1 

How to write code similar to SQL Server code? Thanks.

Answer 1

you can use BULK operations in REF CURSOR:

 SQL> CREATE GLOBAL TEMPORARY TABLE gt (ID NUMBER); Table crÚÚe. SQL> DECLARE 2 l_refcursor SYS_REFCURSOR; 3 TYPE tab_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; 4 l_data tab_number; 5 BEGIN 6 OPEN l_refcursor FOR 7 SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 1e6; 8 LOOP 9 FETCH l_refcursor BULK COLLECT 10 INTO l_data LIMIT 100; 11 12 FORALL i IN 1..l_data.count 13 INSERT INTO gt VALUES (l_data(i)); 14 15 EXIT WHEN l_refcursor%NOTFOUND; 16 17 END LOOP; 18 CLOSE l_refcursor; 19 END; 20 / ProcÚdure PL/SQL terminÚe avec succÞs. 

Oracle 10g already implements this optimization for a regular cycle, although you may not see much improvement from a simple LOOP ... INSERT.

Answer 2

What about

 procedure insert_rec(in_type_id in number) is begin insert into temp_table select account_id, account_name, balance from account where account_type_id = in_type_id; end insert_rec;