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 (subsequently grown to 7) 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
11/02/2012  01:01               190 start_100000.sql
11/02/2012  01:01               202 start_1000000.sql
               7 File(s)            930 bytes
               0 Dir(s)   8,238,260,224 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.