Last updated on 06/05/2017
This is about running SQL*Plus in batch (script) mode. Suppose you have a script (shell or batch file) that calls SQL*Plus to execute a script file. I am talking about running it from Command line, thus:
$ sqlplus <user_id/password>@SID @<sql_file_name)
Chances are, you will want to exit SQL*Plus as soon as the script is done (EOF file is reached in SQL file), so we can continue our processing.
Typically, we add an EXIT statement at the end of the SQL file itself and this will force SQL*Plus to quit. What if you forgot or couldn’t add EXIT (in case you use the same script in a different scenario – like this script gets called by another script, like a wrapper). If you don’t have an EXIT or QUIT statement at the end of your SQL file, you will end up seeing the SQL Prompt:
SQL>
And it waits for the user to type the next statement.
In such scenarios, you can add the EXIT statement externally, by typing:
echo EXIT | sqlplus <user_id/password>@SID @<sql_file_name)
This works in both Unix shell scripts and Windows batch files.
This essentially types EXIT into the SQL Prompt. In case you are wondering, if it will exit immediately, the EXIT statement is really queued after all that is in the SQL*Plus input, in this case what’s in the script file. Thus, it correctly executes EXIT when SQL*Plus finished executing rest of the statements – this means when it reaches the EOF in this case.
Here is another quick tip to exit SQL*Plus after it’s done with the script:
exit | sqlplus <user_id/password>@SID @<sql_file_name)
(That’s it. Essentially piping exit into sqlplus command! When the End of file is reached, SQL*Plus returns to the shell and your shell script can go on!)
This tip works on both DOS (Windows command prompt)_ and *nix systems.
Update: 06/05/2017
I wrote this in 2012. After many years of being there, I see that this post is one of the popular ones! Who could have thought?!!
I have updated the post, to include echo EXIT, based on a comment below.
EXIT and QUIT are SQL*Plus commands (case doesn’t really matter, just wanted to distinguish them from DOS commands).
My suggestion to use exit | sqlplus, uses sending a signal to the program to exit. In this case, exit is an OS level command/program.
The difference in the 2 approaches is when you use echo EXIT, you are literally “typing” into sqlplus prompt whereas in the other case, you are signaling it to end. Both will work, but using exit command may be OS dependent.
Filed under: *nix, CodeProject, Databases, DOS, Oracle, Scripting
