How to bulk insert data from ref cursor to a temporary table in PL/SQL
- by Sambath
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.
