Tuesday, December 15, 2015

PL/SQL Brain Teaser: Find all the hard-codings!

We all know that hard-coding is a bad thing (well, maybe not all of us. At one training several years past, I asked the audience "Does anyone think hard-coding is a good idea?" and one person raised his hand. Um, OK).

You know hard-coding: when you say to yourself "This is never going to change." and so you put the "value" directly in your code, over and over again.

I put "value" in quotes, because many developers think simply of hard-coded literal values when they think of hard-coding. But I think there are many more ways that hard-coding can seep into our programs.

So I invite you to help find all the hard-codings in the procedure below.

Here's the rule: you can only identify ONE HARD-CODING in each comment. I will delete a submission with > 1. It'll be more fun that way. Promise!



I will give everyone a couple of days to submit your ideas, then offer my own views on the subject.


Monday, December 7, 2015

LOG ERRORS: Suppress row-level errors in DML (from PL/SQL Challenge)

Last week, several hundred players tested their knowledge of the LOG ERRORS feature, and associated with it, the DBMS_ERRLOG package. Check out the quiz here, but feel free to explore the topic below!

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

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?







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.

Tuesday, December 1, 2015

SQL%ROWCOUNT: What/how much did my SQL statement do?

This post is courtesy of the PL/SQL Challenge quiz ending 27 November 2015:

If a SELECT INTO statement without a BULK COLLECT clause returns multiple rows, PL/SQL raises the predefined exception TOO_MANY_ROWS and SQL%ROWCOUNT returns 1, not the actual number of rows that satisfy the query.

Furthermore, the value of SQL%ROWCOUNT attribute is unrelated to the state of a transaction. Therefore: When a transaction rolls back to a savepoint, the value of SQL%ROWCOUNT is not restored to the value it had before the save point. When an autonomous transaction ends, SQL%ROWCOUNT is not restored to the original value in the parent transaction.

Here's the code for the quiz - see how you do!

And of course sign up to take each weekly quiz as it is released (you can even compete for international rankings).

I execute the following statements (which you can easily run yourself on LiveSQL):


CREATE TABLE plch_flowers
(
   id   INTEGER PRIMARY KEY,
   nm   VARCHAR2 (100) UNIQUE
)
/

BEGIN
   INSERT INTO plch_flowers
        VALUES (1, 'Orchid');

   INSERT INTO plch_flowers
        VALUES (2, 'Rose');

   COMMIT;
END;
/

Which of the choices result in "RC=1" being displayed on the screen after execution?

DECLARE
   l_id   INTEGER;
BEGIN
   SELECT id
     INTO l_id
     FROM plch_flowers
    WHERE nm = 'Orchid';

   DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);
END;
/

DECLARE
   l_id   INTEGER;
BEGIN
   SELECT id INTO l_id FROM plch_flowers;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);
END;
/

BEGIN
   INSERT INTO plch_flowers
      SELECT id * 3, UPPER (nm) FROM plch_flowers;

   DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);
END;
/

DECLARE
   l_id   INTEGER;
BEGIN
   INSERT INTO plch_flowers
        VALUES (3, 'Tulip');

   SAVEPOINT inserted_row;

   INSERT INTO plch_flowers
        VALUES (3, 'Lotus');
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK TO inserted_row;
      DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);
END;
/

DECLARE
   l_id   INTEGER;

   PROCEDURE insert_and_save
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      INSERT INTO plch_flowers
         SELECT id * 3, UPPER (nm) FROM plch_flowers;

      COMMIT;
   END;
BEGIN
   INSERT INTO plch_flowers
        VALUES (10, 'Ambrosia');

   insert_and_save;
   DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);
END;
/


Background on SQL%ROWCOUNT

Static or embedded SQL are SQL statements that are written natively into your PL/SQL programs (as opposed to defining them as expressions for execution as dynamic SQL). An implicit cursor is a session cursor that is constructed and managed by PL/SQL. PL/SQL opens an implicit cursor every time you run a SELECT or DML statement. You cannot control an implicit cursor, but you can get information from its attributes. Oracle defines a number of attributes of implicit cursors, whose value can be obtained through the SQL%attribute syntax.

SQL%attribute always refers to the most recently run SELECT or DML statement. If no such statement has run, the value of SQL%attribute is NULL. An implicit cursor closes after its associated statement runs; however, its attribute values remain available until another SELECT or DML statement runs. The most recently run SELECT or DML statement might be in a different scope (another subprogram call that has now completed, for example).

To save an attribute value for later use, assign it to a local variable immediately. Otherwise, other operations, such as subprogram invocations, might change the value of the attribute before you can test it. SQL%ROWCOUNT returns NULL if no SELECT or DML statement has run. Otherwise, it returns the number of rows returned by a SELECT statement or affected by a DML statement (a PLS_INTEGER).

 If a SELECT INTO statement without a BULK COLLECT clause returns multiple rows, PL/SQL raises the predefined exception TOO_MANY_ROWS and SQL%ROWCOUNT returns 1, not the actual number of rows that satisfy the query. Furthermore, the value of SQL%ROWCOUNT attribute is unrelated to the state of a transaction.

Therefore: When a transaction rolls back to a savepoint, the value of SQL%ROWCOUNT is not restored to the value it had before the save point. When an autonomous transaction ends, SQL%ROWCOUNT is not restored to the original value in the parent transaction.