Re: Packages - newbie Q

Steve Corbett <stevec_at_fcs.wa.gov.au> wrote:

> I had created some stand-alone procedures and functions - and they
> worked fine.
> Trying to do the right thing I have put them in a Package. Created
> Package and Package Body okay - no errors. Valid Status.
>
> But when i run the function from Sqlplus I get:
>
> select surname, pk_phonetic.fn_phonetic(surname) from table;
> *
> ERROR at line 1:
> ORA-06571: Function FN_PHONETIC does not guarantee not to update
> database
>
> [...]

Hi Steve,

you need to put this into your package specification:

...
FUNCTION fn_phonetic(some_parameter...)
RETURN something;

PRAGMA restrict_references(fn_phonetic, WNDS); ...

This pragma asserts that your function does not update any database tables, which is not allowed for user-defined functions used in SQL statements.

The general syntax for this pragma is:

PRAGMA restrict_references

   (function_name, WNDS [, WNPS] [, RNDS] [, RNPS]);

where WNDS means 'writes no database state', WNPS means 'writes no package state', RNDS is 'reads no database state' and finally, RNPS says 'reads no package state'.

All this is needed by the PL/SQL compiler to know about the side-effects of a function. You can find details on this topic in the 'Oracle Server Application Developers Guide'.

Hope this helps,
Peter

-- 
Peter Schneider
pschneider.ctj_at_metronet.de