[Chapter 11] 11.2 ALL

11.2 ALL_OBJECTS View

The main objective of PLVobj is to provide a programmatic interface to the ALL_OBJECTS view. This data dictionary view has the following columns:

 Name             Null?    Type
 ---------------- -------- ----
 OWNER            NOT NULL VARCHAR2(30)
 OBJECT_NAME      NOT NULL VARCHAR2(30)
 OBJECT_ID        NOT NULL NUMBER
 OBJECT_TYPE               VARCHAR2(12)
 CREATED          NOT NULL DATE
 LAST_DDL_TIME    NOT NULL DATE
 TIMESTAMP                 VARCHAR2(75)
 STATUS                    VARCHAR2(7)

It contains a row for every stored code object to which you have execute access (the USER_OBJECTS view contains a row for each stored code object you created). The OWNER is the schema that owns the program unit.

object_name: The name of the object.
object_type: The type of the object, which is one of the following: package, package body, procedure, function, or synonym.
object_id: An internal pointer used to quickly obtain related information about the object in other data dictionary views.
last_ddl_time: Stores the date and timestamp when this object was last compiled into the database.
Status column: Either VALID or INVALID. This column is set by the Oracle Server as it maintains dependencies and performs compiles.

Without a package like PLVobj, every time you wanted to read from this view, you would need to write a SELECT statement in SQL*Plus or, in PL/SQL , define a cursor, and then do the "cursor thing." You would need to understand the complexities of the ALL_OBJECTS view (for example, all names are uppercased unless you created the object with double quotes around the name). You would also need to know how to parse a program name into its full set of components: owner, name, and type.

If several developers in your organization need to do the same thing, you soon have a situation where the same kind of query and similar code is "hard-coded" across your application or utilities. Lots of hours are wasted and the resources required for maintenance of the application multiply.

A better solution is to write the cursor once -- in a package, of course -- and then let all developers reference that cursor. They each do their own opens, fetches, and closes, but the SQL (just about the most volatile part of one's application) is shared. An even better solution, however, is to hide the cursor in the body of the package and build a complete programmatic interface to the cursor. With this approach, you build procedures and functions that perform the cursor operations; a user of the package never has to call native PL/SQL cursor operations. This is the approach I have taken with PLVobj and described in the following sections.

11.2.1 Cursor Into ALL_OBJECTS

At the heart of PLVobj (in the package body) is a cursor against the ALL_OBJECTS view. The cursor is defined as follows:

CURSOR obj_cur 
IS
   SELECT owner, object_name, object_type, status
     FROM ALL_OBJECTS
    WHERE object_name LIKE v_currname 
      AND object_type LIKE v_currtype
      AND owner LIKE v_currschema
    ORDER BY owner,
       DECODE (object_type, 
          'PACKAGE', 1, 
          'PACKAGE BODY', 2,
          'PROCEDURE', 3,
          'FUNCTION', 4,
          'TRIGGER', 5, 
          6),
       object_name;

Notice that the cursor contains references to three package variables: v_currschema , v_currname , and v_currtype . These three variables make up the current object of PLVobj and are discussed in more detail later in this chapter.

Notice that I use the LIKE operator so that you can retrieve multiple objects from a single schema, or even multiple schemas. Furthermore, since my PL/SQL development is PL/SQL and package-centric, I use a DECODE statement to bring those up first in the ordering system.

Again, since this cursor is defined in the package body, users of PLVobj cannot directly OPEN, fetch from, or CLOSE the cursor. All of these actions must be taken through procedures which are described in the next section.