4 Replies Latest reply on Jan 19, 2017 10:13 AM by Gaz in Oz

    How to resturn the error level and error message from SQL file to windows batch file

    3230967

      Hi All,

       

      I want to return the error level and error message from my sql file to windows batch file and exit the batch whenever error occurs in the sql.

      Please provide your suggestion on this.

       

      Currently my sql file is  returning the error level as 1 in case of any error\s occurs.

       

      IF /I "%ERRORLEVEL%" NEQ "1" (

      @sqlplus /@xyzdb1 @e:\user\test\test_reports.sql

      )
      IF /I "%ERRORLEVEL%" NEQ "1" (

      @sqlplus /@xyzdb1 @e:\user\test\sample_reports.sql

      )
      IF /I "%ERRORLEVEL%" NEQ "1" (

      @sqlplus /@xyzdb1 @e:\user\test\results_reports.sql

      )

      test_reports.sql

       

      WHENEVER SQLERROR EXIT 1
      SET TERMOUT OFF
      SET LINESIZE 610
      SET PAGES 0
      SET ECHO OFF
      SET TRIMSPOOL ON
      SET FEEDBACK OFF
      SET HEADING OFF
      SET COLSEP ,
      COLUMN DESCRIPTION FORMAT A50

      col spoolname new_value spoolname

      SELECT      'Mytest_report'
               || TO_CHAR (SYSDATE, 'yyyymmdd')
               || '.csv'
                  spoolname
        FROM   DUAL;

      spo '&spoolname';

      SELECT  eno,ename,doj from
      employee;

      SPOOL OFF;

      EXIT 0;

       

      Thanks,

      Ramya