Re: ORA 6571 Function doesn't guarantee NOT to update database

Manh-Hoa Le wrote:
>
> 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.
> >
> > Can someone help me?
> >
> > Thanks,
> >
> >
> > James B. Corbin (Jim.Corbin_at_DaytonOH.NCR.COM)
> > NCR Corporation - WWIS, US Apps, US Financial & Planning Systems
> > 1529 Brown Street, Building EMD-6 Dayton, OH, 45479 USA
> > Tel: (513) 445-6309 (VP 622-6309)
> >
>
> At the Sql*plus prompt , just execute your function like the following:
>
> > BEGIN Apkg.Afunc('abcd'); END;
>
> Please let me know if you have others solutions.
>
> Hoa LE ("Leh")
> Oracle consultant

I think that you have to add in your package specification next rows:

      PRAGMA RESTRICT_REFERENCES (name_of_function, WNDS,WNPS);

• WNDS mean: write no database state
• WNPS mean: write no package state

Oracle beginner. Received on Thu Oct 24 1996 - 00:00:00 CEST