Home » SQL & PL/SQL » SQL & PL/SQL » How to find out whether an object is function/procedure under a package using oracle dictionery ?
How to find out whether an object is function/procedure under a package using oracle dictionery ? [message #188009] Wed, 16 August 2006 13:37 Go to next message
cmhasan
Messages: 3
Registered: August 2006
Location: Boston, MA
Junior Member
I can find argument details of object under any package using
SELECT OBJECT_NAME, OBJECT_ID FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'PACKAGE' AND OWNER = 'abcdba';

SELECT DISTINCT OBJECT_NAME FROM ALL_ARGUMENTS WHERE PACKAGE_NAME = 'SPP_ABC' AND OWNER = 'abcdba' ORDER BY OBJECT_NAME;

SELECT ARGUMENT_NAME, POSITION, IN_OUT, DATA_TYPE FROM ALL_ARGUMENTS WHERE PACKAGE_NAME = 'SPP_ABC' AND OWNER = 'abcdba' AND OBJECT_NAME = 'OBJABC' ORDER BY POSITION;

But I dont know how to find out whether the object is a function or procedure. The objects not under any package can be found out from all_objects using object_type. Any help is appreciated.
Re: How to find out whether an object is function/procedure under a package using oracle dictionery [message #188012 is a reply to message #188009] Wed, 16 August 2006 13:53 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Does
SQL> DESC PACKAGE_NAME
provide answer to your question?
Re: How to find out whether an object is function/procedure under a package using oracle dictionery [message #188014 is a reply to message #188012] Wed, 16 August 2006 13:59 Go to previous messageGo to next message
cmhasan
Messages: 3
Registered: August 2006
Location: Boston, MA
Junior Member
I know desc give the details but I want something which can be run from outside oracle like java program. So using data dictionery or metadata function are my options. desc has different format for different type of objects.
Re: How to find out whether an object is function/procedure under a package using oracle dictionery [message #188094 is a reply to message #188014] Thu, 17 August 2006 01:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to look at User_Arguments.
If there is a row for the Proc/Function you are looking at that has no name, but does have a data type, then it is a Function (it's the entry for the funtions Return value)
Re: How to find out whether an object is function/procedure under a package using oracle dictionery [message #188096 is a reply to message #188014] Thu, 17 August 2006 02:06 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
If you have an out parameter at position 0, you have a function. So I guess you could use something like:
SELECT   object_name
     ,   decode(min(position) over (partition by object_name)
               ,0, 'FUNCTION', 'PROCEDURE') x
     ,   argument_name
     ,   data_type
     ,   in_out
FROM     all_arguments
WHERE    package_name = 'yourpackage'
AND      owner        = 'youruser'
ORDER BY object_name, overload, SEQUENCE


MHE
icon12.gif  Re: How to find out whether an object is function/procedure under a package using oracle dictionery [message #188244 is a reply to message #188096] Thu, 17 August 2006 09:43 Go to previous message
cmhasan
Messages: 3
Registered: August 2006
Location: Boston, MA
Junior Member
Thanks to both Maaher and JRowbottom. Your replys solved my problem.
Previous Topic: Complex query on hierarchy
Next Topic: REF CURSOR - With For Loop
Goto Forum:
  


Current Time: Thu Dec 08 02:04:54 CST 2016

Total time taken to generate the page: 0.10650 seconds