sysdate in a SQL statement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: sysdate in a SQL statement

  1. #1
    Join Date
    Apr 2001
    Posts
    47

    Question

    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,

  2. #2
    Join Date
    Oct 2001
    Posts
    122

    Wink

    Could be because........
    Sysdate comes out of DUAL and fact that DUAL has only one record I guess it is behaving like this.





  3. #3
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    There has been an interesting discussion on Oracle behaviour with sysdate with reference to time. jmodic has given a good explanation. Hope you will find your answer in this:

    http://www.dbasupport.com/forums/sho...threadid=15390

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  4. #4
    Join Date
    Apr 2001
    Posts
    47
    Originally posted by Raminder
    There has been an interesting discussion on Oracle behaviour with sysdate with reference to time. jmodic has given a good explanation. Hope you will find your answer in this:

    http://www.dbasupport.com/forums/sho...threadid=15390
    A very educative puzzle and great explanation.

    But it is still unclear why Oracle treats built-in and a user defined date functions in different way.
    Both functions are called within a SQL statement, not from PL/SQL, SYSDATE is nondeterministic, so is my_sysdate. Both functions are PL/SQL constructions.
    Of course, SYSDATE function is called "a SQL built-in function", but in fact it is a PL/SQL code defined in STANDARD package...


  5. #5
    Join Date
    Apr 2001
    Posts
    47
    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? :-)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width