Are your SQL Plus scripts going to ‘ell ?

Excuse the hideous pun in the title.  All will become clear shortly.

Often we use SQL Plus to run scripts on a scheduled basis in our database.  The “problem” is, they need to connect to that database.

Here’s a simple script I want to run:

and here’s my very sophisticated batch file (which could just as easily be a Unix shell script) to run it:

In my case, I’m planning on using a wallet to not reveal the password (see here for details ).

But what if my wallet is wrong or missing?  Or if I’m explicitly coding a username / password and either are incorrect ?

Then this happens when I run my batch file:

Yes my connection failed…but notice that the script did not complete.

It…..just….sat….there.

That’s no good…from an observer’s perspective, it is still “running”.

So here’s a quick tip.  Whenever you launch SQL Plus from within a shell or script, always include the -L parameter (“minus elle”)

If you can’t connect, SQL Plus will exit and at least you’ll get some feedback that all is not OK.

So use minus elle, to stop your scripts from going to hell