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?
Another way is to use this command in the batch file:
echo exit | sqlplus user/pass@connect @scriptname
I found this to be the best solution and it works in a terminal or within a script:
echo @scriptname | sqlplus username/password@connect
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.)
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.
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 \.
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.
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
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
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
exit | SQLPLUS /@ @
This is the correct solution, i have tried it's working