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

In article <343EB853.DD7_at_arrakis.es>, Ismael Perez <perez32_at_arrakis.es> writes
>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...
>
> Thanks
>
> e-mail: perez32_at_arrakis.es
> ismael_at_eitb.com

As far as I know when you are using a user defined function in a SELECT statement you must ensure that your function does not update the database (i.e. does execute an insert/update/delete or call another stored code module - such as raise_application_error() - which does this). If you think about it then this makes sense - you are issuing a select after all. In this situation you are specifically performing an insert in your function so the 'pragma restrict references' is of no help to you either.

Therefore you cannot perform the call to your function in the 'select ... from dual;'. If all you want to do is perform one call to the function to return a number in a sql*plus session have you considered just using a simple pl/sql block or an exec? Assuming that your function call is a little more complicated than you have said and uses values from the tables in the select then you should consider using a pl/sql block containing a cursor for loop to select the table values and then calling the function within the loop. If this pl/sql block is run on the server and not the client then there should not be any serious performance overheads.

I hope that this helps.

-- 
Pete Smith