Maybe there are two different SYSDATE functions?

First example:

SQL> select * from dual;

D
-
X

SQL> select sysdate from user_objects where rownum=1;

SYSDATE
---------
15-OCT-01

SQL> delete from dual;

1 row deleted.

SQL> select * from dual;

no rows selected

SQL> select sysdate from user_objects where rownum=1;

SYSDATE
---------
15-OCT-01 - it still returned the date ! Which means that sysdate did not read dual


Second example:

SQL> select * from dual;

D
-
X

SQL> create or replace function my_date return date
2 is
3 begin
4 return sysdate;
5 end;
6 /

Function created.

SQL> select my_date from all_objects where rownum=1;

MY_DATE
---------
15-OCT-01

SQL> delete from dual;

1 row deleted.

SQL> select my_date from all_objects where rownum=1;

MY_DATE
---------

- it did not return anything - read dual

SQL> rollback;

Rollback complete.

SQL> select my_date from all_objects where rownum=1;

MY_DATE
---------
15-OCT-01


The last one:

SQL> declare
2 v_date date := sysdate;
3 begin
4 null;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> delete from dual;

1 row deleted.

SQL> declare
2 v_date date := sysdate;
3 begin
4 null;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYS.STANDARD", line 586
ORA-06512: at line 2


There should be two SYSDATE functions, which Oracle uses in different way in SQL and PL/SQL environments. In user-defined PL/SQL blocks and functions ORACLE uses STANDARD.SYSDATE. When Oracle execute a SQL statement with such a user function, it evaluates its value for each call, which is expected behavior for a non-deterministic function. In SQL statements Oracle uses another SYSDATE function defined somewhere "inside" Oracle and this function does not read from from dual. When Oracle execute a SQL statement with that function, it evaluates its value only once.

But why so different approach in so similar situations?

Jurij, if you read this, what would you say? :-)