Home / server / Questions / 87035
Accepted
JoshL
Asked: 2008-09-23 15:33:09 +0800 CST

Run Oracle SQL script and exit from sqlplus.exe via command prompt

  • 772

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?

command-line-interface oracle sqlplus

11 Answers

  1. Best Answer

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

    echo exit | sqlplus user/pass@connect @scriptname
    • 166

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

    echo @scriptname | sqlplus username/password@connect
    • 17

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

    • 16

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

    • 15

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

    • 10

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

    • 4

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

    • 3

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

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

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