Sunday, November 21, 2010

How to redirect sqlplus result in Windows batch script

Recently I had a task to write a Windows batch script for Autonomous RMAN online backup.
In mentioned example, first part of problem is to get sequence numbers from v$log. These numbers will ensure backup of all necessary archive log files. In Linux bash it is very easy and looks like:
MIN1=`echo "select 'xX '||MIN(sequence#) from v\\$log where thread#=1;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`
But on Windows shell it was really a challenge, especially for me-a non Windows guy. So I decide to write this blog to be more like documentation for me and maybe for some others that may fall in same problem.

The solution

echo set timi off head off^&echo. select  MIN(sequence#) from v$log where thread#=1; | sqlplus -s "/ as sysdba" | findstr . > result.tmp
FOR /F %i IN (result.tmp) DO @set MIN1=%i
@echo %MIN1%
del result.tmp
And here is live result:
C:\>echo set timi off head off^&echo. select  MIN(sequence#) from v$log where thread#=1; | sqlplus -s "/ as sysdba" | findstr . > result.tmp

C:\>FOR /F %i IN (result.tmp) DO @set MIN1=%i

C:\>@echo %MIN1%
243

The end

I do not want to mention all the problems that I faced, but the main was "how to create new line" in windows shell (because of "head off and timi off" part that was needed to be passed to sqlplus initially).

As you may see, mine solution store semi result in result.tmp file. I was not able to make solution without file so if anyone of you find a way ... appreciate in front to be informed.

Cheers!

No comments :

Post a Comment

Zagreb u srcu!

Copyright © 2009-2014 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign