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

Question

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.

Answer 1

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.