Today, at my current project, I came across an issue where autonomous transactions came in handy.
The situation: I need to create a query to perform an export. A couple of the fields to be selected come from a global temporary table, nothing fancy so far except this global temporary table is filled by a (rather complex) procedure. Another problem is this table is emptied for every row, i.e. it will contain only one row at a time. ‘Just build a wrapper table function for this procedure and have that function call the procedure’ was my first idea.
I created a script that shows the situation
CREATE GLOBAL TEMPORARY TABLE empdate ( empno NUMBER(4) , hiredate DATE ) ON COMMIT DELETE ROWS /
CREATE OR REPLACE PROCEDURE getthehiredate(empno_in IN NUMBER) IS BEGIN DELETE FROM empdate; INSERT INTO empdate (empno ,hiredate) (SELECT empno ,hiredate FROM emp WHERE empno = empno_in); END getthehiredate; /
Then I set out to build a pipelined table function that accepts a cursor as one of its parameters. This function then loops all the values in the cursor, calls the procedure, reads the data from the global temporary table and pipes out the resulting record, nothing really fancy so far.
CREATE TYPE empdate_t AS OBJECT ( empno NUMBER(4), hiredate DATE ) /
CREATE TYPE empdate_tab IS TABLE OF empdate_t /
CREATE OR REPLACE FUNCTION getallhiredates(empnos_in IN SYS_REFCURSOR) RETURN empdate_tab PIPELINED IS l_empno NUMBER(4); l_returnvalue empdate_t; BEGIN FETCH empnos_in INTO l_empno; WHILE empnos_in%FOUND LOOP getthehiredate(empno_in => l_empno); SELECT empdate_t(ed.empno, ed.hiredate) INTO l_returnvalue FROM empdate ed WHERE 1 = 1 AND ed.empno = l_empno; PIPE ROW(l_returnvalue); FETCH empnos_in INTO l_empno; END LOOP; RETURN; END getallhiredates; /
But when I ran a query against this function:
SELECT * FROM TABLE(getallhiredates(CURSOR (SELECT empno FROM emp))) /
I ran into an error:
ORA-14551: cannot perform a DML operation inside a query
So, all the work I done so far had been for nothing? Time wasted? I don’t think so. If there is anything I learned over the years it is that Oracle tries to stop you doing certain things but at the same time supplies you the tools to create a work-around.
There is something like an autonomous transaction, that might help me in this case so I changed the code for the function a bit:
CREATE OR REPLACE FUNCTION getallhiredates(empnos_in IN SYS_REFCURSOR) RETURN empdate_tab PIPELINED IS PRAGMA AUTONOMOUS_TRANSACTION; l_empno NUMBER(4); l_returnvalue empdate_t; BEGIN FETCH empnos_in INTO l_empno; WHILE empnos_in%FOUND LOOP getthehiredate(empno_in => l_empno); SELECT empdate_t(ed.empno, ed.hiredate) INTO l_returnvalue FROM empdate ed WHERE 1 = 1 AND ed.empno = l_empno; PIPE ROW(l_returnvalue); FETCH empnos_in INTO l_empno; END LOOP; COMMIT; RETURN; END getallhiredates; /But when I ran the query:
SELECT * FROM TABLE(getallhiredates(CURSOR (SELECT empno FROM emp))) /I ran into a different error:
ORA-06519: active autonomous transaction detected and rolled back
So this doesn’t work or does it? Pipelined table functions have ‘exit’ the function multiple times. Whenever a row is piped out. So, I tried to put the COMMIT just before the PIPE ROW command:
CREATE OR REPLACE FUNCTION getallhiredates(empnos_in IN SYS_REFCURSOR) RETURN empdate_tab PIPELINED IS PRAGMA AUTONOMOUS_TRANSACTION; l_empno NUMBER(4); l_returnvalue empdate_t; BEGIN FETCH empnos_in INTO l_empno; WHILE empnos_in%FOUND LOOP getthehiredate(empno_in => l_empno); SELECT empdate_t(ed.empno, ed.hiredate) INTO l_returnvalue FROM empdate ed WHERE 1 = 1 AND ed.empno = l_empno; COMMIT; PIPE ROW(l_returnvalue); FETCH empnos_in INTO l_empno; END LOOP; RETURN; END getallhiredates; /And when I ran my statement again:
SELECT * FROM TABLE(getallhiredates(CURSOR (SELECT empno FROM emp))) /It worked as I hoped for.
As you can see I have tried to mimic the situation using the EMP and DEPT tables. I think this is a nice little trick, but it should be used with caution. It is not for no reason that Oracle prevents you from running DML inside a query, but in this case I can bypass this restriction.
Patrick,
Thanks for this solution. It has saved me a lot of time.
I will buy you a beer next time we meet.
Excelente solución!
Yo recién comienzo con Oracle, y estaba convirtiendo una SP de Informix que tiene casi quinientas líneas, y me encontré con exactamente los mismo errores que tú.
Ahora funciona perfectamente.
Gracias, gracias, gracias!
Thanks to Google Translate:
Excellent solution!
I was just starting with Oracle, and I was converting an Informix SP that is almost five hundred lines long, and I ran into the exact same errors as you.
Now it works perfectly.
Thank you thank you thank you!
Thank you for your comment.