Re: Cannot call function in query

In article <MPG.dffc43c6c29613a989683_at_news.acay.com.au>, Rowan McCammon wrote:

Rowan,

This is to do with Oracle not being able to read what is going on in a function when it is embedded within a package. When the function is stand-alone, Oracle reads the function body and identified what the function will do. To get around this, you must use PRAGMAs: The PRAGMA RESTRICT_REFERENCES (see Application Developers Guide - Calling Packages Functions section) is the one that you need to use.

e.g.
PRAGMA RESTRICT_REFERENCES (function_name,state) where function_name is the name of you function and state is one of:

WNDS - writes no database state (no modification of tables)
WNPS - writes no package state (no mod. of packages variables)
RNDS - read no database state (does not query DB)
RNPS - read no package state (does not ref. the value of packages variables)

regs

Neil Chandler
DBA
TCAM Systems (UK) Ltd.

>PL/SQL: I've declared a function in a package that simply
>returns a constant.
>
>When I use this function in a SQL query I get an error
>message that tells me something like "Cannot perform
>query as database may be updated. Cannot guarantee non-
>update."
>
>If I then define a copy of this function outside the
>package (i.e.. simply a stored function) and call it from
>within a query, the query works fine.
>
>Can anyone explain this to me?
>
>Any help appreciated,
>Rowan McCammon.
Received on Wed Jun 04 1997 - 00:00:00 CDT