• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9566
  • Last Modified:

SQLPLUS batch file

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.
0
fakher
Asked:
fakher
2 Solutions
 
johnsoneSenior Oracle DBACommented:
UNIX or Windows?

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

sqlplus user/pw@database << END
alter user ...
exit;
END
0
 
fakherAuthor Commented:
for windows please
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Mark GeerlingsDatabase AdministratorCommented:
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?
0
 
fakherAuthor Commented:
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.
Thanks
0
 
sathyagiriCommented:
Try this

@(
echo conn userid/password@hostname
echo select sysdate from dual;
)>%temp%\1.sql
sqlplus /nolog @%temp%\1.sql
0
 
slightwv (䄆 Netminder) Commented:
>>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.

0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now