4 Replies Latest reply on Jun 23, 2011 5:47 PM by Billy~Verreynne

    Trapping Error in SQL PLUS script

    MarcLaf
      Hi,

      I have a sqlplus script that executes other .sql scripts. But, I want to ABORT the script if there's an error in one of the scripts:

      Set echo off
      Set scan off
      SET SERVEROUTPUT ON SIZE 1000000
      SET TIMING ON
      
      @00_Pre-Deployment_Scripts.sql
      @01_LOAD_LCP_Contacts_prcs.sql
      @02_LCP_Contacts_Scripts.sql
      @03_LOAD_Phones_prcs.sql
      @04_Phone_Scripts.sql
      @05_LOAD_employer_TP_business_prcs.sql
      @06_Employer_TP_Business_Details_Scripts.sql
      @07_LOAD_Contact_Scripts_prcs.sql
      @08_Contact_Scripts.sql
      @98_Clean_Up_Scripts.sql
      Above is the content of my DOS script. Is there a way to check the RETURN CODE after each execute statement, and exit (ABORT) the script?

      Please Help ...

      Thanks in advance,

      Marc
        • 1. Re: Trapping Error in SQL PLUS script
          6363
          http://download.oracle.com/docs/cd/E11882_01/server.112/e16604/ch_twelve052.htm#SQPUG135

          >
          WHENEVER SQLERROR {EXIT [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}

          EXIT [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable]

          Directs SQL*Plus to exit as soon as it detects a SQL command or PL/SQL block error (but after printing the error message). SQL*Plus will not exit on a SQL*Plus error. The EXIT clause of WHENEVER SQLERROR follows the same syntax as the EXIT command. See EXIT for more information
          • 2. Re: Trapping Error in SQL PLUS script
            Centinul
            You could try the WHENEVER OSERROR or WHENEVER SQLERROR options in SQL*Plus.
            • 3. Re: Trapping Error in SQL PLUS script
              MarcLaf
              So,

              If I understand correctly, I should put the WHENEVER SQLERROR EXIT SQL.SQLCODE line

              BEFORE the sqlplus statement. The 'WHENEVER...' command will act on the next statement, right?

              Let's say I want to EXIT if the execution of CNVRT_LCP_SPOUSE_CNTCTS_PRC FAILS. I would write my code this way:

              Set echo on
              Set scan off
              SET SERVEROUTPUT ON SIZE 1000000
              SET TIMING ON
              
              Spool CNVRT_LCP_NAMES_PRC.lst
              EXECUTE CNVRT_LCP_NAMES_PRC
              Spool off
              
              WHENEVER SQLERROR EXIT SQL.SQLCODE
              Spool CNVRT_LCP_SPOUSE_CNTCTS_PRC.lst
              EXECUTE CNVRT_LCP_SPOUSE_CNTCTS_PRC
              Spool off
              
              Spool CNVRT_MERGED_LCP_NAMES_PRC.lst
              EXECUTE CNVRT_MERGED_LCP_NAMES_PRC
              Spool off
              
              Spool CNVRT_MRGD_LCP_SPSE_CNTCTS_PRC.lst
              EXECUTE CNVRT_MRGD_LCP_SPSE_CNTCTS_PRC
              Spool off
              Thanks,

              Marc
              • 4. Re: Trapping Error in SQL PLUS script
                Billy~Verreynne
                MarcLaf wrote:

                Above is the content of my DOS script.
                Impossible. Oracle does not run on MS-DOS anymore for well over a decade now. And I bet that you are not using the DOS operating system either.

                Yes - pet peeve of mine. Do not call a 32bit console virtual machine, DOS. It has nothing in common with DOS.
                Is there a way to check the RETURN CODE after each execute statement, and exit (ABORT) the script?
                Use the SQL*Plus WHENEVER ERROR command to force an exit in case of a SQL or PL/SQL exception.