#

| Chapter 11 PLVobj: A Packaged Interface to ALL_OBJECTS |

## 11.7 A Programmatic Cursor FOR Loop

The `loopexec` procedure provides the procedural equivalent of a cursor FOR loop. Its header is shown below:

>     PROCEDURE loopexec
>        (module_in IN VARCHAR2,
>         exec_in IN VARCHAR2 := c_show_object,
>         placeholder_in IN VARCHAR2 := c_leph,
>         name_format_in IN VARCHAR2 := c_modspec);

The `loopexec` procedure executes the line of code found in the `exec_in` argument for all of the modules specified by the `module_in` argument. If the `module_in` string does not have any wildcard characters, then it will apply the `exec_in` command to the single program only.

The default value for the executable statement is `c_show_object`, a constant defined in the package specification as follows:

>     c_show_object CONSTANT VARCHAR2(100) := 'p.l (:rowobj)';

where `rowobj` is the placeholder for the object identified by the current row fetched from the PLVobj cursor. The default action is, therefore, to display the name of the current object.

The `placeholder_in` argument tells `loopexec` which string will serve as a placeholder in the execution string (this placeholder is similar to the dynamic SQL bind variable placeholder). The default is defined in the PLVobj constant, `c_leph`, as follows:

>     c_leph CONSTANT VARCHAR2(10) := 'rowobj';

You can, however, override this value with your own string (an example of this process is shown in the next section).[1]

> [1] For curious readers, the `leph` stands for "LoopExec PlaceHolder."

The `name_format_in` argument specifies the form that the current object string should take when constructed by PLVobj. The options for the format are:

Format Constant | Format of Object String
--- | ---
`c_modspec` | TYPE:SCHEMA.NAME
`c_modname` | SCHEMA.NAME

The `c_modspec` format is useful when the current object is to be passed to another program that supports the PLVobj format for specifying objects (particularly in PL/Vision utilities). The `c_modname` option structures the name so that it is a valid program unit name in PL/SQL.

### 11.7.1 Some Simple Applications of loopexec

The default statement executed by `PLVobj.loopexec` requests that `loopexec` display the name of the current object. So if I want to display all of the objects located with the string `s:PLVc%`, I would simply enter:

>     SQL> exec PLVobj.loopexec ('s:PLVc%');
>     PACKAGE:PLV.PLVCASE
>     PACKAGE:PLV.PLVCAT
>     PACKAGE:PLV.PLVCHR
>     PACKAGE:PLV.PLVCMT

and would discover that I have four package specifications whose names start with PLVC. To obtain this information, I did not have to write a loop against the ALL_OBJECTS cursor. Neither did I have to call the various `PLVobj` cursor management programs like `fetch_object`. Instead, I simply told `PLVobj`: For every object identified, execute this code. It is, in other words, a programmatically defined cursor FOR loop! See what I mean about PL/SQL being fun?

Now suppose that I want to do something besides display the objects. Let's see how to use `loopexec` to generate a set of DESCRIBE commands for use in SQL*Plus to show the call interface to stored code. The format for this command in SQL*Plus is:

>     SQL> desc prog

where `prog` is the name of a PL/SQL program, either a function or a procedure (standalone or packaged). So if my PL/SQL program is going to generate these commands, I would have to execute something like this:

>     p.l ('DESC ' || current_object);

where `current_object` is a variable containing the current object string. In this scenario, however, I am working with dynamic PL/SQL; `loopexec` does not know in advance which statement I want to execute. So I need to convert this command into a string that will be evaluated into the proper PL/SQL command. In particular, I must double all single quotes and give `loopexec` a way to find my reference to the current object. I do this through the use of a placeholder string.

This approach is shown in the following script (stored in file `gendesc.sql`):

>     BEGIN
>        PLVobj.loopexec
>           ('&1', 'p.l (''DESC '' || :XX)', 'XX', PLVobj.c_modname);
>     END;
>     /

