Skip to Main Content
  • Questions
  • How to combine the result set from two ref cursors.

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Shailandra.

Asked: June 14, 2002 - 10:08 am UTC

Last updated: February 25, 2005 - 5:41 pm UTC

Version: 9.0.1

Viewed 10K+ times! This question is

You Asked

Tom,

I have five functions returning set of rows thru ref cursor. I need to do the union or intersection on the their result set and return the final set of rows.

It is like if the function returns more than a defined no of rows then do the intersection otherwise do the union on the resultset. The other condition is not to exceed the total result set to max_limit. I have to stop processing after the limit (Throttle Limit).

Currently I am doing it using a global temporary table. For implementing this, I have copied one functions as procedure which is passed with different parameters and I am inserting the result set into gloabl temprorary table. Then while calling other function, I do the required union/intersection of its resultset and records from temp table. The current process is woking fine as required.

But I feel this is inefficient way of doing this, inserting records into temp table involves some overhead. This is causing the total procedure to slow down a bit. I also need to reduce the process time to as min as possible.

Can you suggest some other faster way to do it.

and Tom said...

You can use pipelined functions perhaps. Here is an example:

tkyte@TKYTE9I.US.ORACLE.COM> create or replace type myScalarType
2 as object
3 ( x int, y date, z varchar2(80) )
4 /

Type created.

tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM> create or replace type myTableType
2 as table of myScalarType
3 /

Type created.

tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM> create or replace
2 function pipeline_demo
3 ( p_nrows_to_generate in number )
4 return myTabletype
5 PIPELINED
6 as
7 begin
8 for i in 1 .. p_nrows_to_generate
9 loop
10 pipe row( myScalarType( i, sysdate+i, 'row ' || i ) );
11 end loop;
12 return;
13 end;
14 /

Function created.

tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM> select * from table( pipeline_demo( 5 ) );

X Y Z
---------- --------- -------------------------------------------------------------
-
1 15-JUN-02 row 1
2 16-JUN-02 row 2
3 17-JUN-02 row 3
4 18-JUN-02 row 4
5 19-JUN-02 row 5

tkyte@TKYTE9I.US.ORACLE.COM>


you can write a function that fetches from a ref cursor -- returns the result and decides

a) when to stop
b) when to get another result set to return as well.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Combining resultset.

Shailandra, June 14, 2002 - 5:38 pm UTC

Tom,

Can you please give an example how to query from a ref cursor being returned from the function. I tried various combination of fetching the data from ref cursor, but it does not work.

e.g.
select * from table(function_returning_ref_cursor);
select * from function_returning_ref_cursor;
var r refcursor;
exec :r := function_returning_ref_cursor();
select * from r; or
select * from :r;
-- Here only print r; worked.

I also looked at Oracle documentation, they talk about Pl/SQL tables, Collections which does not fit to my requirement.

The example you gave will be useful in creating memory tables which can be later queried for combining the resultset from two such memory table, but again how to populate these memory tables from data returned thru ref cursor. A small example of fetching the data from ref cursor being returned from a function will be very useful.

Thanks

Tom Kyte
June 14, 2002 - 8:45 pm UTC

You need to use a procedural command -- FETCH. You FETCH from a cursor.

Oracle9i allows you to pass a ref cursor to a plsql function in a sql statement -- but even that is not what you are asking for.

You just need to FETCH from it (that is what print :r is doing)



Pipelined functions in packages

Pratap Deshmukh, June 19, 2002 - 10:03 am UTC

Hi Tom

When I try selecting from a pipelined function written in a package then it gives an internal error

ORA-00600: internal error code, arguments: [17274], [4], [], [], [], [], [], []

The function works fine when I use a standalone function. Does pipelined functions not work from packages or am I hitting a bug. (Searched on Metalink but found no info there)

You have also used standalone functions in all the examples you have given on pipelined functions.

Best Regards

Pratap


Tom Kyte
June 19, 2002 - 9:41 pm UTC

ora-00600 = bug, always.

Contact support to get one going.

