I've written a function that returns a ref cursor. The function has params in it, but for simplicity sake, I will only include 1. Lets say the function is called CREATE OR REPLACE FUNCTION "TEST" (AVARIABLE IN BLAH.TVAR%TYPE).
This function returns a ref cursor. I need to return a cursor as the code could change and creating a type would just be another step that I dont think I need.
I also have another function called CREATE OR REPLACE FUNCTION "TESTABC" (). In it are the following lines...
OPEN ref_csr FOR
select * from (
SELECT TEST('Y') FROM DUAL --this returns a cursor
UNION
SELECT TEST('N') FROM DUAL --this returns a cursor
);
return ref_csr;
This returns a ref cursor as well, although not the data I am looking for. The ref cursor is returning 2 ref cursors instead, one from each time it was called the TEST function.
Doing some research, it doesnt look like I'm able to append any data to a ref_cursor. I could use a pipeline, but again, I do not want to use a type. If there is an easy way to accomplish the above, that would be great, but I dont think there is. This is why my question is to make a temp table using the returned cursors above.
Here is an example I'm kinda looking for (pseudo code)...
OPEN ref_csr FOR
select * from (
SELECT TEST('Y') FROM DUAL );
OPEN ref_csr2 FOR
select * from (
SELECT TEST('N') FROM DUAL );
CREATE GLOBAL TEMPORARY TABLE tmp
AS ref_csr;
LOOP
--add all cursor data to tmp table
END LOOP;
-- when done populating data in table, return a cursor of the populated table
Is any of this even possible?
I have to admit
"I could use a pipeline, but again, I do not want to use a type"
is a bit like saying "I want to store some data, but I dont want to use a table" :-)
I'm always bemused by the concept of not wanting to take advantage of all the features available to you.
But if you want to restrict yourself in that way, you could build something using dynamic SQL, eg
SQL> create or replace
2 package pkg is
3 type t_sql_list is table of varchar2(1000) index by pls_integer;
4
5 type t_bind_list is table of int index by pls_integer;
6
7 procedure init;
8 procedure add_cursor(p_sql varchar2, p_bind int);
9 function refcur return sys_refcursor;
10 end;
11 /
Package created.
SQL>
SQL> create or replace
2 package body pkg is
3 l_sql t_sql_list;
4 l_bind t_bind_list;
5
6 procedure init is
7 begin
8 l_sql.delete;
9 l_bind.delete;
10 end;
11
12 procedure add_cursor(p_sql varchar2, p_bind int) is
13 begin
14 l_sql(l_sql.count+1) := p_sql;
15 l_bind(l_bind.count+1) := p_bind;
16 end;
17
18 function refcur return sys_refcursor is
19 l_full_sql varchar2(32767);
20 rc sys_refcursor;
21 l_cur int := dbms_sql.open_cursor;
22 l_exec int;
23 begin
24 for i in 1 .. l_sql.count loop
25 l_full_sql := l_full_sql|| l_sql(i)||' '|| case when i < l_sql.count then 'union all ' end;
26 end loop;
27
28 dbms_sql.parse(l_cur,l_full_sql,dbms_sql.native);
29 for i in 1 .. l_bind.count loop
30 dbms_sql.bind_variable(l_cur,'b',l_bind(i));
31 end loop;
32 l_exec := dbms_sql.execute(l_cur);
33
34 rc := DBMS_SQL.TO_REFCURSOR(l_cur);
35 return rc;
36 end;
37
38 end;
39 /
Package body created.
SQL>
SQL> sho err
No errors.
SQL>
SQL> exec pkg.init;
PL/SQL procedure successfully completed.
SQL> exec pkg.add_cursor('select object_id, object_name from all_Objects where object_id < :b',10);
PL/SQL procedure successfully completed.
SQL> exec pkg.add_cursor('select object_id, object_name from all_Objects where object_id < :b',20);
PL/SQL procedure successfully completed.
SQL>
SQL> variable rc refcursor
SQL>
SQL> exec :rc := pkg.refcur;
PL/SQL procedure successfully completed.
SQL>
SQL> print rc
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
2 C_OBJ#
3 I_OBJ#
4 TAB$
5 CLU$
6 C_TS#
7 I_TS#
8 C_FILE#_BLOCK#
9 I_FILE#_BLOCK#
10 C_USER#
11 I_USER#
12 FET$
13 UET$
14 SEG$
15 UNDO$
16 TS$
17 FILE$
18 OBJ$
19 IND$
2 C_OBJ#
3 I_OBJ#
4 TAB$
5 CLU$
6 C_TS#
7 I_TS#
8 C_FILE#_BLOCK#
9 I_FILE#_BLOCK#
10 C_USER#
11 I_USER#
12 FET$
13 UET$
14 SEG$
15 UNDO$
16 TS$
17 FILE$
18 OBJ$
19 IND$
36 rows selected.
SQL>
SQL>
SQL>