techblog 

In our ant build scripts we have targets to setup and teardown test databases. This is useful for our continuous integration environment. Of course we also want these same scripts to be applied to the live environment. This means that they need to be parameterized, not least so that passwords can be different between testing and production. Since we are using SQL*Plus to apply these scripts to an Oracle database, we use substitution variables. For example to connect to a schema called blah we will use the following syntax within our SQL scripts:

connect blah/&&blah_password@&&database

allowing the password and database to be parameterized. That’s fine until someone makes a typo and we get this instead:

connect blah/&&blah_apssword@&&database

If you are running interactively, the script will stop at this point and ask you for the value of blah_apssword. But when you run via ant, you find that it gives the following cryptic message and keeps on going regardless:

Enter value for blah_apssword:
User requested Interrupt or EOF detected.

We’d rather that it failed at this point. So let’s tell SQL*Plus to fail on error:

WHENEVER SQLERROR EXIT SQL.SQLCODE
connect blah/&&blah_apssword@&&database

This doesn’t quite work because the failure to connect is a SQL*Plus error, not a SQL one. The solution is to pipe something into STDIN so that when SQL*Plus hits an unknown variable it reads this in and uses it. I opted to pass in a single dot. The script is being called by ant’s exec task, so we can set the input using the inputstring attribute. This gives us what we want:

Enter value for blah_apssword: ERROR:
ORA-01017: invalid username/password; logon denied

So now our script will fail when we hit an unknown substitution variable (so long as a single dot is not a valid value for it). Any better solutions would be most welcome…