The caveats of running .sql scripts with GUI tools

One of my pet peeves is people using GUI tools like Toad or SQL Developer while running release scripts on test, acceptation or production systems. Actually, pet peeves is putting it too mildly. Ive had to troubleshoot enough incidents because of this to hold a serious grudge against that careless practice. Especially when running a script that manipulates data, you are much better of using SQL*Plus. Most hardboiled DBAs probably know what Im talking about, but if you don’t, have a look at the two examples below.

Example 1: Do I need to end the line with semicolon or use a slash? Lets just use both!
The differences between the use of semicolons at the end a SQL statement and the use of a slash to run whatever is in the buffer can be confusing at first, especially in those cases where they both seem to be doing the same thing. Ive seen a surprising amount of scripts that use both, ‘just to be sure’. Lets have a look at a script that does just that:

select sum(SAL) as total_salary from EMP;
/
update EMP set SAL = SAL * 1.1 where EMPNO =1;
/
update EMP set SAL = SAL * 1.2 where EMPNO =2;
/
update EMP set SAL = SAL * 1.3 where EMPNO =3;
/
update EMP set SAL = SAL * 1.4 where EMPNO =4;
/
update EMP set SAL = SAL * 1.5 where EMPNO =5;
/
select sum(SAL) as total_salary from EMP;
/

In my experience, scripts like these are made by people who only work with Oracle sporadically. (Unfortunatly this also seems to include some application vendors that sell software that runs on “any database”, be it Oracle, SQL Server or some other flavour.)

If you run the script in Toad, you will get the result that was probably intended by the person who wrote it; five records will each be updated once. However, if you run the script from SQL*Plus, you’ll get a very different result; the five records will each be updated twice! Good news for the employees who just got their intended salary raise twice, bad news for everyone else, especially the person who ran the script.

You might object at this point: “If the use of Toad leads to the intended result and the use of SQL*Plus doesn’t, why are you trying to convince me to use the latter?” In that case you should realise updating the records twice is exactly what the script specifies. The fact that Toad handles this differently is because its a developerstool; it recognizes common errors that are frequently made during development and automaticly corrects them. (like the one in the example above.) This can be perfectly acceptable on development systems where you can code quick and dirty at first to check if things work generally, only to focus on the nitty gritty details once the big picture is complete. But as soon as your code moves to test, acceptance or production, you should no longer rely on tools that guess the intention and change the code accordingly. The code should be correct in the first place! If its not, you’ll want to see an error raised (or get a wrong result if the code is correct but written with different intentions) as soon as it gets to the test phase, so you can go back and fix it. You might think; “but what if I use Toad on development, test, acceptance AND production? Then every run should be identical?” Yes, you might be right as long as you are the only person running the scripts and you never have to take a day off from work because you are never ill and never need a vacation, because as soon as someone else has to take over from you they’ll have to use Toad (configured with exactly the same preferences!) as well or risk running into unexpected problems because there might still be errors in the script. Do you have your Toad prefences documented for them? Furthermore, do you have Toad installed on all servers (with exactly the same preferences) or do you work from a client? My guess is you dont want to install it on all database servers (because you want those servers as clean and simple as possible and dont want to use up a Toad licence per server) but running it from a client brings new risks. I will illustrate this with another example:

Example 2: network hickups
A while back I ran into the stangest issue. Someone had run a very basic sql-script with about 4000 insertstatements on acceptance and, after verification by someone else, ran the same script on production. Unfortunatly during verification on production only a quarter of the records turned out to be inserted in the database. Because the whole run of the script was spooled to a logfile i checked the logfile for ORA errors and counted the amount of succesful inserts. Everything seemed the same as on acceptance; all 4000 inserts were in the spoolfile, no ORA errors were logged. I thought I was losing my mind! Then I checked the alertlog of the database and noticed a TNS error logged during the run of the insert script. Maybe the script wasnt executed on the server itself but on a client and had been interrupted by the network hickup? Even if that was the case, I still couldnt believe that single transaction had apparently partly failed without any warnings. This seemed to undermine the very basic ACID properties of a relational database.
While discussing this with my collegues the person who had run the script confirmed he had run in from a client, but to my surprise he also mentioned he had run it in Toad instead of SQL*Plus. Within a second after saying this he figured out what had happened:

* After the first 75% of the insertstatements had been processed, the connection to the database was terminated, and the transaction was rolled back by the database. (yay ACID test)
* Upon noticing the connection error, Toad had automatically reconnected to the database, and continued with the final 25% of the script in this new session as if nothing happened.

I have never seen issues like these while using SQL*Plus. Sure, you can loose your connection to the database while running a script in SQL*Plus on a client as well, but at least it wont automatically reconnect and continue as if nothing happened. Even better; SQL*Plus will be available on the database server with no extra licence costs, so you can run your scripts on the server itself and not worry about potential network problems.

For convenience sake it might be tempting to use Toad on a client instead of SQL*Plus on a server, but after reading these examples I hope you are aware of the extra risks that this convenience might bring.