#

| Chapter 11 PLVobj: A Packaged Interface to ALL_OBJECTS |

## 11.6 Populating a PL/SQL Table with Object Names

PLVobj provides a procedure to transfer the names of all objects identified by user input from the view into a PL/SQL table. This `vu2pstab` procedure's header is as follows:

>     PROCEDURE vu2pstab
>        (module_in IN VARCHAR2,
>         table_out OUT PLVtab.vc2000_table,
>         num_objects_inout IN OUT INTEGER);

The first argument, `module_in`, is the module specification. This can be a single module or, with wildcarding characters, a set of objects. The second argument, `table_out`, is the PL/SQL table that will hold the names of all identified objects. The final argument, `num_objects_inout`, contains the number of rows populated in the PL/SQL table (starting from row 1).

Use the `vu2pstab` procedure when you want to create a list of the objects which you can then use as the basis for one or more passes through the list to perform actions against the objects. This can be particularly important when you want to make use of different elements of PL/Vision which rely on PLVobj and a current object for processing. If these packages are nested, the outer loop that uses PLVobj can be affected or overridden by the inner usage.

The script `showobj1.sql` shown in a previous section used a simple loop to retrieve and display each of the objects specified by the SQL*Plus argument. That loop can be replaced by a call to `vu2pstab` and a call to `PLVtab.display` to show the contents of the table. This version of "show objects" (stored in the file `showobj2.sql`) is shown below:

>     DECLARE
>        objects PLVtab.vc2000_table;
>        numobjs INTEGER;
>     BEGIN
>        PLVobj.vu2pstab ('&1', objects, numobjs);
>        PLVtab.display (objects, numobjs);
>     END;
>     /

This is far less code than was required by the first version; the open, fetch, and close steps of the cursor manipulation are hidden behind the `vu2pstab` program. In this way, `PLVobj.vu2pstab` offers some of the flavor and code savings of a cursor FOR loop. The `loopexec` procedure covered in the next section, on the other hand, offers an even closer resemblance to the cursor FOR loop and is a very entertaining application of dynamic PL/SQL code execution.

11.5 Binding Objects to a Dynamic Cursor |  | 11.7 A Programmatic Cursor FOR Loop

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