Running SQL scripts as windows batch file

Question

I am using Oracle 11g and have an Oracle SQL query (not SQL+) as shown below

spool E:\teST\log.txt

select username,account_status, created, default_tablespace 
from dba_users where username='MMCANE';
spool off

exit;

which i intend to run as windows batch file and for that I created the .bat file with configuration as below

sqlplus *username*/*password*@*database* @E:\teST\Untitled.sql

Where Untitled.sql is the select query am running

1. Will I be able to run it as SQL and not as SQL Plus ?

Running the batch file generates the required output as a text file, but since am running it as sqlplus, am getting an unformatted output text file (see below)

   USERNAME ACCOUNT_STATUS                                                            
---------- --------------------------------  
CREATED    
--------------------------------------------------------------
DEFAULT_TABLESPACE                                                                     
--------------------------------------------------------------------------------
   MMCANE OPEN                                                              
13-SEP-17
USERS

as opposed to the script output (see below) that is generated by running the query in SQL developer itself

USERNAME             ACCOUNT_STATUS            CREATED                  DEFAULT_TABLESPACE             
------------------  ----------------------------- ------------------------- ----------------------- 
 MMCANE              OPEN                      13-SEP-17                 USERS

2. Is there a way to format it? If so, where should I give the command?

I tried specifying linesize, wrap and other commands in the query, but was not working since I have written the query in Oracle SQL. Any help would be greatly appreciated.

Thanks.

What do you mean by "SQL query (not SQL+)" and "run it as SQL and not as SQL Plus"? SQL is a language, SQL\*Plus is a client, and your script includes SQL\*Plus commands. (Not sure why you've tagged is as SQL Developer either?) For the formatting, [see this](https://stackoverflow.com/q/3006431/266304). You might also want to [check out SQLcl](https://www.oracle.com/technetwork/developer-tools/sqlcl/overview/index.html) as an alternative. — Alex Poole, Sep 19 '18 at 10:16

score 1 · Accepted Answer · answered Sep 25 '18 at 10:55

VBA script might be the way to go?

dim cn, rs

set cn = CreateObject("ADODB.Connection")
set rs = CreateObject("ADODB.Recordset")
cn.connectionString = "Driver={MySQL ODBC 5.1 Driver};Server=yourServerAddress;" & _
                   "Database=yourDataBase;User=yourUsername;" & _
                   "Password=yourPassword;"
cn.open
rs.open "select * from Countries", cn, 3
rs.MoveFirst
while not rs.eof
    wscript.echo rs(0)
    rs.next
wend
cn.close
wscript.echo "End of program"

Running SQL scripts as windows batch file

1 Answers1