Quick Tip: How to exit from SQL*Plus on command line

Updated, 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 sql script, like a wrapper sql). 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 (or if called from within a wrapper, it may produce unpredictable results depending on the next script file/statement wrapper sql has).

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 way to achieve the same result:

exit | sqlplus <user_id/password>@SID @<sql_file_name)

(That’s it. Essentially piping exit into sqlplus command! When the End of the current script file is reached, SQL*Plus returns to the shell and your shell script (or the wrapper sql) can go on!

This also works on both DOS (Windows command prompt)_ and *nix systems.

Update: 06/05/2017

I shared the original tip 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 OS commands). By echoing them into SQL Prompt, you force SQL*Plus to exit.

My suggestion to use exit | sqlplus, uses sending a signal to the program to exit (this is like executing Ctrl-D on Unix). 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 where as in the other case, you are signaling it to end. Both will work, but using exit OS command may be OS dependent. Test on command line, before use.