Every tried being evil just for the fun of it? Let me tell you how..,
Oracle Database doesn’t allow you to do DML operations in Functions, oh no… you need a stored procedure for that. However, there is a backdoor Oracle provided for doing this…. behold the PRAGMA AUTONOMOUS TRANSACTION.
So I have a table “test” into which I need to insert a row.
Being what I am I won’t use a stored procedure; Lets make DBA’s earn their salaries.
Creating a function as below;
create or replace
FUNCTION EVIL RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert into test values (‘d’);
commit;
return ‘muahhhaaaaahhaaaa’;
END EVIL;
Running the function
select evil() from test;
There you have it folks., go forth and be the scourge of the database world…….
PS: The origins of my experience on pragma autonomous were not evil, I had done this when I needed to update data into a table during ETL of a datamart but somehow my team mate couldn’t get informatica to do so……. That week I eneded up crashing the database server which lead to one whole day without work to my team mates….
BIdesi 