Run Oracle SQL script and exit from sqlplus.exe via command prompt

Question

I would like to run an Oracle script through SQL Plus via a Windows command prompt. The script does not contain an "exit" command, but I would still like SQL Plus to exit, returning control to the command prompt on completion of the script. My goal is to do this without modifying the script. Is this possible?

Answer 1

Another way is to use this command in the batch file:

echo exit | sqlplus user/pass@connect @scriptname

Answer 2

Realizing now that your problem may be with the sql file itself, realize that sqlplus needs to be told to exit. The way I do this is:

select * from dual;

quit;
/

(The slash is important. It tells sqlplus to execute the statemet(s) above it.)

Answer 3

Like this:

sqlplus /nolog userid/password@tnsname @filename

If you put this in a batch file, control will continue with the statement(s) following it.

EDIT: My bad, try again with /nolog flag

Answer 4

Yes, it's possible -- generate a wrapper script which sets up SQLPlus appropriately, includes your script (ie. @YourTargetScript.sql), and then does an exit.

That said, I don't recommend this approach at all -- SQLPlus has a great many gotchas for programmatic use; when writing shell scripts in the past that used Oracle, I built a Python wrapper around it (adding more reasonable error-handling behavior, sane separation of output between stdout/stderr, native CSV output support, and other such goodies), and that worked much better.

Answer 5

You can also do this in your shell script.

sqlplus /nolog <<EOF
connect user/pass
@run_some_file.sql
select * from dual;
EOF

You might need to escape the ";" with a \.

Answer 6

The best way to hide user information and exits is:

exit | sqlplus -S user/pwd@server @script.sql

exit is supplied to output of sqlplus forcing it to quit. -S suprresses all server output other then sql query in the script.

Answer 7

I found this to be the best solution and it works in a terminal or within a script:

echo @scriptname | sqlplus username/password@connect

Answer 8

exit | SQLPLUS /@ @

This is the correct solution, i have tried it's working

Answer 9

In your SQL Script add EXIT. Here is example my test.bat file has following:

sqlplus user/pwd@server @test.SQL > myLOG.LOG

my test.sql file has following: select * from dual; exit

Answer 10

For those worried about the security of including your password in the script, AskTom has an article about "identified externally" http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:142212348066