SQLPLUS command line with Windows batch file

Question

I want to create a batch file which will open the SQLPLUS [CLI] and will execute some stored sql file and will also store the output to text file.

So I've created this batch file [which does not work].
These SQL file contains SQL which returns the max number from a table.

sqlplus scott/tiger@DB
@sql1.sql>data1.txt
@sql2.sql>data2.txt

The problem is it does not executes the SQL files after opening the SQLPLUS

Windows XP
Oracle 9i

score 13 · Accepted Answer · answered May 09 '13 at 17:51

What about native Sql*plus spooling?

run.bat:

sqlplus hr/hr@sandbox @d:\run.sql

run.sql:

spool d:\run.log
set echo on 

select * from dual
/
exit

run.log:

01:50:20 HR@sandbox> 
01:50:20 HR@sandbox> select * from dual
01:50:20   2  /

D
-
X

Elapsed: 00:00:00.00
01:50:21 HR@sandbox> exit

score 4 · Answer 2 · answered May 07 '13 at 06:01

4

SET ORACLE_SID=<YOUR SID HERE>

sqlplus scott/tiger@DB < sql1.sql > data1.txt
sqlplus scott/tiger@DB < sql2.sql > data2.txt

answered May 07 '13 at 06:01

valex

23,966
7
43
60

[ORACLE_SID](http://www.orafaq.com/wiki/ORACLE_SID). You can skip this line if you have one instance on your system. – valex May 07 '13 at 06:48
oops, did not worked. Started the SQLPLUS only but failed to pass arguments ! – Sourav May 08 '13 at 14:41
What's data1.txt and data2.txt for? – Prajwal Jul 10 '19 at 12:55

score 1 · Answer 3 · edited Sep 13 '16 at 11:58

For your information, and for the rest of the community, I was using this command line in a dos file :

sqlplus.exe SIEBEL/mypass@mydb @D:\App\Siebel\EIM\sql\my_sql_command.sql

and the output was :

SQL*Plus: Release 11.2.0.1.0 Production on Mar. Sept. 13 11:53:52 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS : .....

in fact, I had an error in the command line ....

sqlplus.exe SIEBEL/mypass@mydb**%** @D:\App\Siebel\EIM\sql\my_sql_command.sql

SQLPLUS command line with Windows batch file

3 Answers3

Linked