5 Replies Latest reply on Dec 3, 2008 4:09 PM by 480488

    Getting a Script to stop execution on first error.

    480488
      I built a script that had 5 inserts. The first 3 should fail, 4 succeed and 5 fail. I'd like to stop execution on the very first failure.

      Is that possible via built in configuration?

      Alterations to the script?

      Is this how it runs in SQL*PLUS?
        • 1. Re: Getting a Script to stop execution on first error.
          -K-
          Not possible yet, but please do vote to add weight on the request at http://htmldb.oracle.com/pls/otn/f?p=42626:39:3359314275153538::NO::P39_ID:561

          Thanks,
          K.
          • 2. Re: Getting a Script to stop execution on first error.
            664398
            Oracle SQL Developer Data Modeling has the ability to generate "Advanced interactive DDL" (see View menu option, File DDL editor). It allows you to skip ERROR codes or to stop conditionally and to restart. You can also define scripts that need to be executed (see table properties, SCRIPTS in the relational model).
            A free download of the second Early Adopter version can be found on: http://www.oracle.com/technology/products/database/sql_developer/files/Modeling.html

            Kind regards,


            René De Vleeschauwer
            SQL Developer Data Modeling team
            • 3. Re: Getting a Script to stop execution on first error.
              -K-
              Would be cool if you could pass the code on to the sqldev team... IMHO it's a basic functionality for a development tool.

              Bedankt in ieder geval,
              K.
              • 4. Re: Getting a Script to stop execution on first error.
                Philip Stoyanov-Oracle
                I think Mark and Rene are talking about two different aspects of "how to control script execution" problem. May be for Mark will be more useful if he adds "whenever" statement at the beginning of the script:
                WHENEVER SQLERROR EXIT 99 COMMIT

                Philip
                • 5. Re: Getting a Script to stop execution on first error.
                  480488
                  Say I have a COTS app. The vendor sends me a script, it has a 1000 changes, some DDL, some inserts to config tables, some updates, some more DDL etc. The way we do it now with toad is, open the file and press F5 (Run as script). When Toad encounters the first error it offers to stop or continue. We stop. Examine the reason for the error, make the corrections, and re-run the script from that point.

                  Currently if we substitute SD for Toad, we could have 900 of the thousand commands execute and a HUGE log of which ones did or didn't and a mountain of correlation to do. That's stopping us from leaving Toad completely.

                  If Philip's suggestion works and all I have to do is add that to the top of the script, they may be a useful workaround.

                  I'll give it a try. Thanks!