accept parameter from sql plus scripts in parallel

[Abhijat]

Hi,
When I run individual sql scripts from DOS batch file one at a time, I do :
call %fpath%\1_A.sql" %2
if %errorlevel% GTR 0 then (goto :ERRBLOCK)

and it works fine. fpath has oracle credentials and path of the sql file. %2 is used to catch the error code received from sqlplus session it had invoked.
Now I want to run 3 scripts simultaneously. So I do:
call %fpath%\1_A.sql" %2 | %fpath%\2_B.sql" %2 | %fpath%\3_C.sql" %2.
if %errorlevel% GTR 0 then (goto :ERRBLOCK)

The idea is to go to ERRBLOCK even if any one of those 3 scripts failed. However, here I only get the error level of the sql sript that finished last. In other words, the problem is -even if two of the scripts failed and exited but if the last script to return to DOS batch file was successful, I will get 0 as errorlevel.
Would you know a way to fix this please?

Kind Regard,
Abhijat.

[Todd Vargo]

First thing I note is the unbalanced quoting on the .sql paths. There is
a trailing quote but no leading quote. When corrected, the pipes are
directing stdout from the first script to stdin of the next script and
so on. Do the scripts really need to pipe output to each other? If not,
you may be able to use the START command to restart the batch with a
parameter to goto a label to run each script in a block of code.

The following batch is untested but it should work. Each START command
restarts the batch using the sql script pathspec as the first parameter,
the batch file's original second parameter as the second, and "restart"
as the third parameter. This is just one way to do it, I am sure others
can code it differently.

@echo off
if "%1" equ "restart" goto :process %1 %2
start "" %0 "%fpath%\1_A.sql" %2 restart
start "" %0 "%fpath%\1_B.sql" %2 restart
start "" %0 "%fpath%\1_C.sql" %2 restart
goto :eof

:process
call %1 %2

if %errorlevel% GTR 0 then (goto :ERRBLOCK)

goto :eof

:ERRBLOCK
echo ERROR running %1

--
Todd Vargo
(Post questions to group only. Remove "z" to email personal messages)

[Abhijat]

