CODEWITHSUNDEEP
sqlsql-serveroracle-database
Ram
Ram

Reputation: 337

How get list of functions inside a package in oracle

I've a requirement like , I need to get all the function names inside a package. Actually I'm a SQL Server guy. I'm new to Oracle. In SQL Server I've options like view dependencies so that i can get all dependencies. is there any way to get all function names inside a package in oracle.

Thanks for your comments.

Upvotes: 1

Views: 5108

Answers (2)

Rax
Rax

Reputation: 838

In Oracle 23c or perhaps a few previous versions, this query will show all functions and procedures in all packages and the schema itself. Features:

  • METHOD_TYPE determines is a FUNCTION or PROCEDURE.
  • METHOD_PARENT_TYPE: determines if the parent is a PACKAGE the SCHEMA
  • METHOD_CONTAINER_NAME: if method is contained in a package, then the package name, else the schema name (owner)
  • If the Package contains no public Functions or Procedures, then it is excluded
SELECT
     -- owner is schema
     P.OWNER SCHEMA_NAME
, CASE WHEN P.OBJECT_TYPE IN ('PACKAGE') THEN p.OBJECT_NAME ELSE p.OWNER END  METHOD_CONTAINER_NAME
     , CASE WHEN P.PROCEDURE_NAME IS NULL THEN P.OBJECT_NAME ELSE P.PROCEDURE_NAME END METHOD_NAME
     , CASE WHEN P.OBJECT_TYPE IN ('PACKAGE') THEN 'PACKAGE' ELSE 'SCHEMA' END METHOD_PARENT_TYPE
     -- when no return param, then must be a procedure
     , CASE WHEN A.OBJECT_ID IS NULL THEN 'PROCEDURE' ELSE A.METHOD_TYPE END METHOD_TYPE
     , P.*
     , A.*
FROM ALL_PROCEDURES P
        LEFT JOIN (SELECT A1.OWNER, A1.OBJECT_ID, CASE WHEN MIN(A1.POSITION) = 0 THEN 'FUNCTION' ELSE 'PROCEDURE' END METHOD_TYPE, COUNT(A1.OBJECT_ID) PARAM_COUNT
                   FROM ALL_ARGUMENTS A1
                   GROUP BY A1.OWNER, A1.OBJECT_ID) A ON P.OWNER = A.OWNER AND P.OBJECT_ID = A.OBJECT_ID
WHERE P.OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE', 'PACKAGE')
  AND P.OWNER = 'MAIN'
  AND ((
   --ALL_PROCEDURES has an extra row for every package, like a package level header but procedure_name is null, eliminate this
   P.OBJECT_TYPE = 'PACKAGE' AND PROCEDURE_NAME IS NOT NULL) OR P.OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE'))

Upvotes: 0

Dba
Dba

Reputation: 6649

You can query ALL_PROCEDURES & USER_PROCEDURES views to get the list of functions and procedures inside a package.

Try like this,

SELECT object_name package_name, 
       procedure_name
  FROM user_procedures
 WHERE object_name = '<PACKAGE_NAME>';

Upvotes: 3

Related Questions