Re: DETERMINISTIC Function in Pkg

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

"Michel Cadot" <micadot{at}altern{dot}org> a écrit dans le message de news: 47051510$0$15774$426a34cc_at_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
|
|

And if you want a headache:

SQL> select dont_trustme, sameone
  2 from all_objects,

  3       (select dont_trustme(1) dont_trustme from dual),
  4       (select sameone(1) sameone from dual)

  5 where rownum <= 10;
DONT_TRUSTME SAMEONE
------------ ----------

  .397680179 .340579663
  .289646127 .708597201
  .289646127  .59538742
  .289646127 .871229287
  .289646127 .039652609
  .289646127 .280923643
  .289646127 .112305773
  .289646127  .08097983
  .289646127 .474665466
  .289646127 .825750639

10 rows selected.

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

  .280667391 .138434843
  .280667391 .138434843
  .280667391 .138434843
  .280667391 .138434843
  .280667391 .138434843
  .280667391 .138434843
  .280667391 .138434843
  .280667391 .138434843
  .280667391 .138434843
  .280667391 .138434843

10 rows selected.

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