Oracle: calling udf that modifies the data. How to do it right

oracleoracle-10g

Say I have a function :

create or replace
function test_function return integer 
as PRAGMA AUTONOMOUS_TRANSACTION;
t_test integer :=1;
begin
 update test1 set id = id+1 ; // table test1 has, for simplicity sake, just 1 column,id
 commit;
 return (t_test);  
end;

There are at least two ways I can execute it (so function successfully updates test1) :
1. SELECT test_function FROM dual;
2. SELECT 1 FROM dual WHERE test_fucntion =1;

Which one is the right ?

Also, I can do
3. SELECT 1 FROM dual
INNER JOIN (SELECT test_function FROM dual)a ON (1=1)
which doesn't show an error, but also doesn't update the table.

I'm really curious if it's intended behavior; I don't see why update fails…
I'm using Oracle 10 if it matters.

Thanks.

Best Answer

In #1 and #2, something uses the output of test_function, but in #3 nothing uses it. The optimizer knows this and does not call test_function. Since test_function isn't called, the side effect of updating the table does not happen.

This is definitely intended behavior.

I rewrote your #3 to the following, and test_function got called (the UPDATE happened). The reason is that the result of test_function was needed for output.

SELECT 1, tf FROM dual
INNER JOIN (SELECT test_function as tf FROM dual)a ON (1=1)
;

However, generally speaking relying on the side effect of a function to do real work from inside a SQL query is not a good idea. The optimizer is always looking for a reason to do as little work as possible, and you might be surprised sometimes to learn that the optimizer sees the necessity of your function differently than you do.

Related Question