Repetition

One of the problems of building models of Oracle activity is that it’s easy to build the wrong model. One of the commonest issues appears with repetitive actions – how do you write code that repeats a simple action many times in a row. Writing a simple PL/SQL loop is often an adequate approach, but there are cases where a PL/SQL  loop behaves very differently from a long list of individual SQL statements – which is why I’ve occasionally used a very simple-minded approach to avoid that particular trap.

If you checked the directory of my database work area, you’d find an interesting pattern of names made by five very short scripts:

q:> dir start*.sql
 Volume in drive Q has no label.
 Volume Serial Number is A8BA-79AA

 Directory of q:\

28/03/2011  08:56               350 start_1.sql
13/01/2005  13:38               110 start_10.sql
13/01/2005  13:39               120 start_100.sql
13/01/2005  13:39               130 start_1000.sql
13/01/2005  13:40               140 start_10000.sql
               5 File(s)            850 bytes
               0 Dir(s)  82,803,621,888 bytes free

The script start_10000.sql contains just 10 lines:

@ start_1000
@ start_1000
@ start_1000
@ start_1000
@ start_1000
@ start_1000
@ start_1000
@ start_1000
@ start_1000
@ start_1000

and you won’t be surprised to find that the script start_1000.sql also contains just 10 lines:

@ start_100
@ start_100
@ start_100
@ start_100
@ start_100
@ start_100
@ start_100
@ start_100
@ start_100
@ start_100

and so on down the chain, until the script start_1.sql contains the piece of code that I actually want to run many times.

Obviously there are cases where you need a more subtle framework – but it’s very convenient to be able to write a small script holding the piece of code that you want to keep repeating and then run it 10,000 times with just a single call to: @start_10000.sql.