134

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,535
  • 3
  • 13
  • 11

11 Answers11

166

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

echo exit | sqlplus user/pass@connect @scriptname
Dave Costa
  • 1,796
  • 1
  • 10
  • 3
  • 1
    Cool! do you know how the "exit" gets appended to the sqlplus session ? –  Sep 23 '08 at 20:55
  • 6
    The sqlplus command runs the script then tries to read more commands from standard input. With this pipeline, the read from standard input will read the string "exit" from the echo command, causing sqlplus to exit. – Dave Costa Sep 24 '08 at 13:42
  • Excellent - this is exactly what I was looking for. Thanks! – JoshL Sep 25 '08 at 17:52
  • That...is so cool – George Mauer Jun 29 '09 at 17:49
  • 4
    Beware that you expose your username password through the proces list on unix (ps -ef) but i'm almost sure it works the same way on windows – Rob van Laarhoven Mar 09 '11 at 10:01
  • 3
    http://stackoverflow.com/questions/1639704/sqlplus-statement-from-command-line and http://dba.stackexchange.com/a/65064/16892 describe some ways to not have to use password on the command line... – rogerdpack Feb 18 '15 at 20:52
  • 1
    To avoid exposing the password, you can use echo exit |sqlplus / as sysdba @scriptname – Olivier May 15 '19 at 12:26
17

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
  • 271
  • 2
  • 4
  • 1
    While this may technically answer the Question, it's substantially a duplicate of other Answers and adds little value. Also, It would help others if you explained what these commands are doing. Thank you! – Chris S Oct 26 '12 at 13:13
  • 7
    This worked for me and I find it more elegant than the top answer because it avoids 'exit'. – Bogdan Calmac Apr 03 '14 at 01:02
16

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
  • 317
  • 1
  • 6
  • Note that Dave Costa's answer works too: piping exit (or quit) into the sqlplus command. –  Sep 23 '08 at 16:42
  • Thanks for the / trick, I was looking for this information! –  Apr 28 '09 at 09:29
  • 4
    The slash means to execute what is in the SQL buffer. It doesn't execute multiple statements, and it isn't necessary to execute SQLPlus control commands like "quit". If you type "quit" at an interactive SQLPlus prompt, it will exit immediately. – Dave Costa Aug 28 '14 at 22:56
15

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
  • 3
girish
  • 159
  • 1
  • 2
  • 2
    If using ssh is to do quit | instead of exit | so that it doesn't close your ssh session if the file is edited later to have an exit or quit in it. This works if the file isn't found too. – Karl Henselin Dec 09 '16 at 17:25
10

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
  • 200
  • 5
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
  • 946
  • 2
  • 10
  • 19
  • With any other program I would agree with you. BUT I found that SQLPlus gave me useful context on a bad query that other environments (in my case Perl/DBI/DBD::Oracle) did not. If you're doing careful error handling, that can be worth the pain. –  Sep 22 '08 at 23:55
  • Also, depending on how much control you have over the server, you may or may not be able to assume availablilty of anything more then shell and SQLPlus. A lot of stogier unix shops running Solaris, HP/UX, AIX and suchlike use very bare installs and won't let you install anything else on the box. Note that sometimes a workaround to this is just to bundle a minimal interpreter with your application. – ConcernedOfTunbridgeWells Jun 30 '10 at 09:32
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
  • 317
  • 1
  • 6
  • Unfortunately, this doesn't work. When I execute the batch file, SqlPlus waits at the SQL> prompt for user input instead of returning control to the command prompt. – JoshL Sep 23 '08 at 00:05
  • Sorry, this doesn't work either. /nolog allows sqlplus to start without logging on. It has nothing to do with exiting a script when it completes. – JoshL Sep 23 '08 at 13:44
  • Hmm, I'm starting to wonder about environment differences. I have numerous bat scripts that use sqlplus in this way. –  Sep 23 '08 at 14:35
  • Chris, could you provide a working example? Just something simple that runs a "select * from dual" or something... I've created examples to try out your suggestions, to no avail. Remember that this is on Windows. – JoshL Sep 23 '08 at 14:37
  • Josh, see new answer below. –  Sep 23 '08 at 16:04
  • The point is not to change the script - I often have a number of large scripts that I want to execute without modifying. – JoshL Sep 23 '08 at 19:43
  • In that case, Dave Costa's answer is the way to go: `echo exit | sqlplus ...`. And yes, this works fine on Windows. –  Sep 23 '08 at 22:40
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
  • 2
    Whilst this may theoretically answer the question, [it would be preferable](http://meta.stackexchange.com/q/8259) to include the essential parts of the answer here, and provide the link for reference. – Scott Pack Oct 11 '12 at 18:31
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
  • 265
  • 1
  • 7
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

  • That's fine, but doesn't meet the requirement of doing this without modifying the script to include an exit/quit statement. The intent is that these scripts can be run by a DBA from SQL Plus, but can also be run from a batch file. – JoshL Jun 19 '09 at 21:18
0

exit | SQLPLUS /@ @

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