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

This is about running SQL*Plus in a batch mode. Suppose you have a script (shell or batch file) that calls SQL*Plus to run a SQL in a batch mode. I am talking about running it from Command line, thus:

$ sqlplus <user_id/password>@SID @<sql_file_name)

Chances, 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 (you use the same script in different scenarios). 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>


Here is a 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.