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.