Re: Functions to update database

>
> From: "Luis Diez Porres" <0768495198#luis_at_cuende-asociados.es>
> Date: Mon, 24 Aug 1998 17:59:27 +0200
>
>I need to develop a PL/SQL function that allows me to insert/update a
>table.
>I'm getting an
>
>ORA-06571: function name does not guarantee not to update database
>
>How is I cannot update the database?
>Do I need to change any initialization parameter?
>
>It has to be a function, not a procedure, cause we're working through a
>third party product.

I don't think what you're trying to do will work. Youy may need to try a different approach.

Oracle will not let you call a function which updates the database *from SQL*. ( You can call such a function from PL/SQL, but as you don't want to use procedures, you seem to have ruled this out )

ie. you can't do this ...

     select ....
            my_function(....)
       from my_table
      where ......

If you think about this, you'll see why it's not allowed. Suppose your where clause said "where amount > 500" but my_function() changed the value of the amount column to < 500. Should the row be selected or not? What value of amount should be displayed? How about more subtle problems where my_function() changes the value of amount on *other* rows?

To avoid all these problems, this sort of operation simply isn't allowed. Only functions which do NOT update the database can be called from SQL.

Depending on exactly what you're trying to do, have you considered defining triggers on your table which perform the update your function does, and are fired when an update, insert or delete are performed on the table?

Dave.
--
Remove the no-spam bit from my email address to reply. Received on Tue Aug 25 1998 - 11:01:48 CDT