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.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…