Say I have a function :

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

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.



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.

| improve this answer | |
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.