Home » Infrastructure » Windows » Batch file / SQLPLUS automation Error message
Batch file / SQLPLUS automation Error message [message #412111] Tue, 07 July 2009 16:51 Go to next message
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
Re: Batch file / SQLPLUS automation Error message [message #412119 is a reply to message #412111] Tue, 07 July 2009 18:37 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
This really is a DOS batch question and not Oracle. Nonetheless, you can test for a files existence with:

if exist C:\test_error\test_error.sql echo.OK
Previous Topic: ORA-12560 : TNS:protocol adapter error
Next Topic: To turn on the VPC once the remote machine is turned on
Goto Forum:
  


Current Time: Mon Dec 29 07:53:52 CST 2014

Total time taken to generate the page: 0.13224 seconds