SQLPLUS command line with Windows batch file
Solution 1
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
Solution 2
SET ORACLE_SID=<YOUR SID HERE>
sqlplus scott/tiger@DB < sql1.sql > data1.txt
sqlplus scott/tiger@DB < sql2.sql > data2.txt
Solution 3
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
Sourav
Hi, I am Sourav Ghosh :) You can find my projects * AJAX Micro Mini Lib [JS] * Autorun Cleaner [VB.NET] * C Code Completer [VB.NET] * DockBar Develop [VB.NET] * ShoutOut-Twitter [ASP.NET + C#] * Ultra Light CAPTCHA [PHP] * Ultra Light Forum [PHP] * Wallpaper Changer [VB.NET]** @ http://sourceforge.net/users/sourav1989 I'll be really happy if you find them useful :) Thanks :)
Updated on May 30, 2020Comments
-
Sourav about 4 years
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
-
valex about 11 yearsORACLE_SID. You can skip this line if you have one instance on your system.
-
Sourav about 11 yearsoops, did not worked. Started the SQLPLUS only but failed to pass arguments !
-
Prajwal almost 5 yearsWhat's data1.txt and data2.txt for?