Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / System Tables Data Dictionary

 

Query user_objects for all PLSQL code

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>