O'Reilly logo

Advanced Oracle PL/SQL Programming with Packages by Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required