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

Question

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?

peter.hrasko.sk · Accepted Answer · 2019-11-04 08:26:53Z

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

answered Nov 4, 2019 at 8:26

peter.hrasko.sk

4,1912 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

shruti singh Over a year ago

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 Over a year ago

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

peter.hrasko.sk Over a year ago

@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.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related