Unanswered: Insert data to a temp table inside a stored function
I want to store the results of a select query in a temporary table for later use. I tried to use a global temporary table but you can't do this from inside a stored function/procedure.
I also tried the following (use a type of table):
CREATE OR REPLACE FUNCTION test_func
-- Declare the PL/SQL table
type dn_num_array is table of VARCHAR2(63)
index by binary_integer;
INSERT INTO dn_num_arr(
SELECT DN.DN_NUM AS DN_NUM
SQL> create global temporary table gtt_emp as select ename from emp where 1 = 2;
SQL> create or replace procedure prc_emp_gtt as
3 for cur_r in (select ename from emp) loop
4 insert into gtt_emp values (cur_r.ename);
5 end loop;
SQL> execute prc_emp_gtt
PL/SQL procedure successfully completed.
SQL> select * from gtt_emp;
15 rows selected.
Why do you think this won't work? Did you try something like this?
And, as of your question, you can't INSERT data into an array just like it was an "ordinary" table ... for such purposes you might use something like this:
create or replace function test_func return varchar2 is
type dn_num_array is table of varchar2(63) index by binary_integer;
cursor cur_emp is select ename from emp;
i number := 1;
for cur_r in cur_emp loop
dn_num_arr(i) := cur_r.ename;
i := i + 1;
dn_num := dn_num_arr.last;
SQL> select test_func from dual;