It should be just dandy in a package.

Fetching from a ref cursor into another %rowtype

Sujata, January 13, 2003 - 4:11 pm UTC

Hi Tom,
I have a procedure that has an output refcursor say
P_1 (...a parameter list....,c_out_cur out a_types_pkg.T_REFCUR)
(where a_types_pkg has:
TYPE T_REFCUR IS REF CURSOR;)
This procedure will have a statement to open the cursor at the end..something like
OPEN C_OUT_CUR FOR VSQL;
(where VSQL is being built dynamically in the procedure..using NDS)
Now the select columns from this procedure could differ...
for example it could be "select emp_name, salary, dept"
or "select emp_name, salary"

Now, if I want to use this procedure in another procedure..
like p_2..
I will declare a variable in p_2 as
c_out_cur a_types_pkg.T_REFCUR;
and in the body...I will call p_1
P_1(..in_parameters...,c_out_cur);
after this step..I need to fetch it into a variable, or a list of variables..or a record...

Question:
How do I declare a %rowtype for a REF cursor..so, i can fetch into that..I have tried..it gave me an error.....






Tom Kyte
January 13, 2003 - 7:04 pm UTC

You won't be - you'll be using DBMS_SQL instead which has procedures to

o describe a result set so you can discover the names, types, lengths and number of columns

o a define column procedure so you can define output for the i'th column of a dynamic query

o a variable value procedure so you can retreive the i'th column of a dynamic query.

You will NOT use a ref cursor for this in plsql since plsql cannot describe a ref cursor at runtime and plsql cannot fetch INTO <unknown number/types of columns> at runtime.



Combine multiple ref cursors

Mark, February 25, 2005 - 9:44 am UTC

Tom, 

Thanks again for your time.  Here is what I got:

SQL> CREATE TABLE TEST_USER_ROLES (
  2        USER_ID       NUMBER,
  3        ROLE_ID       NUMBER)
  4  /

Table created.

SQL> INSERT INTO TEST_USER_ROLES
  2              (USER_ID, ROLE_ID)
  3       VALUES (1, 1)
  4  /

1 row created.

SQL> INSERT INTO TEST_USER_ROLES
  2              (USER_ID, ROLE_ID)
  3       VALUES (1, 2)
  4  /

1 row created.

SQL> INSERT INTO TEST_USER_ROLES
  2              (USER_ID, ROLE_ID)
  3       VALUES (2, 1)
  4  /

1 row created.

SQL> INSERT INTO TEST_USER_ROLES
  2              (USER_ID, ROLE_ID)
  3       VALUES (2, 3)
  4  /

1 row created.

SQL> COMMIT
  2  /

Commit complete.

SQL> 
SQL> CREATE OR REPLACE TYPE TEST_SCALARTYPE AS OBJECT (
  2     X   INT,
  3     Y   DATE,
  4     Z   VARCHAR2 (80)
  5  )
  6  /

Type created.

SQL> 
SQL> CREATE OR REPLACE TYPE TEST_TABLETYPE AS TABLE OF TEST_SCALARTYPE
  2  /

Type created.

SQL> 
SQL> CREATE OR REPLACE PROCEDURE TEST_P1 (
  2     P_CUR   IN OUT   SYS_REFCURSOR)
  3  IS
  4  BEGIN
  5     OPEN P_CUR FOR
  6        SELECT 1, SYSDATE, 'FROM_FIRST_CURSOR'
  7          FROM DUAL;
  8  END;
  9  /

Procedure created.

SQL> 
SQL> CREATE OR REPLACE PROCEDURE TEST_P2 (
  2     P_CUR   IN OUT   SYS_REFCURSOR)
  3  IS
  4  BEGIN
  5     OPEN P_CUR FOR
  6        SELECT 2, SYSDATE, 'FROM_SECOND_CURSOR'
  7          FROM DUAL;
  8  END;
  9  /

Procedure created.

