# Batch file / SQLPLUS automation Error message



## cocl04 (Jul 7, 2009)

All,
I have a batch file process that runs my sql script in sqlplus. I have error handling / whenever statements in the sql script. I ran into an issue when the database admin accidently changed my directory path for one of my scripts. The script failed, however it did not send me an email. I want to get an email if the path name for the script is incorrects. When I run the batch file manually with an incorrect path, the process hangs. It logs into sqlplus. Once it cannot find the file, I can find a way to exit sqlplus and send a error code back to the batch file. Below is the batch file. I also want to be notified if I can't connect to the database or the login/password is incorrect. As long as the directory is fine and the tns names and database are fine the code runs. I just want to capture the error if it does not run. Can someone help me?

Note: If file does not exist, I can capture the error. However, the batch file hangs. I want to go the the next step and email the content of the captured error.
sqlplus command line:
SQLPLUS -s xxxx/[email protected] @C:\test_error\test_error.sql > C:\test_error\sql_logs\%SortDate%_sql_log_file1.txt 2>&1

Batch file:
@ECHO on
VERIFY ON
SET SortDate=%date%

:START_SQL
SQLPLUS -s xxxx/[email protected] @C:\test_error\test_error1.sql > C:\test_error\sql_logs\%SortDate%_sql_log_file1.txt 2>&1
IF %ERRORLEVEL% == 0 GOTO SQL_SUCESSFUL
IF %ERRORLEVEL% NEQ 0 GOTO SQL_ERROR

:SQL_ERROR
echo "THE SQL SCRIPT FAILED!!!" 
c:\bmail -s 0.0.0 -t [email protected] -f [email protected] -h -a "THE SQL SCRIPT FAILED!!!" -m C:\test_error\sql_logs\%SortDate%_sql_log_file1.txt -c 
GOTO END

:SQL_SUCESSFUL
ECHO "SQL WAS SUCESSFUL" 
c:\test_error\test_dribble_merger_perl.pl > C:\test_error\perl_logs\%SortDate%_PERL_ERROR.log 2>&1
IF %ERRORLEVEL% == 0 GOTO PERL_COMPLETE
IF %ERRORLEVEL% NEQ 0 GOTO PERL_ERROR
GOTO End

ERL_ERROR
echo "THE PERL SCRIPT FAILED!!!"
c:\bmail -s 0.0.0 -t [email protected] -f [email protected] -h -a "THE PERL SCRIPT FAILED!!!" -m C:\test_error\perl_logs\%SortDate%_PERL_ERROR.log -c 
GOTO END

ERL_COMPLETE
ECHO "PERL WAS SUCESSFUL. PROCESS COMPLETE" 
GOTO End

:END

Thanks,

CC


----------

