Home » SQL & PL/SQL » SQL & PL/SQL » Select against function or procedure
Select against function or procedure [message #354318] Fri, 17 October 2008 12:10 Go to next message
Setya
Messages: 3
Registered: October 2008
Location: Jakarta, Indonesia
Junior Member
Hi all,

Is it possible to create a stored procedure/function that returns records so that I can just call by the following query :

SELECT * FROM MyProcedure()

I know I can do this in SQL Server, but how to do this in Oracle ?

If it is impossible, what is your suggestion when you have a situation where you find queries that are duplicated across several caller and want to put them in 1 place to be reusable ?

Any help would be greatly appreciated.

Best Regards,

Setya
Re: Select against function or procedure [message #354320 is a reply to message #354318] Fri, 17 October 2008 12:25 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Search for SYS_REFCURSOR.

I don't know what you mean by "duplicated across several caller."
Re: Select against function or procedure [message #354323 is a reply to message #354320] Fri, 17 October 2008 13:48 Go to previous messageGo to next message
Setya
Messages: 3
Registered: October 2008
Location: Jakarta, Indonesia
Junior Member
Hi,

Thanks for the response.

I mean the same queries duplicated in several locations that I want to put them into 1 function/procedure to be reusable.

I have created the following Oracle function:

create or replace FUNCTION MyFunction RETURN SYS_REFCURSOR AS
myCursor SYS_REFCURSOR;
BEGIN
OPEN myCursor FOR SELECT * FROM MyTable;
RETURN myCursor;
END MyFunction;

I can call the above function using the following statement :

SELECT * FROM (SELECT MyFunction() FROM DUAL);

but not :

SELECT a.MyField FROM (SELECT MyFunction() FROM DUAL) a;

why ?

Setya
Re: Select against function or procedure [message #354335 is a reply to message #354323] Fri, 17 October 2008 15:21 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
See if Kevin Meade's Blog may help you in your case:

http://www.orafaq.com/node/980
Re: Select against function or procedure [message #354344 is a reply to message #354335] Fri, 17 October 2008 21:54 Go to previous message
Setya
Messages: 3
Registered: October 2008
Location: Jakarta, Indonesia
Junior Member
Thanks for the link.

I found it quite helpful.

Setya
Previous Topic: Using a keyword in a query?
Next Topic: handling recursive call in pl/sql
Goto Forum:
  


Current Time: Sun Dec 11 08:06:45 CST 2016

Total time taken to generate the page: 0.05231 seconds