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!