Home » SQL & PL/SQL » SQL & PL/SQL » Getting Data From Weak Ref Cursors (Oracle 11.1)
Getting Data From Weak Ref Cursors [message #391053] Tue, 10 March 2009 16:39 Go to next message
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 Confused

We are using Oracle Version 11.1

Re: Getting Data From Weak Ref Cursors [message #391055 is a reply to message #391053] Tue, 10 March 2009 17:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>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/
Re: Getting Data From Weak Ref Cursors [message #391057 is a reply to message #391055] Tue, 10 March 2009 17:14 Go to previous messageGo to next message
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 #391059 is a reply to message #391053] Tue, 10 March 2009 17:19 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> 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?


Re: Getting Data From Weak Ref Cursors [message #391061 is a reply to message #391059] Tue, 10 March 2009 17:29 Go to previous messageGo to next message
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 #391062 is a reply to message #391053] Tue, 10 March 2009 17:53 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Simply put "SELECT * FROM function_a(...)" is NOT valid SQL.
Regardless of your desires or projections, you need to find some other solution.
Re: Getting Data From Weak Ref Cursors [message #391063 is a reply to message #391062] Tue, 10 March 2009 17:58 Go to previous messageGo to next message
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 #391064 is a reply to message #391053] Tue, 10 March 2009 18:05 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

>Do you or anyone else here know of another solution?

What are the inputs?
What are the requirements?
What are the desired/expected results?

Based upon what metrics & what values would an independent observer conclude this problem has been solved?

If these functions allow Java code to obtain result sets from Oracle & additional processing is required, why can't the Java code be modified to do what is needed?
Re: Getting Data From Weak Ref Cursors [message #391065 is a reply to message #391064] Tue, 10 March 2009 18:13 Go to previous messageGo to next message
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 #391093 is a reply to message #391065] Wed, 11 March 2009 00:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use DBMS_SQL package.

Regards
Michel
Re: Getting Data From Weak Ref Cursors [message #391300 is a reply to message #391093] Wed, 11 March 2009 12:26 Go to previous messageGo to next message
sgengineer
Messages: 6
Registered: March 2009
Junior Member
Michel Cadot wrote on Wed, 11 March 2009 00:15
Use DBMS_SQL package.

Regards
Michel



Thanks Michel! I was able to find a useful blog pointing me in the right direction:

http://oracle-cookies.blogspot.com/2007/11/describe-refcursors.html
Re: Getting Data From Weak Ref Cursors [message #391302 is a reply to message #391065] Wed, 11 March 2009 12:41 Go to previous message
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?
Previous Topic: PL/SQL
Next Topic: table or view does not exist error while using db links (merged)
Goto Forum:
  


Current Time: Fri Dec 09 19:35:53 CST 2016

Total time taken to generate the page: 0.15036 seconds