Advanced Oracle PL/SQL Programming with Packages

11.4. Accessing ALL_OBJECTS

Once you have set the current object in PLVobj (with either a call to setcurr or calls to the individual set programs), you can open, fetch from, and close the PLVobj cursor.

11.4.1. Opening and Closing the PLVobj Cursor

To open the cursor, you call the open_objects procedure, defined as follows:

PROCEDURE open_objects;

This procedure first checks to see if the cursor is already open and, if not, takes that action. The implementation of open_objects is shown below:

PROCEDURE open_objects IS
   BEGIN
      IF obj_cur%ISOPEN
      THEN
         NULL;
      ELSE
         OPEN obj_cur;
      END IF;
   END;

When you are done fetching from the cursor, you may close it with the following procedure:

PROCEDURE close_objects;

whose implementation makes sure that the cursor is actually open before attempting to close the cursor:

PROCEDURE close_objects IS
   BEGIN
      IF obj_cur%ISOPEN
      THEN
         CLOSE obj_cur;
      END IF;
   END;

11.4.2. Fetching from the PLVobj Cursor

Once the cursor is open, you will usually want to fetch rows from the result set. You do this with the fetch_object procedure, which is overloaded as follows:

PROCEDURE fetch_object;
PROCEDURE fetch_object (name_out OUT VARCHAR2, type_out OUT VARCHAR2);

If you call fetch_objects without providing any OUT arguments, the name and type will be passed directly into the current object variables, v_currname and v_currtype.

If, on the other hand, you provide two return values in the call to fetch_object, the current object will remain unchanged and you will be ...