114

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?

migrated from stackoverflow.com Nov 22 '09 at 3:34

This question came from our site for professional and enthusiast programmers.

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

10 Answers 10

140

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

echo exit | sqlplus user/pass@connect @scriptname
  • 1
    Cool! do you know how the "exit" gets appended to the sqlplus session ? – anjanb 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
  • 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 – Robert Merkwürdigeliebe Mar 9 '11 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 '15 at 20:52
13

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

echo @scriptname | sqlplus username/password@connect
  • 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
  • 5
    This worked for me and I find it more elegant than the top answer because it avoids 'exit'. – Bogdan Calmac Apr 3 '14 at 1:02
12

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

  • Note that Dave Costa's answer works too: piping exit (or quit) into the sqlplus command. – Chris Noe Sep 23 '08 at 16:42
  • Thanks for the / trick, I was looking for this information! – PhiLho Apr 28 '09 at 9:29
  • 2
    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 '14 at 22:56
12

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.

  • 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 9 '16 at 17:25
6

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

3

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.

  • 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 '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 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

  • 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 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 '08 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 '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. – Chris Noe Sep 23 '08 at 16:04
1

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

  • 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 '12 at 18:31
0

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

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