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.