0

I need to run a SQL query by connecting to SQL Plus through Batch File.

Hence, I have created a batch file 'test.bat' in notepad and wrote the below contents :-

@echo off
set /p uname=Enter SQLPlus username:
set /p pwd=Enter SQLPlus password with instance:

sqlplus %uname%/%pwd%
select table_name from all tables 
order by table_name;
pause

When I run the batch file I am able to login to the SQLplus, but in cmd it prints SQL>

And I need to enter the select command manually.

How can I get the select statement run automatically?

Amar Kumar
  • 11
  • 6
  • Check [this](https://community.oracle.com/tech/developers/discussion/1075727/how-to-execute-sql-statement-under-bat-file), [this](https://stackoverflow.com/questions/16412040/sqlplus-command-line-with-windows-batch-file) and [this](https://serverfault.com/questions/87035/run-oracle-sql-script-and-exit-from-sqlplus-exe-via-command-prompt) – astentx Oct 28 '20 at 17:30
  • Tried the commands stated there, but still not working – Amar Kumar Oct 28 '20 at 17:46

2 Answers2

0

You can put your SELECT statement in another file which SQL*Plus will call with the @ symbol:

c:\> type test.sql
select sysdate from dual;
exit

c:\> type test.bat
@echo off
set /p uname=Enter SQLPlus username:
set /p pwd=Enter SQLPlus password with instance:
sqlplus -s %uname%/%pwd% @test.sql

C:\> test.bat
Enter SQLPlus username: MYUSER
Enter SQLPlus password with instance: MYPWD

SYSDATE
-------------------
2020-10-28 19:59:46

If creating a separate .SQL file isn't possible you can try something like:

echo select sysdate from dual; | sqlplus -s %uname%/%pwd%
Marco Baldelli
  • 3,638
  • 1
  • 22
  • 29
0

There is a way to do that by

  • enclosing your code into brackets
  • echo-ing every line within brackets
  • and - if necessary (like in my example) - escaping < with 3 ^ signs.
  • finally, pipe it to SQL*Plus

Looks kind of stupid, but it works. Here's how.

The p1.bat script:

@echo off
set /p uname=Enter SQLPlus username:
set /p pwd=Enter SQLPlus password with instance:

(
echo select table_name 
echo from user_tables 
echo where rownum ^^^<= 5 
echo order by table_name;
) | sqlplus %uname%/%pwd%

Testing:

c:\Temp>p1
Enter SQLPlus username:scott
Enter SQLPlus password with instance:tiger

SQL*Plus: Release 11.2.0.2.0 Production on Sri Lis 28 21:45:34 2020

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


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>   2    3    4
TABLE_NAME
------------------------------
BONUS
DEPT
EMP
LINKS
SALGRADE

SQL> Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

c:\Temp>

In my opinion, a separate .SQL file is simpler. See if you can avoid that "restriction" you mentioned.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Hi, @Littlefoot Actually I can't use separate .SQL files as there are multiple SQL queries to run in different parts of the batch file and I have some restrictions on creating new folders & any avoidable files as well. – Amar Kumar Oct 28 '20 at 21:07
  • OK, then use what I suggested. – Littlefoot Oct 28 '20 at 21:10