VF=>user  ID=>    Login Feedback FAQ Blog
1,050,815 quizzes played | 1,461 active players

Actions

Important News!

We've built a new "skin" for our quiz platform: the Oracle Dev Gym. We'd love to hear what you think of it. Click on the Dev Gym image on the right of your home page to check it out.



  On Suppress DML Errors with LOG ERRORS: Row-level suppression of exceptions (Oracle PL/SQL)      Add to Favorites

Summary

Use LOG ERRORS to suppress row-level errors from within the SQL engine, instead writing information to the error log table. 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.

Details

Assumes Oracle Database 11g or higher
Advanced Quiz, IDs: 13561/1335554/830111

Reviewer(s): Darryl Hurley, Vitaliy Lyanchevskiy

The Question

I execute the following statements:

CREATE TABLE plch_employees
(
   employee_id   INTEGER PRIMARY KEY,
   last_name     VARCHAR2 (100),
   salary        NUMBER (3)
)
/

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

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

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

   COMMIT;
END;
/

BEGIN
   DBMS_ERRLOG.create_error_log (dml_table_name => 'PLCH_EMPLOYEES');
END;
/

I then write the following incomplete block:

DECLARE
   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
   BEGIN
      SELECT SUM (salary) INTO l_total FROM plch_employees;
      DBMS_OUTPUT.put_line (l_total);
   END;
BEGIN
   l_ids (1).lowval := 290;
   l_ids (1).hival := 500;
   l_ids (2).lowval := 75;
   l_ids (2).hival := 275;

##REPLACE##

   show_sum;
EXCEPTION 
   WHEN OTHERS 
   THEN 
       show_sum;
END; 
/

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

The Choices [↑]

Explanation of Result Icons
Choice 1 (26741) [Do Not Use]
   FORALL indx IN 1 .. l_ids.COUNT
      UPDATE plch_employees
         SET salary = salary * 10
       WHERE employee_id BETWEEN l_ids (indx).lowval
                           AND l_ids (indx).hival
         AND salary < 100;

In this choice, I "filter out" through the WHERE clause all those rows which could cause an too-large number for salary when the raise was applied.

This choice is not recommended for two reasons:

1. Since we never even try to apply the raise to those other employees, there are no errors, and therefore no chance to record the fact that we attempted a raise and it failed. This might be (and usually is) important to do.

2. The solution is "hard-coded" - it assumes the maximum size of the salary will never change. That's not a smart assumption to make.




64%
Choice 2 (26737) [Do Not Use]
   FOR indx IN 1 .. l_ids.COUNT
   LOOP
      FOR rec
         IN (SELECT employee_id
               FROM plch_employees
              WHERE employee_id BETWEEN l_ids (indx).lowval
                                    AND l_ids (indx).hival)
      LOOP
         BEGIN
            UPDATE plch_employees
               SET salary = salary * 10
             WHERE employee_id = rec.employee_id;
         EXCEPTION
            WHEN OTHERS
            THEN
               NULL;
         END;
      END LOOP;
   END LOOP;

In this case, I have two loops:

1. The outer loop iterates through the array (two elements).

2. The inner loop uses the values in the array to set up a cursor FOR loop that then updates a single row at a time.

We then trap any error and keep on going. Since we are now executing four UPDATE statements, each of which updates a single row, we achieve a similar effect to that of LOG ERRORS, and the total salary is increased to 800.

If, however, you do need to suppress row-level errors and apply changes to as many rows as possible, use LOG ERRORS instead. It is simpler, declarative, and still allows you to use FORALL, critical for optimal performance.




52%
Choice 3 (26738) [Recommended Solution]
   FORALL indx IN 1 .. l_ids.COUNT
   UPDATE plch_employees SET salary = salary * 10
   WHERE employee_id
      BETWEEN l_ids(indx).lowval AND l_ids(indx).hival
      LOG ERRORS REJECT LIMIT UNLIMITED;

This choice relies on LOG ERRORS to suppress errors at the row level, allowing the SQL engine to continue modifying all the rows that it can. Failure information is written to the log table.

Employees 100 and 300 cannot have their salary increased by a factor of 10, since the constraint on the salary column is 3 digits - NUMBER(3). So only employee 200 can receive the increase.

The new total salary is 800.




60%
Choice 4 (26740)
   FORALL indx IN 1 .. l_ids.COUNT SAVE EXCEPTIONS
      UPDATE plch_employees
         SET salary = salary * 10
       WHERE employee_id
          BETWEEN l_ids (indx).lowval AND l_ids (indx).hival;

This choices uses SAVE EXCEPTIONS to suppress statement-level failures and allow FORALL to process the remaining statements. Each statement executed by FORALL in this case, however, fails because the new salary is too large. So all changes to rows identified by each statement are rolled back and the total salary remains 350.




53%
Choice 5 (26736) [Do Not Use]
   FORALL indx IN 1 .. l_ids.COUNT
      UPDATE 
         ( SELECT employee_id, salary, salary * 10 as new_salary
             FROM plch_employees
            WHERE employee_id BETWEEN l_ids (indx).lowval
                                  AND l_ids (indx).hival)
         SET salary = new_salary
       WHERE new_salary <= 999;

In this choice, I "filter out" all those rows which could cause an too-large number for salary when the raise was applied. It also demonstrates how you can use a subquery inside the UPDATE statement in place of the table.

This choice is not recommended for two reasons:

1. Since we never even try to apply the raise to those other employees, there are no errors, and therefore no chance to record the fact that we attempted a raise and it failed. This might be (and usually is) important to do.

2. The solution is "hard-coded" - it assumes the maximum size of the salary will never change. That's not a smart assumption to make.




57%

Answer [↑]

You will encounter several kinds of errors and warnings when working with PL/SQL program units:

1. Compilation errors: your programs will not compile. The errors reported by the PL/SQL compiler are of the form "PLS-NNNN: message".

2. Compile-time warnings: While compiling stored PL/SQL units, the PL/SQL compiler generates warnings for conditions that are not serious enough to cause errors and prevent compilation—for example, using a deprecated PL/SQL feature. Warnings take the form "PLW-NNNN: message".

3. Run-time errors: you (or your users) run your program and an error (known as an exception) is raised. Run-time errors generally take the form "ORA-NNNN: message".

You can write an exception handler in a PL/SQL block to trap and handle run-time errors.


The PL/SQL run-time engine will raise exceptions whenever the Oracle database detects a problem or it executes a RAISE or RAISE_APPLICATION_ERROR statement in your code. You can then trap or handle these exceptions in the exception section - or let the exception propagate unhandled to the enclosing block or host environment.


If you want processing in your block to continue, even if an exception was raised, enclose the code that might raise an exception in its own BEGIN-END nested block. Then add an exception to that block, trap the error, record information about what went wrong, and then allow the (now) outer block to continue.


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.

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.


Statistics and Rankings [↑]

Rank Percentile Score Seconds% Correct
Median
72
50
336
342
60
Best
1
100
744
18
100

Click to View Verification Code [↑]

Quiz Link and Tweet [↑]

Pass along a link the quiz or use our text to tweet about this quiz. Thanks for helping to spread the word!

 
About Oracle | Terms of Use | Your Privacy Rights | Copyright 2010-2017, Oracle Corporation