3 Replies Latest reply on May 21, 2012 5:10 PM by rp0428

    Difference between ERROR_LOGGING and SAVED EXCEPTION

    928466
      Hi All,
      greetings for the day.

      i would like to know the difference between SAVED EXCEPTION and ERROR_LOGGING.
      my requirement is like without interrupting the main procedure flow (although we got BAD file during the bulk collection, bulk insert )..?
      what should i proceed for this scenario?

      version is 10g..
      thanks in advance
        • 1. Re: Difference between ERROR_LOGGING and SAVED EXCEPTION
          AlbertoFaenza
          pram6291 wrote:
          Hi All,
          greetings for the day.

          i would like to know the difference between SAVED EXCEPTION and ERROR_LOGGING.
          my requirement is like without interrupting the main procedure flow (although we got BAD file during the bulk collection, bulk insert )..?
          what should i proceed for this scenario?

          version is 10g..
          thanks in advance
          Have a look here: [url:http://www.oracle.com/technetwork/issue-archive/2009/09-mar/o29plsql-085126.html]On Avoiding Termination.
          It is an article that explains what you are looking for. Check also at the end the part Which Approach Is Best? and the line starting with Now let’s move on to SAVE EXCEPTIONS versus LOG ERRORS.

          Regards.
          Al
          • 2. Re: Difference between ERROR_LOGGING and SAVED EXCEPTION
            Stew Ashton
            "DML Error Logging" is an SQL thing. When you use it, errors from the INSERT/UPDATE/DELETE go into the error log table and the statement continues.

            If you want a program to do something about the errors, such as notify someone, then the program has to read the error log table.

            If you do INSERT INTO ... SELECT FROM, then errors during INSERT will be logged, but errors during the SELECT (such as explicit data conversions) will cause the whole statement to fail.

            "Save exceptions" is a PL/SQL thing. It is a clause in the FORALL statement. Errors found here are not stored in a table, but put in a collection in memory, and the PL/SQL program must handle them immediately.

            Very often, BULK COLLECT followed by FORALL is not necessary and simple SQL with "error logging" will do.

            If you really have a need for BULK COLLECT and FORALL, use "save exceptions".
            • 3. Re: Difference between ERROR_LOGGING and SAVED EXCEPTION
              >
              i would like to know the difference between SAVED EXCEPTION and ERROR_LOGGING.
              my requirement is like without interrupting the main procedure flow (although we got BAD file during the bulk collection, bulk insert )..?
              what should i proceed for this scenario?
              >
              As already mentioned one is SQL the other is PL/SQL.

              So the first decision to make is whether you need PL/SQL to do what you are doing. Use SQL whenever possible.

              If you are using SQL then you can't use SAVE EXCEPTIONS so the choice between that and LOG ERRORS is made for you.

              There are some major difference between the two:

              1. SAVE EXCEPTIONS will work for all datatypes whereas the use of a log table has restrictions.

              2. SAVE EXCEPTIONS will not provide ANY indication (other than the error code) of which column(s) caused the exception. A log table can be used to check for problems in a user-specified list of columns

              3. SAVE EXCEPTIONS will not providing the offending value whereas a log table will log the offending value so you can examine it.

              4. SAVE EXCEPTIONS will not save any of the offending data you must save it yourself if you want it. The log table will save all of the offending data but you must then manage/delete it yourself.

              5. SAVE EXCEPTIONS will trap ALL errors whereas dml logging will not.
              See 'Error Logging Restrictions and Caveats' in the DBA guide
              http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables004.htm
              >
              Some errors are not logged, and cause the DML operation to terminate and roll back. For a list of these errors and for other DML logging restrictions, see the discussion of the error_logging_clause in the INSERT section of Oracle Database SQL Language Reference.
              >
              And see Restrictions on DML Error Logging in the SQL doc
              http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9014.htm#SQLRF01604


              One use of logging tables is in ETL and data cleansing processes. For example incoming data might be 'dirty': values out of range (i.e. CHECK contraint violations), null data when it must be NOT NULL and the like. If a log table is used this data can be filtered out and saved in the log table where it can be examined and/or fixed.

              If I have 5 columns that might be 'dirty' an error in ANY of the five can cause all five values to be recorded in the log table. Only one of the values actually CAUSED the error (the exception is raised as soon as there is an error) but the other four values will be recorded. Then when I examine the log table I check all five values to determine which ones are 'dirty'. I can then take appropriate action.

              If I were using SAVE EXCEPTIONS I would need to have code that identifies and saves the original source data so I can examine it later.
              1 person found this helpful