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

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.

-

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.

-
Thanks, I got it. I know it's not the best way for using functions; I did it mostly for self-education purposes. –  a1ex07 Jan 6 '12 at 14:14