Managing PL/SQL Code - Display information about stored objects

(Page 3 of 4 )

The USER_OBJECTS view contains the following key information about an object:

OBJECT_NAME
   Name of the object

OBJECT_TYPE
   Type of the object (e.g., 'PACKAGE', 'FUNCTION',
   'TRIGGER')

STATUS
   Status of the object: VALID or INVALID

LAST_DDL_TIME
   Timestamp indicating the last time that this object
   was changed.

The following SQL*Plus script displays the status of PL/SQL code objects:

  /* File on web: psobj.sql */
  SET PAGESIZE 66
  COLUMN object_type FORMAT A20
  COLUMN object_name FORMAT A30
  COLUMN status FORMAT A10
  BREAK ON object_type SKIP 1
  SPOOL psobj.lis
  SELECT object_type, object_name, status
    FROM user_objects
   WHERE object_type IN (
      'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE',
      'TYPE', 'TYPE BODY', 'TRIGGER')
   ORDER BY object_type, status, object_name
  /
  SPOOL OFF

The output from this script file contains the following list:

  OBJECT_TYPE   OBJECT_NAME       STATUS
  ------------  --------------    ---------
  FUNCTION      DEVELOP_ANALYSIS  INVALID 
                NUMBER_OF_ATOMICS INVALID

  PACKAGE       CONFIG_PKG        VALID
                EXCHDLR_PKG       VALID

Notice that a two of my modules are marked as INVALID. See the section “Recompiling Invalid Code” for more details on the significance of this setting and how you can change it to VALID.

Next: Display and search source code >>
 

More Oracle Articles
More By O'Reilly Media

Comments On: Managing PL/SQL Code

· This article is an excerpt from the book "Oracle PL/SQL Programming, Fourth...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O'Reilly; ISBN: 0596009771). Check it out today at your favorite bookstore. Buy this book now.