| Batch file / SQLPLUS automation Error message [message #412111] |
Tue, 07 July 2009 16:51  |
cocl04
Messages: 1
Registered: July 2009
|
Junior Member |
|
|
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/xxxx@database @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/xxxx@database @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 xxx.xxx@center.com -f xxx.xxx@center.com -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
:PERL_ERROR
echo "THE PERL SCRIPT FAILED!!!"
c:\bmail -s 0.0.0 -t xxx.xxx@center.com -f xxx.xxx@center.com -h -a "THE PERL SCRIPT FAILED!!!" -m C:\test_error\perl_logs\%SortDate%_PERL_ERROR.log -c
GOTO END
:PERL_COMPLETE
ECHO "PERL WAS SUCESSFUL. PROCESS COMPLETE"
GOTO End
:END
Thanks,
CC
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
