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

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 Received on Wed Oct 23 1996 - 00:00:00 CEST