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.