5 Replies Latest reply on Aug 6, 2010 1:50 PM by Ramesh_R

    Batch file / SQLPLUS automation Error message

    710677
      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