Hi,

There is one thing about sysdate function that is puzzling me.
If you run

SELECT to_char(sysdate, 'hh:mi:ss') FROM all_objects;

you get the same time in the first and the last rows, inspite the fact that the query ran for several seconds.

Now, if you create a function like:

create or replace function my_sysdate return date
deterministic
is
begin
return sysdate;
end;

and run another statement:

SELECT to_char(my_sysdate, 'hh:mi:ss') FROM all_objects;

you get different time in the rows during execution.

That means that Oracle calls sysdate function only once and uses its value for each row, but calls my_sysdate function for each row. The only difference between the two functions is that sysdate function is built-in and my_sysdate is user-defined.

If, for example, due to some reasons Oracle considers sysdate function as deterministic, then why it does not consider my_sysdate function as deterministic as well?
If, on the opposite, Oracle considers sysdate function as nondeterministic and tries to follow 'read consistancy' conception, then why it does not follow the conception in the second SQL statement?

What could be an explanation of such Oracle behavior?
I could not find any answer in Oarcle documentation.

Any thoughts would be greatly appreciated.

Thank you,