#

| Chapter 11 PLVobj: A Packaged Interface to ALL_OBJECTS |

## 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 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;
>     /

11.3 Setting the Current Object |  | 11.5 Binding Objects to a Dynamic Cursor

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.