132

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?

1
  • Are you executing this with "sqlplus .... <scriptname" or "sqlplus ... @scriptname" ?? I find different behaviours depending which one you do on Unix.
    – runrig
    Jun 1, 2011 at 17:44

11 Answers 11

163

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

echo exit | sqlplus user/pass@connect @scriptname
7
  • 1
    Cool! do you know how the "exit" gets appended to the sqlplus session ?
    – anjanb
    Sep 23, 2008 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, 2008 at 13:42
  • 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 Mar 9, 2011 at 10:01
  • 3
    stackoverflow.com/questions/1639704/… and dba.stackexchange.com/a/65064/16892 describe some ways to not have to use password on the command line...
    – rogerdpack
    Feb 18, 2015 at 20:52
  • 1
    To avoid exposing the password, you can use echo exit |sqlplus / as sysdba @scriptname
    – Olivier
    May 15, 2019 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
2
  • 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, 2012 at 13:13
  • 7
    This worked for me and I find it more elegant than the top answer because it avoids 'exit'. Apr 3, 2014 at 1:02
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.

1
  • 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. Dec 9, 2016 at 17:25
14

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

3
  • Note that Dave Costa's answer works too: piping exit (or quit) into the sqlplus command.
    – Chris Noe
    Sep 23, 2008 at 16:42
  • Thanks for the / trick, I was looking for this information!
    – PhiLho
    Apr 28, 2009 at 9:29
  • 3
    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"<Enter> at an interactive SQLPlus prompt, it will exit immediately.
    – Dave Costa
    Aug 28, 2014 at 22:56
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 \.

1
  • It works, Life Saver! Aug 1, 2020 at 5:49
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.

2
  • 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.
    – Anonymous
    Sep 22, 2008 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. Jun 30, 2010 at 9: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

7
  • 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, 2008 at 0: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, 2008 at 13:44
  • Hmm, I'm starting to wonder about environment differences. I have numerous bat scripts that use sqlplus in this way.
    – Chris Noe
    Sep 23, 2008 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, 2008 at 14:37
  • Josh, see new answer below.
    – Chris Noe
    Sep 23, 2008 at 16:04
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

1
  • 2
    Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference.
    – Scott Pack
    Oct 11, 2012 at 18:31
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

1
  • 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, 2009 at 21:18
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.

1
  • The cleanest answer to this question.
    – Cristik
    Aug 20, 2020 at 16:22
0

exit | SQLPLUS /@ @

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

0

Not the answer you're looking for? Browse other questions tagged or ask your own question.