Hi Todd,
Thanks for looking into it.
The unbalanced quote has its pair in %fpath%. I can assure you that the code runs fine like that (i.e. %fpath%\1_C.sql" %2 is correct syntax).

The idea is to run the three sql scripts in parallel and catch the error when any one of them fails.

Despite the fact that running them in parallel is acheived by calling them by combining in pipe ( as I showed below ), it seems from your posting that its not the correct logic (I googled a bit as well to read usage of pipes).
So I revise my question - How can I run the three sql scripts in parallel from DOS batch file and catch the error of each, such that, if %errorlevel% GTR 0 for any of them, the code should go to :ERRBLOCK.
At the moment, I manage to run three sql plus sessions in parallel but the errorlevel that is returned after all the three finish is invariably the errorlevel of the script that finished last. Thats is what I want to overcome.
Thanks once again.

Kind Regards,
Abhijat.

[Todd Vargo]

On 9/26/2012 7:42 PM, Abhijat wrote:
> Hi Todd,
> Thanks for looking into it.
> The unbalanced quote has its pair in %fpath%. I can assure you that the code runs fine like that (i.e. %fpath%\1_C.sql" %2 is correct syntax).
>
> The idea is to run the three sql scripts in parallel and catch the error when any one of them fails.
>
> Despite the fact that running them in parallel is acheived by calling them by combining in pipe ( as I showed below ), it seems from your posting that its not the correct logic (I googled a bit as well to read usage of pipes).
> So I revise my question - How can I run the three sql scripts in parallel from DOS batch file and catch the error of each, such that, if %errorlevel% GTR 0 for any of them, the code should go to :ERRBLOCK.
> At the moment, I manage to run three sql plus sessions in parallel but the errorlevel that is returned after all the three finish is invariably the errorlevel of the script that finished last. Thats is what I want to overcome.
> Thanks once again.

The way that you want to do it using pipes will not work for the reasons
that you already stated.

If your syntax is correct, then simply revise my solution to remove the
leading quotes and then run it and observe the results. Please report
only on the actual results that occur (not what you think the logic
seems to be).

The START commands are there to allow the batch to restart itself with
each script running simultaneously in separate processes with each
checking errorlevel within their respective process. Control returns to
the original instance of the batch immediately after each new process
begins.

[billious]

--- (please follow posting protocol) ---

The problem is that your batch file is a single process, and can have
only one ERRORLEVEL.

Here's a way of doing what you want. As ever, each line is indented two
spaces; lines not beginning with two spaces have been wrapped and will
need to be rejoined.



@Echo off
setlocal
set fpath='"C:\106x\
set record="%temp%"\done.
DEL %record%* 2>NUL >nul
SET processes=a b c
set proccnt=0
for %%i in (%processes%) do call :construct %%i %1
:loop
Ping -n 5 127.0.0.1 >nul
set done=0
for %%i in (%processes%) do if exist %record%%%i set /a done+=1
if not %done%==%proccnt% goto loop
(SET done=)
CALL :check %processes%
DEL %record%* 2>NUL >nul
IF DEFINED done goto errblock
GOTO :eof

:check
IF "%1"=="" GOTO :EOF
FOR /f %%i in ( ' type %record%%1 ' ) DO IF NOT %%i==0 SET done=%done% %1
SHIFT
GOTO :check

:errblock
ECHO errors IN process(es) %done%
GOTO :eof

:construct
set /a proccnt+=1
>%record%%1.bat ECHO @ECHO OFF
>>%record%%1.bat ECHO DIR %1*
>>%record%%1.bat ECHO ECHO %%errorlevel%% ^>%record%%1
>>%record%%1.bat ECHO cls^&exit
START /min "running %1" %record%%1.bat
GOTO :eof


Essentially, this creates subsidiary batchfiles in %TEMP% and runs them.
Each subsidiary batch creates a single-line result file containing its
exit errorlevel, also in %TEMP%

I simply executed a DIR command ( :construct +2) - obviously, this would
need to be your SQL. C:\106x is where I develop batch files.

The PING command in :loop is effectively a SLEEP for 5 seconds or so to
stop the process from wasting resources. When all processes have created
their results files, CHECK looks for a non-0 result and accumulates any
non-0-result process names in %done%

Since DIR will always put 0 into the result file, I fudged an error by
simply inserting "1" before %%errorlevel%%

%1 from the main batch is passed to :construct as %2 - I just didn't use
it...

[frank.w...@gmail.com]

From Abhijat:

>The idea is to run the three sql scripts in parallel and
>catch the error when any one of them fails.

What if more then one fails, each with a different
error?

Perhaps something like this:

set "errors="
for %%a in (
%fpath1%"
%fpath2%"
%fpath3%"
) do (
call whatever %%a
if !errorlevel! neq 0 (
call :errorRoutine !errorlevel!
)
)


Frank

[Abhijat]

Hi,
I made it work for me. Many thanks.
Some questions - what is exactly happening in :Loop, what does Defined do? What does Shift do?

Thanks again,
Abhijat.

[billious]

:loop
:: sleep for about 5 secs

Ping -n 5 127.0.0.1 >nul

:: initialise DONE
set done=0
:: For each process in the convenient list,
:: if the file exists,
:: use SET in /a (ie arithmetic) mode to increment DONE
:: (See SET /? from the prompt for docco)

for %%i in (%processes%) do if exist %record%%%i set /a done+=1

:: Loop until the number of result files produced=number of
:: elements in PROCESSES
:: (calculated each time CONSTRUCT is entered to build a
:: subsidiary batch, hence self-adjusting depending on
:: the number of elements specified in PROCESSES)

if not %done%==%proccnt% goto loop

IF DEFINED var

is true if var is defined in the environment.
set var=something
defines var
(set var=)
'sets' var to nothing, which removes it from the environment.
It has become accepted practice in this ng to use one of
(set var=)
set "var="
to clear var because the conventional
set var=
will set var to a value of [any trailing spaces on the line] if there
are trailing spaces on the line - and spaces can be a mite hard to see.

:: (See IF /? from the prompt for docco)

Shift drops the first replaceable argument (%1...) from the list, so
what was %2 becomes %1; %3 becomes %2 and so on.

:: (See SHIFT /? from the prompt for docco)

[Abhijat]

Fantastic !! Thanks very much. This is a very useful learning for me.
Thanks all.

Kind Regards,
Abhijat.

[Todd Vargo]

I ran the untested batch and found I made an error by altering the code
just before posting. Here is a modified and tested code with logging
included. Give it a try.

@echo off
if "%3" equ "restart" goto :process
if "%~2" equ "" goto :SYNTAX
type nul > "%~dp0\sql.log"

start "" %0 %fpath%\1_A.sql" %2 restart
start "" %0 %fpath%\1_B.sql" %2 restart
start "" %0 %fpath%\1_C.sql" %2 restart
goto :eof

:process
call %1 %2

if %errorlevel% GTR 0 (goto :ERRBLOCK)
echo %date% %time% SUCESS: %1 >> "%~dp0\sql.log"
goto :eof

:ERRBLOCK
echo %date% %time% ERROR: running %1 >> "%~dp0\sql.log"
goto :eof

:SYNTAX
echo Syntax: batname parm1 parm2
goto :eof