SQL> 
SQL> CREATE OR REPLACE PROCEDURE TEST_P3 (
  2     P_CUR   IN OUT   SYS_REFCURSOR)
  3  IS
  4  BEGIN
  5     OPEN P_CUR FOR
  6        SELECT 3, SYSDATE, 'FROM_THIRD_CURSOR'
  7          FROM DUAL;
  8  END;
  9  /

Procedure created.

SQL> 
SQL> CREATE OR REPLACE FUNCTION TEST_PIPELINE_DEMO (
  2     P_USER_ID   IN   NUMBER)
  3     RETURN TEST_TABLETYPE PIPELINED
  4  AS
  5     L_X     INT;
  6     L_Y     DATE;
  7     L_Z     VARCHAR2 (80);
  8     P_CUR   SYS_REFCURSOR;
  9  BEGIN
 10     FOR X IN (SELECT ROLE_ID
 11                 FROM TEST_USER_ROLES
 12                WHERE USER_ID = P_USER_ID) LOOP
 13        CASE
 14           WHEN X.ROLE_ID = 1 THEN
 15              TEST_P1 (P_CUR);
 16           WHEN X.ROLE_ID = 2 THEN
 17              TEST_P2 (P_CUR);
 18           WHEN X.ROLE_ID = 3 THEN
 19              TEST_P3 (P_CUR);
 20        END CASE;
 21  
 22        LOOP
 23           FETCH P_CUR
 24            INTO L_X, L_Y, L_Z;
 25  
 26           EXIT WHEN P_CUR%NOTFOUND;
 27           PIPE ROW (TEST_SCALARTYPE (L_X, L_Y, L_Z));
 28        END LOOP;
 29     END LOOP;
 30  
 31     RETURN;
 32  END;
 33  /

Function created.

SQL> 
SQL> CREATE OR REPLACE PROCEDURE TEST_MAIN (
  2     P_USER_ID   IN       NUMBER,
  3     P_CUR       IN OUT   SYS_REFCURSOR)
  4  IS
  5  BEGIN
  6     OPEN P_CUR FOR
  7        SELECT X, Y, Z
  8          FROM TABLE (TEST_PIPELINE_DEMO (P_USER_ID));
  9  END;
 10  /

Procedure created.

SQL> 
SQL> -- SELECT USING FUNCTION
SQL> 
SQL> SELECT X, Y, Z
  2    FROM TABLE (TEST_PIPELINE_DEMO (1))
  3  /

         X Y         Z
---------- --------- -------------------------------------------------
         1 25-FEB-05 FROM_FIRST_CURSOR
         2 25-FEB-05 FROM_SECOND_CURSOR

SQL> 
SQL> -- SELECT USING PROCEDURE RETURNING REF CURSOR 
SQL> SET SERVEROUTPUT ON
SQL> SET PAGESIZE 8000
SQL> SET autoprint on
SQL> VAR p_cur refcursor
SQL> 
SQL> 
SQL> EXEC TEST_MAIN(1,:p_cur);

PL/SQL procedure successfully completed.


         X Y         Z
---------- --------- -------------------------------------------------
         1 25-FEB-05 FROM_FIRST_CURSOR
         2 25-FEB-05 FROM_SECOND_CURSOR

SQL> 
SQL> EXEC TEST_MAIN(2,:p_cur);

PL/SQL procedure successfully completed.


         X Y         Z
---------- --------- -------------------------------------------------
         1 25-FEB-05 FROM_FIRST_CURSOR
         3 25-FEB-05 FROM_THIRD_CURSOR



We have 15 roles right now.  Each role has its own logic that builds a recordset.  ALL recordset have the same columuns and are combined to make up a users task list.  
Most users are in one to two roles, but some can be as many as six or seven.  The TEST_MAIN proc is what the app calls.  

Questions
1:  Is this how you would do it?  

2:  In the Pipline function, can you define the columns as a rowtype instead of defining each one?  We will have 18 columns now.  

Thanks..


 

Tom Kyte
February 25, 2005 - 5:41 pm UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 <code>

you could use dynamic sql and build "a query"

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library