Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

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

up vote 58 down vote accepted

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

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

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.

share|improve this answer

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

echo @scriptname | sqlplus username/password@connect
share|improve this answer
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
    
Hi Chris, I didn't see any similar answer to mine. Why did you consider it a duplicate? Also, the command is self explanatory. It's very obvious where to use the username, passord, etc.. Thanks for your comment. –  user142847 Oct 26 '12 at 13:55
    
This worked for me and I find it more elegant than the top answer because it avoids 'exit'. –  Bogdan Calmac Apr 3 at 1:02

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

share|improve this answer
    
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

exit | SQLPLUS /@ @

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

share|improve this answer

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.

share|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

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

share|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

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

share|improve this answer

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

share|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
    
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 at 22:56

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

share|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

protected by Chris S Oct 26 '12 at 13:13

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

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