oracle - ALL_PROCEDURES view doesn't show the PROCEDURE_NAME

Question

Why can't I see my procedure in user_procedures view? Why is the procedure_name filter on all_procedures view returns no rows.

This question is primarily to aid those who are searching for a similar question. I hope that anyone looking for such question would find a answer here.

Test case :

SQL> show user
USER is "LALIT"
SQL> CREATE OR REPLACE
  2  PROCEDURE new_proc
  3  AS
  4  BEGIN
  5    NULL;
  6  END;
  7  /

Procedure created.

SQL>
SQL> SELECT owner,
  2    object_name,
  3    procedure_name,
  4    object_type
  5  FROM all_procedures
  6  WHERE owner='LALIT'
  7  AND procedure_name='NEW_PROC';

no rows selected

SQL>

Answer 1

From documentation,

ALL_PROCEDURES lists all functions and procedures, along with associated properties. For example, ALL_PROCEDURES indicates whether or not a function is pipelined, parallel enabled or an aggregate function. If a function is pipelined or an aggregate function, the associated implementation type (if any) is also identified.

It doesn't clarify whether it would list a STAND ALONE PROCEDURE and a procedure wrapped in a PACKAGE the same way or does it consider it differently. Since, the procedure_name would not list the name of a stand alone procedure as seen in the test case in the question above.

PROCEDURE_NAME column will only have the procedure name for the procedures which are part of a PACKAGE. For STAND ALONE PROCEDURES you need to use OBJECT_NAME.

SQL> show user
USER is "LALIT"
SQL> CREATE OR REPLACE
  2  PROCEDURE new_proc
  3  AS
  4  BEGIN
  5    NULL;
  6  END;
  7  /

Procedure created.

SQL>
SQL> SELECT owner,
  2    object_name,
  3    procedure_name,
  4    object_type
  5  FROM all_procedures
  6  WHERE owner='LALIT'
  7  AND object_name='NEW_PROC';

OWNER OBJECT_NAME     PROCEDURE_NAME  OBJECT_TYPE
----- --------------- --------------- ---------------
LALIT NEW_PROC                        PROCEDURE

SQL>

You could get the list of procedures using procedure_name only if it is wrapped in a package.

SQL> -- package
SQL> CREATE OR REPLACE
  2  PACKAGE new_pack
  3  IS
  4    PROCEDURE new_proc;
  5  END new_pack;
  6  /

Package created.

SQL>
SQL> -- package body with a procedure
SQL> CREATE OR REPLACE
  2  PACKAGE BODY new_pack
  3  IS
  4  PROCEDURE new_proc
  5  IS
  6  BEGIN
  7    NULL;
  8  END;
  9  END new_pack;
 10  /

Package body created.

SQL> SELECT owner,
  2    object_name,
  3    procedure_name,
  4    object_type
  5  FROM all_procedures
  6  WHERE owner='LALIT'
  7  AND procedure_name='NEW_PROC';

OWNER OBJECT_NAME     PROCEDURE_NAME  OBJECT_TYPE
----- --------------- --------------- -----------
LALIT NEW_PACK        NEW_PROC        PACKAGE

SQL>

Now you could see the procedure_name as the actual procedure, and the object_name as the package_name.

Of course, mostly in production systems we would have packages, and not stand alone procedures. But, while testing and demos, we do compile and run stand alone procedures. So, it is good to know how Oracle maintains the information in *_PROCEDURES views.