Re: User defined functions in SQL: is it possible?

> I'm trying to write a user-defined function which I can call from SQL.
> eg.
>
> CREATE FUNCTION HR_LOOKUP ( .... )
>
> to be called in a select statement as follows:
>
> SELECT HR_LOOKUP('MARITAL_STATUS',PP.MAR_STAT)
> FROM PER_PEOPLE_F PP
This works because the PL/SQL engine can check the purity level of a stand alone function when it is called.

> So my next experiment was to include the function in a package. The
> package compiles ok, but I get the error ORA-6571, when I try to
> execute a select statement which calls my packaged function.
>
> 06571, 00000, "Function %s does not guarantee not to update database"
> // *Cause: A plsql function that does not have a pragma asserting that
it
> // does not write any database state is referenced in a sql
> statement.
> // Such functions cannot be used in sql statements.
> // *Action: Recreate the function and/or the functions it calls with the
> pragma

Packages are more complex and pragmas must be used to ensure the purity level when they are compiled. There is a good explanation of this in 'PL/SQL Programming' from Oracle Press under the section 'Using stored functions in SQL statements' in chapter 5.

I think what you need is the statement "Pragma Restrict_References(HR_LOOKUP, WNDS);" added to your package header after the function declaration.

WNDS = Writes no database state (or no database object is updated) Received on Wed Jun 24 1998 - 21:46:22 CDT