We all used to iterators, recursions and branching in programming, but sql*plus does not have such commands. Today I will show how to do iterators/recusions.

Suppose we want to call a script 3 times. So it would be convenient if we can do it like:

@iterate 3 @some_script

It is very easy: We can recursively call the script by reducing variable until it is greater than zero. and if a variable is zero, then call an empty “NULL” script.

iterate.sql:

-- initializing input parameters as i showed in previous tips: @inc/input_params_init.sql; -- for example max number of input parameters = 14: def _INIT_PARAMS = '"&3" "&4" "&5" "&6" "&7" "&8" "&9" "&10" "&11" "&12" "&13" "&14" "&15" "&16"' def _ITER = "&1" prompt ------ iteration &_ITER start: "@&2 -----------; set termout off col _SCRIPT new_val _SCRIPT noprint; col _CONTINUE new_val _CONTINUE noprint; col _PARAMS new_val _PARAMS noprint; select replace('"&3" "&4" "&5" "&6" "&7" "&8" "&9" "&10" "&11" "&12" "&13" "&14" "&15" "&16"' ,'${ITER}' ,'&_ITER' ) as "_PARAMS" ,case when &1 > 0 then 'iterate '||(&1 - 1) else 'inc/null' end as "_CONTINUE" ,case when &1 > 0 then '&2' else 'inc/null' end as "_SCRIPT" from dual; set termout on @&_script &_PARAMS @&_CONTINUE &_SCRIPT &_INIT_PARAMS

As you see i also added option to pass iteration_number as input parameter, so we can call it like: **@iterate 3 script.sql ${ITER}**

And ** null.sql** in “

*inc*” directory is just empty file.

Lets test it:

SQL> $cat test.sql select &1 a,'&2' b,'&3' c from dual; SQL> @iterate 3 test.sql 1 2 3 ------ iteration 3 start: "@test.sql ----------- A B C ---------- - - 1 2 3 ------ iteration 2 start: "@test.sql ----------- A B C ---------- - - 1 2 3 ------ iteration 1 start: "@test.sql ----------- A B C ---------- - - 1 2 3 ------ iteration 0 start: "@test.sql ----------- SQL> @iterate 2 test.sql 1 2 ${ITER} ------ iteration 2 start: "@test.sql ----------- A B C ---------- - - 1 2 2 ------ iteration 1 start: "@test.sql ----------- A B C ---------- - - 1 2 1 ------ iteration 0 start: "@test.sql -----------

Note that last string *“iteration 0 start”* just means that it is last empty call. I made this only for showing how we can do postprocessing after all iterations.

Ok, it works good, but with a little modification we can also add option to call such script with list of values too:

See how it works:

-- iterate by list of values (7,3,10): SQL> @iterate (7,3,10) test.sql 1 2 ${ITER} ****** iteration 7 start: "@test.sql *********** A B C ---------- - - 1 2 7 ****** iteration 3 start: "@test.sql *********** A B C ---------- - - 1 2 3 ****** iteration 10 start: "@test.sql *********** A B C ---------- - -- 1 2 10 -- now with simple 2 iterations: SQL> @iterate 2 test.sql 1 2 ${ITER} ****** iteration 2 start: "@test.sql *********** A B C ---------- - - 1 2 2 ****** iteration 1 start: "@test.sql *********** A B C ---------- - - 1 2 1

So if you call script with “iterate N …” it will iterate N times, and if you specify “iterate (X,Y,…,Z)” it will iterate by this list.

PS. About a branching i will wrote later…