11.6 Populating a PLSQL Table with Object Names

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.