RETURNING INTO revisited

While reviewing some code I came across a feature with the RETRUNING INTO clause that I’m not sure I knew about (or I had long forgotten about). In any case it’s worth noting as a follow-up to my earlier RETURNING INTO posts…

As previously noted, RETURNING INTO is used to return scalar values back to PL/SQL from an SQL statement. The RETURNING INTO BULK COLLECT option is used for returning a set of values. The feature noted in a recent code review showed that it’s possible to use aggregate functions with the RETURNING INTO clause to collapse a multi-row operation into one or more scalar values. From a quick search of other posts on the internet, this feature crept in somewhere around Oracle 10g.

A simple example should illustrate things better. First up, we’ll create a simple table with two columns and ten rows:

CREATE TABLE t1
   (id      NUMBER (6) NOT NULL
   ,val     NUMBER (4) NOT NULL)
/

INSERT INTO t1
SELECT ROWNUM
,      ROWNUM
FROM   dual
CONNECT BY ROWNUM <= 10
/

COMMIT
/

Next we’ll write a simple PL/SQL block that increments the VAL column and, using the RETURNING INTO clause, returns the SUM and AVG of the values updated:

DECLARE
   l_sum  NUMBER;
   l_avg  NUMBER;
BEGIN
   UPDATE t1
   SET    val = val + 1
   RETURNING SUM(val), AVG(val) INTO l_sum, l_avg;
 
   dbms_output.enable; 
   dbms_output.put_line ('Sum: ' || TO_CHAR(l_sum));
   dbms_output.put_line ('Avg: ' || TO_CHAR(l_avg));
END;
/   

which gives us a result of:

Sum: 65
Avg: 6.5

The sum value of 65 is (2+3+4+5+6+7+8+9+10+11), which are values the VAL column contains after the update. Obviously the average of 6.5 is the sum of 65 divided by the 10 rows that were modified.

The same applies when deleting rows:

DECLARE
   l_sum  NUMBER;
   l_avg  NUMBER;
BEGIN
   DELETE
   FROM   t1
   WHERE  id BETWEEN 1 AND 4
   RETURNING SUM(val), AVG(val) INTO l_sum, l_avg;
 
   dbms_output.enable; 
   dbms_output.put_line ('Sum: ' || TO_CHAR(l_sum));
   dbms_output.put_line ('Avg: ' || TO_CHAR(l_avg));
END;
/   

which results in (assuming we’re operating on the original table, prior to the UPDATE above):

Sum: 10
Avg: 2.5

Note, it’s not possible to use the DISTINCT clause of the agregate functions:

DECLARE
   l_count  NUMBER;
BEGIN
   UPDATE t1
   SET    val = val + 1
   RETURNING COUNT(id) INTO l_count;
 
   dbms_output.enable; 
   dbms_output.put_line ('Count: ' || TO_CHAR(l_count));
END;
/   

which results in the following error:

ERROR at line 1:
ORA-00934: group function is not allowed here
ORA-06512: at line 4

So, +1 for learning new things from code reviews. Unfortunately the developer totally spoilt it all by using something else I didn’t know about and I would definitely not recommend but that’s a topic for a different post…

Deferrable Constraints

As someone who has done a lot of data manipulation work I strongly support anything that goes to improve the quality of data. Within the database that means I encourage the use of declarative constraints. In Oracle, these are:

• Primary key constraints
• Unique key constraints
• Foreign key constraints
• Check constraints
• Not Null constraints

As anyone who has dealt with databases would know, data inserted or updated must abide by whatever constraints have been declared on a table. Normally this check is made at the point the data modification is made. However, deferrable constraints offers us the ability to delay the checking to the commit point.

So why might we need deferrable constraints? Depending on the data processes it might be that data obtained early on in the process is incomplete and gets supplemented as the process progresses. Deferrable constraints allow us to gradually build up the data knowing that at the end of the transaction the data will be complete. It’s certainly a better situation than dropping the constraints completely!

Let’s look at a very simple example of a deferrable NOT NULL constraint. We’ll create a table with two columns, COL_1 and COL_2, both with NOT NULL constraints but the COL_2 constraint will be deferrable:

CREATE TABLE t1
   (col_1   NUMBER CONSTRAINT col_1_not_null NOT NULL
   ,col_2   NUMBER CONSTRAINT col_2_not_null NOT NULL DEFERRABLE INITIALLY DEFERRED)
/

I’ve taken the liberty of naming the NOT NULL constraints in the above DDL, which is something that I would not normally do for NOT NULL constraints, as we’ll see the names in the exceptions thrown.

If we insert a row into our table with NULL for COL_1 we get:

SQL>INSERT INTO t1
  2  VALUES (NULL, 1)
  3  /
VALUES (NULL, 1)
        *
ERROR at line 2:
ORA-01400: cannot insert NULL into ("DEVELOPER"."T1"."COL_1")

However if the COL_2 value is set to NULL:

SQL>INSERT INTO t1
  2  VALUES (1, NULL)
  3  /

1 row created.

we find that the row gets created. If we were to commit at this point then the NOT NULL constraint check on COL_2 kicks in and we get:

SQL>COMMIT;
COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (DEVELOPER.COL_2_NOT_NULL) violated

However, if we initially insert NULL into COL_2 and then provide a value prior to the commit we find all goes through without error:

SQL>INSERT INTO t1
  2  VALUES (1, NULL)
  3  /

1 row created.

SQL>
SQL>UPDATE t1
  2  SET    COL_2 = 1
  3  WHERE  COL_1 = 1
  4  /

1 row updated.

SQL>COMMIT
  2  /

Commit complete.

You’ll note that the deferrable constraint was declared with the keywords DEFERRABLE INITIALLY DEFERRED. The DEFERRABLE keyword notes that the constraint is capable of being deferred while the INTIALLY DEFERRED part specifies that the constraint defaults to deferred checking. The alternative to this is DEFERRABLE INITIALLY IMMEDIATE, which means the constraint check is not deferred by default but can be made deferrable.

To make all deferrable constraint deferred issue the follow:

ALTER SESSION SET CONSTRAINTS = DEFERRED

and to reverse this, i.e. turn off deferred checking:

ALTER SESSION SET CONSTRAINTS = IMMEDIATE

Use:

ALTER SESSION SET CONSTRAINTS = DEFAULT

puts deferrable constraints into DEFERRED or IMMEDIATE according to how they are declared. A session can change its constraint setting in the middle of a transaction without impacting the DML that has occurred prior the change.

Note, the above commands only impact constraints that are declare to be DEFERRABLE. By default constraints are NOT DEFERRABLE, which means they can never be deferred. The only way to enable deferrable checking on a constraint that has been declared as not deferrable is to drop it and recreate it with one of the deferrable options (at least, as of Oracle 12.1.0.2. I guess future versions may support this).

If you have read my earlier post on unique constraints indexes then you’ll know that by default Oracle will create a unique index to enforce the constraint. If the unique constraint is declared as DEFERRABLE then the index Oracle creates will not be unique. On consideration this makes senses. During the course of a transaction Oracle might have duplicate entries on the unique column(s) and a unique index would prevent this scenario.