Skip to main content

LOG ERRORS: Suppress row-level errors in DML

Use LOG ERRORS to suppress row-level errors from within the SQL engine when executing non-query DML statements (like inserts, updates and deletes). Instead of raising an error, the SQL engine will insert a row into your error log table - which you then must, really must check after statement execution to see if there were any problems.

Or you will be hiding under the pillows, and your users will really not appreciate that.

In contrast, if you use SAVE EXCEPTIONS with FORALL, you will suppress statement-level errors, but all changes made to rows identified by that statement are rolled back.

Suppose I execute the following statements:
CREATE TABLE plch_employees
   employee_id   INTEGER PRIMARY KEY,
   last_name     VARCHAR2 (100),
   salary        NUMBER (3)

   INSERT INTO plch_employees
        VALUES (100, 'Sumac', 100);

   INSERT INTO plch_employees
        VALUES (200, 'Birch', 50);

   INSERT INTO plch_employees
        VALUES (300, 'Alder', 200);


   DBMS_ERRLOG.create_error_log (dml_table_name => 'PLCH_EMPLOYEES');
I then write the following incomplete block:
   TYPE two_vals_rt IS RECORD
      lowval   NUMBER,
      hival    NUMBER

   TYPE ids_t IS TABLE OF two_vals_rt;

   l_ids     ids_t := ids_t (NULL, NULL);

   l_total   NUMBER;

   PROCEDURE show_sum IS
      SELECT SUM (salary) INTO l_total FROM plch_employees;
      DBMS_OUTPUT.put_line (l_total);
   l_ids (1).lowval := 290;
   l_ids (1).hival := 500;
   l_ids (2).lowval := 75;
   l_ids (2).hival := 275;


Which of the choices provide a replacement for ##REPLACE## so that after the resulting block executes, "800" is displayed?

When you execute a non-SELECT DML statement against a table and an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. You can avoid this situation by using the DML error logging feature.

To use DML error logging, you add a LOG ERRORS statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. Afterwards, you can query the contents of the table to either move your error information into the application log or take corrective action on the rows that caused problems.

The error logging clause allows you to specify an upper limit for the number of errors that are allowed to be logged for that statement. If this number is exceeded, then the statement terminates with an error and all its changes are rolled back. Alternatively, you can specify REJECT LIMIT UNLIMITED to allow any number of errors to be logged.

If Oracle is able to log all the eventual errors without exceeding the limit allowed, the statement can succeed and the changes that were successful are not rolled back.

The error logging is always performed as an autonomous transaction, so that the logged errors are not rolled back when a DML statement fails and/or is rolled back, thus allowing them to be used for checking and error correction.

When is it a good idea to implement error log tables?

Certainly in the development and test phases of a project, they are an excellent mechanism for catching issues that were not captured as part of the application requirements.

In addition, if you are performing "bulk" changes to a table in which a large number of rows are modified by a single DML statement, you may not want to "lose" all changes to rows when a change to one fails. In this case, LOG ERRORS is the only way to go.

Are error log tables needed in production?

Can you safely say that all accepted requirements were correctly implemented, that the requirements are complete and that nothing was left out? If the answer is Yes, then you don't need those tables (nothing can go wrong). If the answer is No, they may help you debug issues as they pop up.

You can implement error log tables (or equivalents thereof) as part of instrumentation that documents execution of application logic, and in many cases that is probably the best way to go. But, for tables that are subjected to nothing but simple DML, yet have constraints and therefore the ability to cause errors, error log tables may be the easiest and "cheapest" way of documenting when something didn't work out the way we thought it would.


Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers. In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply. All the code shown in this example may be found in this LiveSQL script . How to Get a Mutating Table Error I need to implement this rule on my employees table: Your new salary cannot be mo

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch,

Working With JSON Arrays in PL/SQL

Oracle Database 12c Release 2 built upon the 12.1 SQL/JSON features by adding a number of builtin object types (similar to classes in object-oriented languages) for manipulating JSON data in PL/SQL blocks. In this post, I explore some of the array-oriented JSON features, all made available through the JSON_ARRAY_T type and its methods. Just like a class, an object type offers a pre-defined constructor function to instantiate new instances of that type, static methods and member methods. Here are the methods you are most likely to use: A couple of things to remember, generally, about working with JSON elements generally and JSON arrays specifically in PL/SQL: Error Handling Behavior By default, if an error occurs when you call a member method for your JSON array (or object), NULL is returned. In other words, an exception is not  raised back to your block. If you want errors to be propagated from the method as an exception, call the ON_ERROR method and pass a value greate