CODEWITHSUNDEEP
oracle-databasebatch-filesqlplus
shruti singh
shruti singh

Reputation: 166

Run sqlplus commands from multiple files with query logging or spool in Windows Batch File

I am quite new to batch scripting, and I am trying to run multiple sql files, which in turn may contain multiple sql DML/DDL queries from bat file. The output files must contain all the queries being executed and the query output. Unlike this example , I don't have spool command inside my sql file, and I can not edit the input sql files. The following command works for me in ksh file (thanks to here-document):

$sqlplus  /nolog <<! >>$sqlLogs.lst
connect $USERNAME/$PASSWORD@${DBNAME}
set echo on timing on
spool ${SCRIPTRUNFILE_SPOOL}
select name from v\$database;
@${SCRIPTRUNFILE};
spool off
exit
!

I want the exact same in Windows bat file. I have tried using ^. I can't do combine all sql files into one, as I need logging for each sql file into different file. My attempt at the bat file script is as follows and I have played around this much, and it fails with spool command not recognized. I also prefixed below commands with sqlplus, but still unable to achieve something like above ksh file:

sqlplus -s username/pwd@DBName >> sqlLogs.lst
set echo on timing on
spool %RUNFILENAME%.lst
@%RUNFILENAME% > %RUNFILENAME%.lst
select name from v\$database;
spool off
quit

Following logic executes my scripts but does not log the query being executed. Also, I don't want to connect twice to the database. 

echo select name from v$database; | sqlplus -s username/pwd@DBName >> sqlLogs.lst
echo quit | sqlplus -s username/pwd@DBName @%SCRIPTRUNFILE%>> %SCRIPTRUNFILE_SPOOL%.lst

Can someone here please help to spool to a file where I can log the queries as well, while maintaining a single DB Connection?

Upvotes: 0

Views: 1028

Answers (1)

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

Pass your immediate SQL*Plus commands to your sqlplus via stdout, grouped together by Windows' ( and ) symbols...

(
    echo.set echo on timing on
    echo.spool %SCRIPTRUNFILE_SPOOL%
    echo.select name from v$database;
    echo.@%SCRIPTRUNFILE%
    echo.spool off
    echo.exit
) | sqlplus -s %USERNAME%/%PASSWORD%@%DBNAME% >> sqlLogs.lst

Upvotes: 1

Related Questions