0

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?

shruti singh
  • 146
  • 11

1 Answers1

1

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
peter.hrasko.sk
  • 4,043
  • 2
  • 19
  • 34
  • thanks. The above is able to log the output of the queries that I am running. It is not logging the queries. I tried `echo.set serveroutput on`, but no luck. Can you help here? – shruti singh Nov 04 '19 at 11:29
  • Nevermind. I was able to do it by launching sqlplus with /nolog and then connecting inside as a bulk command. `(echo.connect username/pwd@dbname .... )|sqlplus /nolog` – shruti singh Nov 04 '19 at 12:00
  • @shrutisingh, the `set echo on` should do the exact trick you're asking for. I'm not sure why it doesn't in your case. – peter.hrasko.sk Nov 04 '19 at 13:45