Re: Problem: Function doesn't guarantee not to update database (ORA-06571)

Here are some further details:

Oracle can check stand-alone functions to see if they update the database, so you don't have to do anything special for these.

For a function in a package, you must assert the "purity level" (their phrase , not mind) of the function and of the package initialization code:

create package my_pkg as

• Assert purity of pkg initialization pragma restrict_references (my_pkg, WNDS, RNDS, WNPS, RNPS);

   function funky(param_i in number) returns number;

• Assert purity of function pragma restrict_references (funky, WNDS);

end my_pkg;

The cryptic codes are WNDS - writes no database state (no insert/update/delete)

RNDS - reads no db state

WNPS - writes no package state (iechanges package variables in another package)

RNPS - reads no package state

See the Developer's Guide in the chapter on stored procedures for the full details.

Regards

Louis Cohen

jtdennis_at_atl.mindspring.com (John Dennis) wrote:

>I thought being able to use my own function in my select statments
>would a great time saver. However, there seems to be the limitation
>that the fucntion must quarantee not to update the database. I'm
>trying to identify ways around this or at least identify what to
>avoid.
 

>Here's what I got so far:
 

>Functions (called from selects) cannot be in a package. I didn't
>think this was true til I called the one and only function in a
>package that only had a return statement in it.
 

>Functions (called from selects) cannot call so standard packages like
>raise_application_error.
 

>Any thought or insights on this?
 

>Thanks in advance!
 

>Virtually,
 

>John Dennis
>
>John Dennis
>Tactics, Inc
>Atlanta, GA

Regards

Louis Cohen
San Leandro, CA
louiscoh_at_sprynet.com Received on Wed Jul 31 1996 - 00:00:00 CEST