Re: ORA-06571 HELP!!! HELP!!! HELP!!!

In article <343EB853.DD7_at_arrakis.es>, Ismael Perez <perez32_at_arrakis.es> wrote:
>Ismael Perez wrote:
>
>Hi All,
>
> I have a problem with a Function. I´m using a function that INSERT
>records in a Table.
>The function is in a Package, the function Return a NUMBER. When I
>called this function from
>an SQL expression, such as:
> SELECT <function-name> FROM DUAL;
>
>Oracle return this Error:
> ORA-06571 Function <function-name> does not guarantee not to Update
>Database
>
>How can I resolve this Error...

You can't :

• quote from Oracle7 Server Application Developer’s Guide, page 7-47 ---

To execute a SQL statement that calls a stored function, the Oracle Server must know the purity level of the function. That is, the extent to which the function is free of side effects. In this context, side effects are references to database tables or packaged variables. Side effects can prevent the parallelization of a query, yield order–dependent (and therefore indeterminate) results, or require that package state be maintained across user sessions (which is not allowed). Therefore, the following rules apply to stored functions called from SQL expressions:  The function cannot modify database tables; therefore, it cannot execute an INSERT, UPDATE, or DELETE statement.

---

You can call the function from a PL/SQL block, e.g.:

BEGIN
  DBMS_OUTPUT.PUT_LINE( <function-name> );
END

Stefan.


--
Name      :G.R.S. Deisz
Phone     :+31-50-5855954
E mail    :G.R.S.Deisz_at_PTT-Telecom.Unisource.NL
DISCLAIMER:This statement is not an official statement from, nor
           does it represent an official position of, PTT Telecom BV.