144

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?

JoshL
  • 1,635

11 Answers11

175

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

echo exit | sqlplus user/pass@connect @scriptname
Dave Costa
  • 1,886
22

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

echo @scriptname | sqlplus username/password@connect
user142847
  • 321
17

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.

misguided
  • 103
girish
  • 179
17

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.)

Chris Noe
  • 327
11

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 \.

Ethan Post
  • 210
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.

Charles Duffy
  • 1,081
  • 2
  • 11
  • 21
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

Chris Noe
  • 327
2

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

user128494
  • 21
1

Another one for Linux without pipes or forking another sub-shell/-process

sqlplus -S dbuser/dbpasswd@dbsid @scriptname </dev/null

After @scriptname is complete, sqlplus waits for user input. In this case, sqlplus reads an end of file from /dev/null and exits, because of this.

Olaf Dietsche
  • 295
1

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

0

exit | SQLPLUS /@ @

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