In this call to `loopexec`, I provide a value for every argument! The first value is actually a SQL*Plus substitution parameter containing the specification of the program unit(s) for which I want to generate a DESC command. The second argument is the dynamic PL/SQL version of the call to `p.l`, which outputs a DESC command. Notice the double quotes around DESC and the hard-coding of a concatenation of the `XX` placeholder. The third argument (`XX`) tells `loopexec` to replace any occurrence of `:XX` in the command with the current object. Finally, the fourth argument requests that the current object string be returned as a valid PL/SQL object name (the DESC command doesn't know about the type:schema.name syntax of PL/Vision).

I execute `gendesc` below to create DESCRIBE commands for all procedures in the PLV schema.

>     SQL>  @gendesc p:%
>     DESC PLV.CREATE_INDEX
>     DESC PLV.MODVALS
>     DESC PLV.MORE
>     DESC PLV.PLVHELP
>     DESC PLV.PLVSTOP
>     DESC PLV.SHOWEMPS
>     DESC PLV.SHOWERR
>     DESC PLV.SHOWUSER

I can then cut and paste these commands into a file (or use the SPOOL command) and execute them.

You might still be looking at the arguments I passed to `PLVobj.loopexec` and wondering what the heck that all means and, more importantly, how you could ever figure out how to use `loopexec` properly. So let's now turn our attention to the task of constructing an execution string for the `loopexec` procedure.

### 11.7.2 Constructing the Execution String

The `loopexec` procedure uses dynamic PL/SQL (via the PL/Vision `PLVdyn` package and, as a result, the builtin DBMS_SQL package) to execute the string you pass to it. For this to work properly, you must build a string which evaluates to a valid PL/SQL command. This task can become very complicated when you need to include single quote marks and especially when you want your executed code to operate on the current object (which is, after all, the main reason you would use `loopexec`). To work with the current object fetched from the PLVobj cursor, `loopexec` needs to bind the current object into the dynamic PL/SQL string.

To understand how to deal with these issues, let's start by looking more closely at the default action. This code string is contained in the packaged constant, `c_show_object`, which is the following string:

>     c_show_object CONSTANT VARCHAR2(100) := 'p.l (:rowobj)';

In PLVobj terminology, the string `:rowobj` is the placeholder for the current object. This is the default placeholder string and is defined in a package-level constant shown below:

>     c_leph CONSTANT VARCHAR2(10) := 'rowobj';

The `p.l` procedure, as you should be well aware by now, displays output to the screen. So this command says: "Display the current object." When `loopexec` prepares to execute this simple command, it replaces all occurrences of the string `:rowobj` with the variable containing the current object string (in the form type:schema.name). It then passes this string to the `open_and_parse` function of PLVdyn and immediately executes the PL/SQL program contained in the string.

When you construct your own strings to pass to `loopexec`, you can use the default placeholder string or you can specify your own string. You saw in the last section how I used my own placeholder, `XX`, to direct `loopexec` to perform the right substitution. Now let's look at how `PLVcase` uses `loopexec` to convert the case of multiple programs to demonstrate use of the default placeholder. The full body of the `PLVcase.modules` procedure is shown below:

>     PLVobj.loopexec
>        (module_spec_in,
>         'PLVcase.module(' || PLVobj.c_leph || ', PLVcase.c_usecor,
>            FALSE)');

As you can see, it consists of a single line: a call to the `loopexec` program. This call contains only two arguments, so the default values will be used for the last two arguments (the placeholder string and the string format). The line of code executed by `loopexec` is a call to `PLVcase.module` program, which converts the case of a single PL/SQL program. Suppose that I am converting the `employee_maint` package. I would then want this string executed by `loopexec`:

>     PLVcase.module ('employee_maint', PLVcase.c_usecor, FALSE);

Since I am passing in a variable containing the package name, however, my call to `PLVcase.module` would look more like this:

>     PLVcase.module (v_currobj, PLVcase.c_usecor, FALSE);

Now, it is theoretically possible for me to find out the specific string used by PLVobj for its placeholder replacement (you have already seen it: `:rowobj`). This is an example, however, of dangerous knowledge. In this situation, what I know could hurt me. What if I hard-code the `rowobj` string into my calls to `loopexec` and then somewhere down the line, PLVobj is changed and a new string is used? Yikes! Lots of broken code.

### 11.7.3 Using the Predefined Placeholder

A better approach is to reference the placeholder string by a named constant, rather than a literal value. This constant is provided by the `PLVobj.c_leph` constant. In this approach, when I call `PLVcase.module`, I would concatenate this constant into my command string wherever the current object variable appeared in the last example:

>     'PLVcase.module(' || PLVobj.c_leph || ', PLVcase.c_usecor, FALSE)'

When passed to `loopexec`, this string will be executed for every object retrieved from the cursor. And for each of those objects, the placeholder string will be replaced by the object name and the dynamic PL/SQL code then executed for that object.

The PLVcat package also calls the `loopexec` procedure in its `modules` program to catalogue multiple programs. In this case, when I pass the current object to the `PLVcat.module` I only want to pass the SCHEMA.NAME portion of the current object. Consequently, I request the alternative name format:

>     PLVobj.loopexec
>        ('s:' || module_in,
>         'PLVcat.module(' || PLVobj.c_leph || ')',
>         name_format_in => PLVobj.c_modname);

If you do not want to bother with making reference to the PLVobj constant for the placeholder value, you can specify another of your own design. For example, I could recode my call to `loopexec` in PLVcat to this:

>     PLVobj.loopexec
>        ('s:' || module_in,
>         'PLVcat.module(:XX)',
>         'XX',
>         PLVobj.c_modname);

> NOTE: When you execute dynamic PL/SQL as practiced by PLVobj, you can only reference global data structures and programs. You can, in other words, only reference elements defined in a package specification to which you have access. A PL/SQL block that is executed dynamically is not a nested block in the current program; it is treated as a standalone block. That is why when `PLVcase.modules` calls `loopexec` in this section's example, it includes the package name PLVcase in its reference to module and the constant, `c_usecor`.

Now I have hard-coded the `XX` placeholder string into my execution string, but I also inform `loopexec` that `XX` is the new placeholder string. I have, therefore, established internal consistency. Even if the value of the `c_leph` constant changes, my code will not be affected.

As you can see, `PLVobj.loopexec` offers a tremendous amount of flexibility and potential. That's what happens when you leverage dynamic PL/SQL code execution and you take the time to build an interface through which users can tweak a utility's behavior. Take some time to play around with `loopexec` and all its parameter variations. You will benefit not only in your ability to take advantage of PLVobj, but also in your efforts with DBMS_SQL. It will be an investment of time richly rewarded.

### 11.7.4 Applying loopexec in PL/Vision

I found that in a number of PL/Vision packages I wanted to execute a certain piece of functionality (convert the case, build a catalogue, etc.) for more than one object at a time. For example, I might want to catalogue all the external references in all packages with names like `PLV%`. The first time I did this, for PLVcase, I made use of my carefully constructed cursor-related programs to come up with a loop like this:

>     PROCEDURE modules
>       (module_spec_in IN VARCHAR2 := NULL)
>     IS
>        objects PLVtab.vc2000_table;
>        numobj INTEGER := 0;
>     BEGIN
>        PLVobj.setcurr (module_spec_in);

>        PLVobj.open_objects;

>        LOOP
>           PLVobj.fetch_object;
>           EXIT WHEN NOT PLVobj.more_objects;
>           numobj := numobj + 1;
>           objects (numobj) :=
>              PLVobj.currtype || ':' ||
>              PLVobj.currschema || '.' ||
>              PLVobj.currname;
>        END LOOP;

>        PLVobj.close_objects;

>        FOR objind IN 1 .. numobj
>        LOOP
>           module (objects (objind), c_usecor, FALSE);
>        END LOOP;

>        save_program;
>     END;

In this procedure, I loop through the cursor, loading up a PL/SQL table with the selected objects. Then I use a cursor FOR loop to convert the case of each program found in that table. I was proud of the way I was able to quickly apply my different, high-level elements of PL/Vision to come up with rich functionality. But then I got to the PLVcat package and I wanted to do the same thing there as well. Suddenly my elegant set of loops seemed like an awful lot of code to repeat. So what did I do? I built the `vu2pstab` procedure of PLVobj and was able to shrink down the `PLVcase.modules` program to nothing more than:

>     PROCEDURE modules
>       (module_spec_in IN VARCHAR2 := NULL)
>     IS
>        objects PLVtab.vc2000_table;
>        numobj INTEGER := 0;
>     BEGIN
>        PLVobj.vu2pstab (module_spec_in, objects, numobj);

>        FOR objind IN 1 .. numobj
>        LOOP
>           module (objects (objind), c_usecor, FALSE);
>        END LOOP;
>        save_program;
>     END;

And that was a very satisfying reduction of code and simultaneous abstraction of the process. Yet I still found myself repeating that FOR loop again and again, the only difference being the line of code that was executed inside the loop. This repetition brought me to another realization: Maybe I could use the PLVdyn package to dynamically construct and execute the body of the FOR loop. If that were true, then I could hide all of these details behind a single procedure call.

With the `PLVobj.loopexec` procedure, such a consolidation is possible. The final implementation of `PLVcat.modules` does in fact consist of a single line of code as shown below:

>     PROCEDURE modules (module_in IN VARCHAR2) IS
>     BEGIN
>        PLVobj.loopexec
>           ('s:' || notype (module_in),
>            'PLVcat.module(' || PLVobj.c_leph || ')',
>            name_format_in => PLVobj.c_modname);
>     END;

It uses the default placeholder string in the call to `PLVcat.module`. Since that placeholder argument does not need to be specified, it uses named notation to make sure that the module name format is used.

It was possible in this way to shrink a 15-line program body down to just one line. In the process, I switched from a procedural mode of coding to a declarative style. By calling `loopexec`, I simply describe the action I want and let the underlying engine work out the details.

11.6 Populating a PL/SQL Table with Object Names |  | 11.8 Tracing PLVobj Activity

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