Re: ORA 6571 Function doesn't guarantee NOT to update database
Date: 1996/10/29
Message-ID: <1996Oct29.140831.565_at_ns2>#1/1
In article <DzqG3E.IGJ_at_ranger.daytonoh.ncr.com>, James B. Corbin <jim.corbin_at_DaytonOH.NCR.com> writes:
>I have a function in a package, say APKG.AFUNC(), that takes
>a parameter, does some processing, and returns a VARCHAR2.
>This function works fine in a PL/SQL cursor. However, when I
>call this function like this (a trivial example) at the SQL*Plus
>prompt:
>
>SELECT Apkg.AFunc('abcd') FROM dual;
>
>Oracle returns the ORA-06571 error. The errors text (from the
>"oerr" command) says:
>
>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
>
>The function does NOT update the database, but other procedures
>within the package do - but they are NOT called from this
>function. If I
>remove this function from the package and make it stand-alone, and
>remove calls to ALL other procedures/functions (including
>raise_application_error), the above SELECT statement works.
>
>However, I would like to keep this function in its package, along with
>the raise_application_error call to handle errors. What is the pragma
>referenced in the above error message text? A scour through
>the manuals only mentions the exception_init pragma. I could not
>find any type of reference to Oracle pragmas in general,
>or (more usefully) the pragma I need to solve my problem.
The pragma you need is RESTRICT_REFERENCES, discussed in the PL/SQL Release 2.1 and Oracle Precompilers Release 1.6 Addendum on pages 2-4 to 2-6.
| Peter J.R. Vermaat | Dutch Department of Agriculture | Wageningen |
| p.j.r.vermaat_at_fd.agro.nl | http://www.agro.nl/~vermaat/ |
| Flagellant in Dreamland | ********************************************************************************Received on Tue Oct 29 1996 - 00:00:00 CET