Power to Build

Home » Operating Systems » *nix » Quick Tip: How to exit from SQL*Plus on command line

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.


3 Comments

  1. Anonymous says:

    It saved my day!!. Thank you a lot!

  2. Anonymous says:

    Rather use “echo exit | …” or “echo quit | …”

    • SamV says:

      Echo exit or Echo quit is good too, they type the word into SQLPlus.

      My solution works fine too. I’ve used it on both Unix and Dos/Windows.
      Thanks

Comments, please?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: