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 able to do what you want with the fetched values. The call to fetch_object without arguments is, therefore, equivalent to:

PLVobj.fetch_object (v_name, v_type);
PLVobj.setcurr (v_name, v_type);

11.4.3 Checking for Last Record

To determine when you have fetched all of the records from the cursor, use the more_objects function, whose header is:

   FUNCTION more_objects RETURN BOOLEAN;

This function returns TRUE when the obj_cur is open and when obj_cur%FOUND returns TRUE. In all other cases, the function returns FALSE (including when the PLVobj cursor is not even open).

11.4.4 Showing Objects with PLVobj

To see how all of these different cursor-oriented programs can be utilized, consider the following script (stored in showobj1.sql ).

DECLARE
   first_one BOOLEAN := TRUE;
BEGIN
   PLVobj.setcurr ('&1');
   PLVobj.open_objects;
   LOOP
      PLVobj.fetch_object;
      EXIT WHEN NOT PLVobj.more_objects;
      PLVobj.showcurr (first_one);
      first_one := FALSE;
   END LOOP;
   PLVobj.close_objects;
END;
/

It sets the current object to the value passed in at the SQL*Plus command line. It then opens and fetches from the PLVobj cursor, exiting when more_objects returns FALSE. Finally, it closes the PLVobj cursor. This cursor close action is truly required. The PLVobj cursor is not declared in the scope of the anonymous block; instead, it is defined in the package body. After you open it, it will remain open for the duration of your session, unless you close it explicitly.

In the following example of a call to showobj1.sql , I ask to see all the package specifications in my account whose names start with "PLVC". I see that I have four packages.

SQL> start showobj1 s:PLVc%
Schema.Name.Type
PLV.PLVCASE.PACKAGE
PLV.PLVCAT.PACKAGE
PLV.PLVCHR.PACKAGE
PLV.PLVCMT.PACKAGE

If you are not working in SQL*Plus, you can easily convert the showobj1.sql script into a procedure as follows:

CREATE OR REPLACE PROCEDURE showobj (obj_in IN VARCHAR2)
IS
   first_one BOOLEAN := TRUE;
BEGIN
   PLVobj.setcurr (obj_in);
   PLVobj.open_objects;
   LOOP
      PLVobj.fetch_object;
      EXIT WHEN NOT PLVobj.more_objects;
      PLVobj.showcurr (first_one);
      first_one := FALSE;
   END LOOP;
   PLVobj.close_objects;
END;
/