| select * from TABLE(func(CURSOR( << no query, but REF CURSOR>>))) - Is this possible? [message #176529] |
Thu, 08 June 2006 11:42  |
rasa
Messages: 45
Registered: February 2006
|
Member |
|
|
Here is my problem. Rather than passing an explicit SQL query within the CURSOR(..) expression, can I pass a REF CURSOR (Cursor Variable) to a TABLE function? Or is there a restriction that I can only pass a SQL Query into the CURSOR(..) expression?
Please help.
Here is the code.
CREATE OR REPLACE TYPE TEST_VARCHAR2_SET AS TABLE OF VARCHAR2(64);
/
CREATE OR REPLACE FUNCTION Test_Stream ( p_CUR IN SYS_REFCURSOR)
RETURN TEST_VARCHAR2_SET
PIPELINED
IS
i_REC VARCHAR2(30);
BEGIN
LOOP
FETCH p_CUR INTO i_REC;
EXIT WHEN p_CUR%NOTFOUND;
PIPE ROW (' *** ' || i_REC);
END LOOP;
CLOSE p_CUR;
RETURN;
END;
/
Now, when I execute a SQL query like this, everything works fine.
SELECT *
FROM
TABLE(Test_Stream(CURSOR(SELECT USERNAME FROM ALL_USERS)))
However, I want to be able to use the same type of Query within PL/SQL, wherein I will replace the "SELECT USERNAME FROM ALL_USERS" query by a cursor variable.
I am thinking something like this (pseudocode)
DECLARE
BEGIN
-- I am opening first REF cursor that has USERNAME values
open p_REF_CURSOR FOR
SELECT USERNAME FROM ALL_USERS;
-- Now, I want to pass this "pointer" to the PIPELINED function
-- like this...Is this feasible?
open p_REF_CURSOR_2 FOR
SELECT * FROM TABLE (TEST_STREAM (CURSOR ( p_REF_CURSOR ) ) );
END;
Is this even possible? Can I pass a REF CURSOR variable to a TABLE function by wrapping that variable within a CURSOR(...) expression as shown above? Currently am getting errors, if I try to do that. Please help.
|
|
|
|
| Re: select * from TABLE(func(CURSOR( << no query, but REF CURSOR>>))) - Is this possible [message #176612 is a reply to message #176529] |
Thu, 08 June 2006 22:02  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I haven't tried it, but can't you just take out the CURSOR() function? It's purpose is to convert a SQL statement into a REF CURSOR. If you have a variable that is already a REF CURSOR then you shouldn't need the function.
DECLARE
BEGIN
-- I am opening first REF cursor that has USERNAME values
open p_REF_CURSOR FOR
SELECT USERNAME FROM ALL_USERS;
-- Now, I want to pass this "pointer" to the PIPELINED function
-- like this...Is this feasible?
open p_REF_CURSOR_2 FOR
SELECT * FROM TABLE (TEST_STREAM (p_REF_CURSOR) );
END;
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
