As noted in my previous post, a recent code review revealed a couple of things that I wasn’t aware of… which is one of the pleasures of working in IT; learning new things. Aggregation within the RETURNING INTO clause of a DML statement was a good thing to find out about. I’m having a hard time thinking of a scenario that would legitimately justify the other feature; performing DML within DML…
To demonstrate, let’s create two simple tables, TAB_A and TAB_B, each with two columns; ID and VAL. Both table start off with a small set of data:
CREATE TABLE tab_a (id NUMBER (6) NOT NULL ,val NUMBER (6) NOT NULL ,CONSTRAINT tab_a_pk PRIMARY KEY (id)) / CREATE TABLE tab_b (id NUMBER (6) NOT NULL ,val NUMBER (6) NOT NULL ,CONSTRAINT tab_b_pk PRIMARY KEY (id)) / INSERT INTO tab_a SELECT ROWNUM , ROWNUM FROM dual CONNECT BY ROWNUM <= 10 / INSERT INTO tab_b SELECT ROWNUM , ROWNUM FROM dual CONNECT BY ROWNUM <= 10 / COMMIT /
Next we’ll create a package with two routines. The first routine is a function that sets the VAL value in TAB_A to a random value and returns that value. The second routine is a procedure that sets the VAL value in TAB_B using the return value of the first function:
CREATE OR REPLACE PACKAGE proc_pkg AS FUNCTION val_a (p_id IN tab_a.id%TYPE) RETURN tab_a.val%TYPE; PROCEDURE update_b (p_id IN tab_b.id%TYPE); END proc_pkg; / CREATE OR REPLACE PACKAGE BODY proc_pkg AS FUNCTION val_a (p_id IN tab_a.id%TYPE) RETURN tab_a.val%TYPE AS l_return_value tab_a.val%TYPE; BEGIN UPDATE tab_a SET val = TRUNC(dbms_random.value (100,200)) WHERE id = p_id RETURNING val INTO l_return_value; RETURN l_return_value; END val_a; PROCEDURE update_b (p_id IN tab_b.id%TYPE) AS BEGIN UPDATE tab_b SET val = val_a (id) WHERE id = p_id; END update_b; END proc_pkg; /
If you look at the UPDATE_B routine, we’re updating TABL_B but since we invoke function VAL_A we’re also updating TAB_A. My initial thought when seeing code like this was “hey, that won’t work”… and it seems I was wrong:
SQL> EXEC proc_pkg.update_b (5) PL/SQL procedure successfully completed. SQL> SELECT * 2 FROM tab_a 3 WHERE id = 5 4 / ID VAL ---------- ---------- 5 163 SQL> SELECT * 2 FROM tab_b 3 WHERE id = 5 4 / ID VAL ---------- ---------- 5 163
Okay, so it works; performing DML on one table can invoke a function that performs DML on another table. We can insert, update and delete data on a table from a PL/SQL function invoked via DML on a different table. If we were to try running a simple SELECT query using a function that does DML then we get ORA-14551:
SQL> SELECT id 2 , proc_pkg.val_a (id) 3 FROM tab_a 4 / , proc_pkg.val_a (id) * ERROR at line 2: ORA-14551: cannot perform a DML operation inside a query ORA-06512: at "DEVELOPER.PROC_PKG", line 8
Yes, you can get around this particular error by making the function an autonomous transaction… but don't do that, it's not what autonomous transaction are designed for.
My problem is that I have difficulty thinking of a scenario whereby doing DML from within DML might be a good idea instead of splitting it into separate operations. In some ways it’s similar to a row level trigger and I dislike that approach to data processing as it leads to spaghetti code. Sure, some sort of demo where I want to log details about the execution of the function as it executes I can understand, but in normal business type processing…? Furthermore, keep in mind that we're essentially doing row by row type processing here; our TAB_A DML function will be invoked for each row modified in TAB_B so we're not doing bulk processing on TAB_A. This is likely to be a performance inhibitor for the TAB_A changes.
I guess this is something that I would classify as a nice feature but use only with caution when no other option exists… although I'm happy to take advice on this.
Calling a function with side effects, from an update statement? Ugh – in my books that’s just bad practice and will only end in tears.
I couldn’t agree more, Jeff. I was somewhat surprised that it was possible to do this but I was astonished that someone would actually use the “feature” when it was clearly not necessary.