Explicit Cursors For Better Program Control

Arrgh! I just spent several days working through a bug in a large (1000+ line) unfamiliar package that was raising unexpected NO DATA FOUND errors.

After working through several calls to the procedure and monitoring the results I isolated the problem to code very similar to the following.

CREATE OR REPLACE PROCEDURE abc AS
  v_a VARCHAR2(1);

    PROCEDURE def IS
      v_b VARCHAR2(1);
    BEGIN
      SELECT NULL
        INTO v_b
        FROM another_table
       WHERE 1 = 2;
   END;

BEGIN

  -- get row from a_table
   BEGIN
     SELECT NULL
       INTO v_a
       FROM a_table
      WHERE 1 = 2;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
     -- if row not found then run def procedure
     def;
   END;

END;
/

A simple piece of code right? Nope. The call to the DEF procedure in the EXCEPTION handler was raising the NO DATA FOUND error which was not handled.

This caused the calling code to fail.

This brings up one of the things I don’t like to use in PL/SQL – implicit cursors. I agree they are simpler to type than explicit ones but when their associated EXCEPTION handlers are used for program flow it can quickly get unruly. I much prefer explicit cursors such as in this example:

CREATE OR REPLACE PROCEDURE abc AS

  v_a VARCHAR2(1);

  CURSOR curs_a IS
  SELECT NULL
    FROM a_table
   WHERE 1 = 2;

  CURSOR c_another IS
  SELECT NULL
    FROM another_table
   WHERE 1 = 2;

    PROCEDURE def IS
      v_b VARCHAR2(1);
    BEGIN
      OPEN c_another;
      FETCH c_another INTO v_b;
      CLOSE c_another;
    END;

BEGIN

  -- self documenting!?
  -- easy to follow!
  OPEN curs_a;
  FETCH curs_a INTO v_a;
  IF curs_a%NOTFOUND THEN
    def;
  END IF;
  CLOSE curs_a;

END;

Alright that’s enough ranting for today.

Thanks for reading.

Scratch And PLSQL

My daughter showed me a site called Scratch she is using to learn coding. Its at https://scratch.mit.edu/

I decided to see if it could be used to teach PL/SQL as well.

Actually I was just having fun and decided to make slightly related to work…

Move PL the monkey from left to right to catch the correct PLSQL syntax. Press the green flag to start.

Enjoy

DML In A Select

It has long been a sort-of golden rule of Oracle database programming that Data Manipulation (Insert/Update/Delete) cannot be done by a SELECT statement.

Note that I am not referring to DML statements that include a query to determine rows to process.

Oracle Table Functions combined with Autonomous Transactions allow you to violate this rule as shown in this example.

SQL> CREATE TABLE demo
 2   ( col NUMBER );
Table created.

SQL> INSERT INTO demo
 2   VALUES(1);
1 row created.

SQL> INSERT INTO demo
 2   VALUES(2);
1 row created.

SQL> INSERT INTO demo
 2   VALUES(3);
1 row created.

SQL> COMMIT;
Commit complete.

SQL> CREATE TYPE v_n_t AS TABLE OF NUMBER;
 2   /
Type created.

SQL> CREATE OR REPLACE FUNCTION delete_demo
 2   RETURN v_n_t AS
 3     PRAGMA AUTONOMOUS_TRANSACTION;
 4     v_ret_val v_n_t := v_n_t();
 5   BEGIN
 6     v_ret_val.EXTEND;
 7     -- delete all records in the table and return the
 8     -- the count of deleted rows
 9     DELETE demo;
 10    v_ret_val(1) := SQL%ROWCOUNT;
 11    COMMIT;
 12    RETURN(v_ret_val);
 13 END;
 14 /
Function created.

SQL> -- Now SELECT from the TABLE FUNCTION
SQL> -- The rows will magically disappear
SQL> SELECT *
 2     FROM TABLE(delete_demo);
COLUMN_VALUE
------------
           3

SQL> SELECT *
 2     FROM demo;
no rows selected

They records were deleted when the function ran. The downside of this there is no ROLLBACK because the function was declared as AUTONOMOUS. But there is an upside to this as well. I’ll explain that in a future blog post.

Thanks for reading!