Clicky

Hi,

I need batch file to do following job.

1. Run following query from oracle database
    Select status from load_table where job = "Data_push"
2. If output is Completed Go to 4.
3. Else sleep for 10 mins and Go to 1.
4. Execute other batch file.

If possible I would like this script to wait for maximum 2 hours if it cannot go to 4 and after that send mail to admin and exit .

Thanks,
-Naman.

asked 12/11/2011 05:44

namanpatel's gravatar image

namanpatel ♦♦


12 Answers:
Hi, assuming you have sqlplus in your PATH, modify code below so you have correct user, password, database and on the last line your call to the next batch file.

Basically it's checking the output of the query and finding 'Completed' which is not a string in sqlplus output normally. Using a ping command for sleeping in between.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
@echo off
goto main

:sleep
setlocal
@set /a "t = %1 + 1"
@ping 127.0.0.1 -n %t% >nul
endlocal
goto :eof

:main
echo Start of batch file
echo Select status from load_table where job = 'Data_push'; > %TEMP%	.sql
echo exit >> %TEMP%	.sql

:qry
sqlplus user/password@database @%TEMP%	.sql | findstr Completed
if %errorlevel% neq 0 (
 :: not found, sleeping
 call :sleep 600
 goto qry
)

:done
echo End - call next batch file
:: call your next batch file here


See if this works for you, I can add the '2 hour' check later if you like. What application do you have that can send mail messages?
link

answered

gerwinjansen's gravatar image

gerwinjansen

Can you please explain me line 12,13 and 14? sorry I am very new to dos script.

Also, this solution seems to be searching text in output .. is it possible to push query output in local variable and compare it with static value? this will make code more re-usable for me.
link

answered 2011-12-12 at 02:15:25

namanpatel's gravatar image

namanpatel

Regarding mail, I would prefer not to use any application. If possible I want to use dos command to connect to exchange server and send plain text email.
link

answered 2011-12-12 at 03:56:01

namanpatel's gravatar image

namanpatel

Also in sleep section I would like to have display which shows what is the wait cycle number.
link

answered 2011-12-12 at 03:59:13

namanpatel's gravatar image

namanpatel

As I added some requirement .. I would like to increase points but not sure from where I can do that.. please let me know if you have idea.
link

answered 2011-12-12 at 04:17:44

namanpatel's gravatar image

namanpatel

These lines are creating a temporary sql script that is later used in line 17. You set a variable like this:

set FINDIT=Completed

and then change line 17 to:

sqlplus user/password@database @%TEMP%\t.sql | findstr %FINDIT%

then it is using the string in the variable FINDIT to search for.
link

answered 2011-12-12 at 04:21:25

gerwinjansen's gravatar image

gerwinjansen

I want to store output of sql into variable, not static string into variable. Please help.
link

answered 2011-12-12 at 05:35:59

namanpatel's gravatar image

namanpatel

Change this line:
sqlplus user/password@database @%TEMP%\t.sql | findstr Completed

to:
1:
for /f %%a in ('sqlplus user/password@database @%TEMP%	.sql ^| findstr Completed') do set YOURVAR=%%a


>> If possible I want to use dos command to connect to exchange server and send plain text email.
You cannot do this from DOS, sending mail requires some mail application for example blat

Btw: you cannot really add more requirements once the original question has been answered, you can't increase above 500 points. New requirements require a new question from your side :)

I've added your '2 hours' requirement, complete code here:
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
@echo off
goto main

:sleep
setlocal
@set /a "t = %1 + 1"
@ping 127.0.0.1 -n %t% >nul
endlocal
goto :eof

:main
echo Start of batch file
echo Select status from load_table where job = 'Data_push'; > %TEMP%	.sql
echo exit >> %TEMP%	.sql
set count=0

:qry
for /f %%a in ('sqlplus user/password@database @%TEMP%	.sql ^| findstr Completed') do set YOURVAR=%%a
if '%YOURVAR%"' neq 'TheStringYouWantToFind' (
 :: check if 2 hours have passed
 set /a "count = %count% + 1"
 if %count% gtr 12 goto twohours 
 :: sleeping
 call :sleep 600
 goto qry
)

:done
echo End - call next batch file
:: call your next batch file here
exit /b

:twohours
echo Two hours have passed. Exiting
exit /b


Let me know if this works for you.
link

answered 2011-12-12 at 19:30:14

gerwinjansen's gravatar image

gerwinjansen

are your other batch files going to invoke oracle to do other actions?

if so, a stored procedure will be more efficient and easier to implement
link

answered 2011-12-13 at 03:32:07

sdstuber's gravatar image

sdstuber

No its not going to invoke oracle actions..
link

answered 2011-12-13 at 10:23:41

namanpatel's gravatar image

namanpatel

As far as I know previously there was option to increase points.. any way I cannot grant you more point here .. thanks for your solution.
link

answered 2011-12-13 at 21:02:35

namanpatel's gravatar image

namanpatel

You're welcome :)
link

answered 2011-12-13 at 21:04:33

gerwinjansen's gravatar image

gerwinjansen

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×3
×9
×51
×132

Asked: 12/11/2011 05:44

Seen: 394 times

Last updated: 12/13/2011 01:04