Getting Data From Weak Ref Cursors [message #391053] |
Tue, 10 March 2009 16:39  |
sgengineer
Messages: 6 Registered: March 2009
|
Junior Member |
|
|
We are currently using PL/SQL functions and procedures that return weak REF CURSORS to our Java applications to retrieve/modify data in our databases (and it's working great).
I've run into a problem though that I can't seem to find a solution for now that I'm trying to write a PL/SQL function to get that same data from those functions.
Here's an example:
TYPE weak_cursor IS REF CURSOR;
FUNCTION function_a (...)
RETURN weak_cursor
IS
cu_return weak_cursor;
BEGIN
OPEN cu_return FOR
SELECT some_data_a FROM some_table_a;
RETURN cu_return;
END function_a;
.
.
.
FUNCTION function_z (...)
RETURN weak_cursor
IS
cu_return weak_cursor;
BEGIN
OPEN cu_return FOR
SELECT some_data_z FROM some_table_z;
RETURN cu_return;
END function_z;
With functions a through z, ideally I want to be able to do something like:
SELECT * FROM function_a(...)
WHERE some_parameter = some_criteria
It seems so simple, yet Oracle seems to make it very difficult to do, if not impossible. Any help would be greatly appreciated
We are using Oracle Version 11.1
|
|
|
|
Re: Getting Data From Weak Ref Cursors [message #391057 is a reply to message #391055] |
Tue, 10 March 2009 17:14   |
sgengineer
Messages: 6 Registered: March 2009
|
Junior Member |
|
|
BlackSwan wrote on Tue, 10 March 2009 17:08 | >SELECT * FROM function_a(...) WHERE some_parameter = some_criteria
Such a "construct" makes no sense.
This pig won't fly.
What problem are you really trying to solve?
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
|
That's exactly the problem I'm trying to solve. I'm trying to SELECT from the results of one function in another function.
|
|
|
|
Re: Getting Data From Weak Ref Cursors [message #391061 is a reply to message #391059] |
Tue, 10 March 2009 17:29   |
sgengineer
Messages: 6 Registered: March 2009
|
Junior Member |
|
|
BlackSwan wrote on Tue, 10 March 2009 17:19 | > I'm trying to SELECT from the results of one function in another function.
WHY? Why? why do you think this is required or desired?
|
Well, in my specific case I have a set of predefined functions that are working and deployed to our production environment... so they can't be changed without modifying a large code base. I now need to take the data from these functions, and process them further.
Non-specifically, I ask why wouldn't you want to do it? The whole concept of functions is modularity and building functions that call other functions. That's like asking why do you want to build a wall out of bricks?
Any help would be appreciated, thank you.
|
|
|
|
Re: Getting Data From Weak Ref Cursors [message #391063 is a reply to message #391062] |
Tue, 10 March 2009 17:58   |
sgengineer
Messages: 6 Registered: March 2009
|
Junior Member |
|
|
BlackSwan wrote on Tue, 10 March 2009 17:53 | Simply put "SELECT * FROM function_a(...)" is NOT valid SQL.
Regardless of your desires or projections, you need to find some other solution.
|
Yes, you would be correct. Do you or anyone else here know of another solution? I thought that's what I was asking, sorry if I confused you.
|
|
|
|
Re: Getting Data From Weak Ref Cursors [message #391065 is a reply to message #391064] |
Tue, 10 March 2009 18:13   |
sgengineer
Messages: 6 Registered: March 2009
|
Junior Member |
|
|
Maybe I need to ask a simpler question:
How can I retrieve data from a weak ref cursor in PL/SQL? The data is unknown. The number of columns are unknown. I thought that was the definition of a weak ref cursor, but I'll state it here so you understand.
|
|
|
|
|
Re: Getting Data From Weak Ref Cursors [message #391302 is a reply to message #391065] |
Wed, 11 March 2009 12:41  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The problem is (as I understand it) that because you're using a weak cursor, Oracle has no way of knowing what the data it's expecting is, and so no way of knowing if the query is valid.
If you were using a Strong cursor, you could use a Pipelined function to construct something like: SELECT *
FROM TABLE (pipeline_function(ref_cursor))
I don't know of a generic way of doing it for weak cursors other than using DBMS_SQL.
See @Black_Swan - is it so hard to be polite, non-confrontational, and (dare I say it) marginally helpful?
|
|
|