Oracle db view/system table to check if given part of a package is a Procedure or a Function

I am using the Oracle system tables to get some metadata about the Packages in our database.

Here is one of my queries:

select AP.PROCEDURE_NAME
  from ALL_PROCEDURES ap
 where ap.object_name = :object_name
   and ap.owner=:owner
   and ap.procedure_name is not NULL
   and ap.procedure_name like :procedure_name

I also want to find out if the given object is either a function, a procedure or a type. I can't seem to find a direct way to pull the data directly from a table.

Please Note: I don't want to have to parse it from all_sources.

Answers


It turns out that there is a view all_arguments where you can get all the parameters for a method in a package. When the method is a function there is an argument with a null name representing the return value. So if you join against all_arguments you can determine if a given all_procedures entry is a function or a procedure. What follows is an example showing such a select.

select CASE (Select count(*) from ALL_ARGUMENTS aa 
                       where aa.object_name=ap.procedure_name
                         and aa.object_id = ap.object_id 
                         and argument_name is null)
         WHEN 1 THEN 'FUNCTION'
         WHEN 0 THEN 'PROCEDURE'
         ELSE ''
       END as is_function, ap.*
  from all_procedures ap
 where ap.object_name like '<package name>'

SQL> select distinct object_type from user_procedures;

OBJECT_TYPE
-------------------
PROCEDURE
PACKAGE
TRIGGER
FUNCTION

I suspect one won't have any choice. At least it's well formatted -- all the types begin with TYPE, and so on.

You could use SQL*Plus and the DESC command, which does work on packages:

SQL> desc dbms_application_info;
PROCEDURE READ_CLIENT_INFO
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CLIENT_INFO                    VARCHAR2                OUT
PROCEDURE READ_MODULE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MODULE_NAME                    VARCHAR2                OUT
 ACTION_NAME                    VARCHAR2                OUT
PROCEDURE SET_ACTION
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ACTION_NAME                    VARCHAR2                IN
PROCEDURE SET_CLIENT_INFO
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CLIENT_INFO                    VARCHAR2                IN
PROCEDURE SET_MODULE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MODULE_NAME                    VARCHAR2                IN
 ACTION_NAME                    VARCHAR2                IN
PROCEDURE SET_SESSION_LONGOPS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RINDEX                         BINARY_INTEGER          IN/OUT
 SLNO                           BINARY_INTEGER          IN/OUT
 OP_NAME                        VARCHAR2                IN     DEFAULT
 TARGET                         BINARY_INTEGER          IN     DEFAULT
 CONTEXT                        BINARY_INTEGER          IN     DEFAULT
 SOFAR                          NUMBER                  IN     DEFAULT
 TOTALWORK                      NUMBER                  IN     DEFAULT
 TARGET_DESC                    VARCHAR2                IN     DEFAULT
 UNITS                          VARCHAR2                IN     DEFAULT

By iterating over the packages, and parsing the results -- it's an easier structure to parse than DBA_SOURCE -- you might get what you want. A caveat to remember; packages support overloading so you could have both FUNCTION foo(date) RETURN date, and PROCEDURE foo(varchar). Matching just by name may not be enough.


Need Your Help

“Screen” effect in Java 2D graphics

java graphics screen composite alpha

This is a question that's been bugging me for some time now:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.