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
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
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.....
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..
February 25, 2005 - 5:41 pm UTC