How to bulk insert data from ref cursor to a temporary table in PL/SQL
22,073
Solution 1
you can use BULK operations on 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 regular loop though, so you may not see much improvement from a simple LOOP...INSERT.
Solution 2
How 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;
Author by
Sambath Prum
A junior rails developer who keeps up learning web development.
Updated on March 12, 2020Comments
-
Sambath Prum about 4 years
Could anyone tell me how to bulk insert data from a ref cursor to a temporary table in PL/SQL? I have a procedure that one of its parameters stores a result set, this result set will be inserted to a 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 as 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 can I write similar to the code in SQL Server? Thanks.