In Which Package Is This Procedure Placed?

Typical scenario: in a project’s design phase, procedureA is meant to be placed in packageX. During implementation, packages are refactored:  packageX gets split into packageY and packageZ. All goes well — the application gets deployed and is running merrily – till a change request comes in.

A new developer refers to the design documentation to understand the change request.

The design documentation should have been updated to reflect the new package structure — but it isn’t.

packageX is nowhere to be found in the database.

How should the developer find out the package in which this procedure lies?

ALL_PROCEDURES to the rescue

For information about stored procedures in Oracle, look up the data dictionary view ALL_PROCEDURES.

From Oracle’s documentation:

ALL_PROCEDURES lists all functions and procedures that are accessible to the current user, along with associated properties. For example, ALL_PROCEDURES indicates whether or not a function is pipelined, parallel enabled or an aggregate function.

Among these properties is also the answer to the question that interests us: in which package will we find the procedure?

Related views: DBA_PROCEDURES, USER PROCEDURES

As it happens with data dictionary views in Oracle, corresponding to the ALL* view are the DBA* and USER* views.

DBA_PROCEDURES lists details of all stored procedures available in the database.

USER_PROCEDURES lists details of stored procedures owned by the current user.

Select from ALL_PROCEDURES

Run a query to get the listing for procedureA in ALL_PROCEDURES:

SQL> select owner
  2       , object_name
  3  from all_procedures
  4  where procedure_name = 'PROCEDUREA';

OWNER                          OBJECT_NAME
------------------------------ -------------
HR                             PACKAGEY

The result tells us what we were looking for: that procedureX was created in schema HR, package packageY.

Epilogue

After querying ALL_PROCEDURES to identify the package name for your procedure, go fix the design documentation!