Pseudo-FOR Loops In SQL*Plus Scripts

One way to do a FOR Loop is by using a separate recursive script (a script that calls itself), in which the recursive call at the end of the script is bypassed at the end of the loop using a generated begin-comment-block marker as seen in the Pseudo-IF description.  In our payroll voucher script, we wanted to be able to enter a variable number of adjustment periods, prompting for year, payroll ID, payroll number, sequence number, and optional SSN's for each adjustment period entered, and build a character string list of all of those periods entered to match against.  The resulting recursive script, payroll_voucher_adjs.sql, prompts for the year first, and, if no year is entered, skips the rest of the script.  If a year is entered, the remaining prompts are processed, the entered values are concatenated to the list, and the script is called again.  The relevant code snippets in the script to do this recursion are shown below.  Be aware that there is a limit to the number of recursions that can be done, depending on the size of the internal call stack maintained by Oracle, so, you probably couldn't use this technique in an application that required looping thousands of times (you would probably use an unnamed PL/SQL block for that, but those can't contain "accept" statements - see Sequential Numbering of Records).

-- Script: payroll_voucher_adjs.sql
...
--
-- Get the year for the next set of adjustments, or blank if no more to enter.
--
accept adjyear char prompt 'Enter adjustment year for &adjnext set of adjustments, if any: '
--
-- If there are no more adjustments (year is blank), execute a begin-comment-
-- block marker to bypass the rest of this script.
--
spool payvoudo.sql
select decode('&adjyear','','/*','') from dual;
spool off
@payvoudo.sql
... Other prompts and processing go here ...
--
-- Rerun this script to get the next adjustment set. Note: This rerun and
-- the commands above will be bypassed if no adjustment year was entered.
--
@payroll_voucher_adjs.sql
--
-- Define the end-comment-block marker. It is in a commented line to prevent
-- error messages about unmatched comment block markers, but it will be seen
-- and matched against if Oracle is looking for the end of the comment block.
--
-- */

Then, in the main payroll voucher script, we just initialize the variable containing the list of adjustment periods and call the recursive script:

define adjs=';'
@payroll_voucher_adjs.sql

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.