up vote 25 down vote favorite
3
share [g+] share [fb]

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?

link|improve this question
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
feedback

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

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

8 Answers

up vote 31 down vote accepted

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

echo exit | sqlplus user/pass@connect @scriptname
link|improve this answer
Cool! do you know how the "exit" gets appended to the sqlplus session ? – anjanb Sep 23 '08 at 20:55
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
1  
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
feedback
up vote 4 down vote

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

link|improve this answer
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
show 2 more comments
feedback
up vote 3 down vote

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.

link|improve this answer
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
feedback
up vote 2 down vote

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

link|improve this answer
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
feedback
up vote 1 down vote

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

link|improve this answer
feedback
up vote 1 down vote

exit | SQLPLUS /@ @

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

link|improve this answer
feedback
up vote 1 down vote

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.

link|improve this answer
feedback
up vote 0 down vote

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

link|improve this answer
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
feedback

Your Answer

 
or
required, but never shown
discard

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