Query user_objects for all PL/SQL code : user_objects « System Tables Data Dictionary « Oracle PL/SQL Tutorial

SQL>
SQL> SET PAGESIZE 66
SQL> COLUMN object_type FORMAT A20
SQL> COLUMN object_name FORMAT A30
SQL> COLUMN status FORMAT A10
SQL> BREAK ON object_type SKIP 1
SQL>
SQL> SELECT   object_type, object_name, status
  2      FROM user_objects
  3     WHERE object_type IN ('PACKAGE',
  4                          'PACKAGE BODY',
  5                          'FUNCTION',
  6                          'PROCEDURE',
  7                          'TYPE',
  8                          'TYPE BODY',
  9                          'TRIGGER'
 10                         )
 11           and rownum < 50
 12  ORDER BY object_type, status, object_name
 13  /

OBJECT_TYPE          OBJECT_NAME                    STATUS
-------------------- ------------------------------ ----------
FUNCTION             ACCOUNT_BALANCE                INVALID
                     AUTHORIZED_EMPS                INVALID
                     BIZDAYS_BETWEEN                INVALID
                     BOOKTITLE                      INVALID
                     ADD_TWO                        VALID
                     BETWEENSTRING                  VALID
                     BETWNSTR                       VALID
                     BIZDAYS_BETWN                  VALID

PACKAGE              AA_TYPES                       INVALID
                     ANYNUMS_PKG                    INVALID
                     BIDIR                          INVALID
                     BOOK_INFO                      INVALID
                     AUDIT_TRAIL_PKG                VALID
                     AUTHORS_PKG                    VALID
                     BT                             VALID

PACKAGE BODY         ANYNUMS_PKG                    INVALID
                     AUDIT_TRAIL_PKG                INVALID
                     BIDIR                          INVALID
                     BOOK_INFO                      INVALID
                     AUTHORS_PKG                    VALID
                     BT                             VALID

PROCEDURE            A                              INVALID
                     ADJUST_SALARY                  INVALID
                     APPLY_BALANCE                  INVALID
                     AUDIT_CEO_COMP                 INVALID
                     AUTONFAIL                      INVALID
                     AUTONOMOUS_INSERT              INVALID
                     AUTO_PROC                      INVALID
                     AUTO_PROC1                     INVALID
                     AUTO_PROC2                     INVALID
                     B                              INVALID
                     BACKGROUND_ALERT               INVALID
                     BACKGROUND_SEND_MAIL           INVALID
                     BIND                           INVALID
                     BULK_EXCEPTIONS                INVALID
                     BULK_TEST                      INVALID
                     AA                             VALID
                     ADD_UP                         VALID
                     ANALYZE_MY_TABLES              VALID
                     ASSIGN                         VALID
                     ASSIGNDT                       VALID
                     BB                             VALID
                     BUILD_SCHEDULE                 VALID
                     CALCULATE_TOTALS               VALID

TRIGGER              APPLICATION_USERS_IOI          INVALID

TYPE                 ASCII_TABLE_TYPE               INVALID
                     ADDRESS2                       VALID
                     ADD_LIST                       VALID

TYPE BODY            ADDRESS2                       VALID


49 rows selected.

SQL>
SQL>

30.90.user_objects
	30.90.1.	Check user_objects for object name
	30.90.2.	Check the package status in user_objects
	30.90.3.	Check function/procedure status
	30.90.4.	Check the status for all stored procedures is by using the Oracle data dictionary view USER_OBJECTS
	30.90.5.	Get invalid table objects by joining user_segments and user_objects
	30.90.6.	Get all invalid user objects in user_objects table
	30.90.7.	Create drop command by query the user_objects table
	30.90.8.	Create grant permission command by querying user_objects table
	30.90.9.	Query user_objects for all packages, procedures and functions
	30.90.10.	Query user_objects for user-defined data type
	30.90.11.	Recompile all invalid package
	30.90.12.	Query user_objects for all java classes
	30.90.13.	Query user_objects for all PL/SQL code
	30.90.14.	Get Object id from user_objects
	30.90.15.	Join sys.col$ and user_objects
	30.90.16.	Query user_objects for object status
	30.90.17.	Query user_objects for your functions and procedures
	30.90.18.	User and its average object id
	30.90.19.	Show the procedure is marked invalid **
	30.90.20.	Behavior of dependent objects.
	30.90.21.	Finding, Validating, and Describing Packages
	30.90.22.	If procedure is valid