SQLPLUS batch file

Posted on 2007-08-07
Last Modified: 2013-11-05
I am trying to create a batch file
sqlplus user/pw@database @123.sql

Is there a way that instead of pointing to 123.sql file I simply include sql command in this batch file such as "alter user ......."

Thanks for help.
Question by:fakher
    LVL 34

    Expert Comment

    UNIX or Windows?

    In a UNIX environment, you can do it like this:

    sqlplus user/pw@database << END
    alter user ...

    Author Comment

    for windows please
    LVL 34

    Expert Comment

    by:Mark Geerlings
    Yes, it is possible in UNIX or Linux (and some people say in Windows too, but I've never tried) to put the actual SQL commands that SQL*Plus will execute in a batch file (or shell script in UNIX or Linux).  I always use the two-file approach:
    1. a *.SQL that contains the actual SQL commands for SQL*Plus, plus an "exit" at the end.
    2. a batch file (for Windows) or a shell script (for UNIX or Linux) that calls SQL*Plus and includes the name of the *.SQL file that SQL*Plus should run.
    LVL 34

    Expert Comment

    by:Mark Geerlings
    I've used Oracle on Windows for 10 years and I find the two-file approach to be the simplest to test and automate in Windows.

    What advantage do you see in having the SQL commands in the batch file itself?

    Author Comment

    I want to run batch file on a remote machine using a remote shell utility and do not want the batch file to read the *.sql from remore machine.
    LVL 14

    Accepted Solution

    Try this

    echo conn userid/password@hostname
    echo select sysdate from dual;
    sqlplus /nolog @%temp%\1.sql
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    >>and some people say in Windows too, but I've never tried

    I'm one of the ones....  and it took a LONG time to figure out.

    Many years ago I got tired of the UNIX people bashing Windows so I came up with a way to do 'here' script style in windows BAT.

    Here's an sample covering simple SQL.  The 'cmdsep' is the secret.
    @echo off
    echo user/password@database
    echo set cmdsep on
    echo set cmdsep '"'; --"
    echo "select sysdate from dual;"
    ) | sqlplus -s

    I've also found tricks for most other non-SQL things you can do at the SQL*Plus prompt.

    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    This video shows how to recover a database from a user managed backup
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now