Re: DETERMINISTIC Function in Pkg

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 4 Oct 2007 18:24:28 +0200
Message-ID: <47051510$0$15774$426a34cc@news.free.fr>

"Maxim Demenko" <mdemenko_at_gmail.com> a écrit dans le message de news: 4704D41F.8020303_at_gmail.com...
| Shakespeare schrieb:
| > 1) It is deterministic: it will return the same value at anytime for the
| > same parameter
|
| Whether the function is deterministic - is irrelevant to compiler
| errors/warnings - Oracle can impossible check the logic of pl sql unit (
| in general case of course) and validate, whether it is deterministic or
| not, it is completely responsibility of developer.
|
|
| Session altered.
|
| SQL> create or replace function dont_trustme(i number)
| 2 return number
| 3 deterministic
| 4 is
| 5 begin
| 6 return to_number(to_char(sysdate - i,'sssss'));
| 7 end;
| 8 /
|
| Function created.
|
| SQL> show error
| No errors.
| SQL> select dont_trustme(1) from dual
| 2 /
|
| DONT_TRUSTME(1)
| ---------------
| 50022
|
| SQL> select dont_trustme(1) from dual
| 2 /
|
| DONT_TRUSTME(1)
| ---------------
| 50031
|
|
|
| Best regards
|
| Maxim

Yes but Oracle trust you and optimizer don't call again the function:

SQL> create or replace function dont_trustme (i number)   2 return number
  3 deterministic
  4 is
  5 begin
  6 return dbms_random.value;
  7 end;
  8 /

Function created.

SQL> create or replace function sameone (i number)   2 return number
  3 is
  4 begin
  5 return dbms_random.value;
  6 end;
  7 /

Function created.

SQL> select dont_trustme(1) dont_trustme, sameone(1) sameone   2 from all_objects
  3 where rownum <= 10;
DONT_TRUSTME SAMEONE
------------ ----------

   .96470974 .225453748
  .321966948 .357773657
  .321966948 .484186897
  .321966948  .19917853
  .321966948 .204169781
  .321966948 .356031019
  .321966948 .460143825
  .321966948 .290083481

  .321966948 .101042633
  .321966948 .083357663

10 rows selected.

SQL> /
DONT_TRUSTME SAMEONE
------------ ----------

  .850239643  .98824765
  .594974187 .745916001
  .594974187 .087994863
  .594974187 .036469649
  .594974187 .382895907
  .594974187 .075305796
  .594974187 .550168906
  .594974187 .662560175
  .594974187 .129124224
  .594974187  .93351007

10 rows selected.

By the way, I don't know why it calls it "forgets" the first value.

Regards
Michel Cadot Received on Thu Oct 04 2007 - 11:24:28 CDT