Re: Write No Package State

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 22 Oct 1998 19:07:38 GMT
Message-ID: <36367f49.24757990@192.86.155.100>

A copy of this was sent to Michael Rothwell <michael_rothwell_at_non-hp-usa-om46.om.hp.com> (if that email address didn't require changing) On Thu, 22 Oct 1998 10:14:33 -0700, you wrote:

>OK, here is the function:
>

Nope, need the *package* not just the function. Need the spec and the body.

What is the purity level of valid()?
of holidays() ?
of weekends() ?

What is the purity level of the package itself?

What purity level did you attempt to specify for today?

In order to be called from the where clause, today MUST not write to the package state -- it can read the package state, it can read the database state but it cannot have any side effects.

For example, a valid implementation that allows you to use today in a where clause would be:

SQL> create or replace package workday
2 as

  3          function today( d in date ) return number;
  4          pragma restrict_references( today, wnds, wnps );
  5  
  5          function valid( d in date ) return varchar2;
  6          pragma restrict_references( valid, wnds, wnps );
  7  
  7          function holidays( d in date ) return number;
  8          pragma restrict_references( holidays, wnds, wnps );
  9  
  9          function weekends( d in date ) return number;
 10          pragma restrict_references( weekends, wnds, wnps );
 11  
 11          pragma restrict_references( workday, wnds, wnps, rnps, rnds );

12 end;
13 /

Package created.

SQL> 
SQL> 
SQL> create or replace package body workday
  2  as
  3          function today( d in date ) return number
  4          is
  5                  nCurDay number default 0;
  6          begin
  7                  if valid( d ) = 'YES' then
  8                          nCurDay := to_number( to_char( d, 'DD' ) ) -
  9                                            ( holidays(d) + weekends(d) );
 10                  end if;
 11                  return nCurDay;
 12          end today;
 13  
 13          function valid( d in date ) return varchar2
 14          is
 15          begin
 16                  return 'YES';
 17          end valid;
 18  
 18          function holidays( d in date ) return number
 19          is
 20          begin
 21                  return 0;
 22          end;
 23  
 23          function weekends( d in date ) return number
 24          is
 25          begin
 26                  return 0;
 27          end;

28 end;
29 /

Package body created.

SQL> show errors
No errors.
SQL> l
1 create or replace package body workday 2 as

  3     function today( d in date ) return number
  4     is
  5             nCurDay number default 0;
  6     begin
  7             if valid( d ) = 'YES' then
  8                     nCurDay := to_number( to_char( d, 'DD' ) ) -
  9                                       ( holidays(d) + weekends(d) );
 10             end if;
 11             return nCurDay;
 12     end today;
 13     function valid( d in date ) return varchar2
 14     is
 15     begin
 16             return 'YES';
 17     end valid;
 18     function holidays( d in date ) return number
 19     is
 20     begin
 21             return 0;
 22     end;
 23     function weekends( d in date ) return number
 24     is
 25     begin
 26             return 0;
 27     end;

28* end;
SQL> select workday.today(sysdate) from dual;

WORKDAY.TODAY(SYSDATE)

SQL> select 'test' from dual where workday.today(sysdate)=22;

'TES

test

Note the pragmas are WNDS and WNPS -- that is the minimum you need to call from a where clause. WNDS is the minimum you need to be in a select list, update set clause or insert values statement...

[snip]

>> >
>> >I dont understand why I am getting these errors. How am I
>> >modifying the package state?
>> >
>> >Michael
>>
>> How about posting the package?
>>
>> Thomas Kyte
>> tkyte_at_us.oracle.com
>> Oracle Government
>> Herndon VA
>>
>> --
>> http://govt.us.oracle.com/ -- downloadable utilities
>>
>> ----------------------------------------------------------------------------
>> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>>
>> Anti-Anti Spam Msg: if you want an answer emailed to you,
>> you have to make it easy to get email to you. Any bounced
>> email will be treated the same way i treat SPAM-- I delete it.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Oct 22 1998 - 14:07:38 CDT