Re: Update in Oracle Function.

A copy of this was sent to "Keith Jamieson" <jamiesonk_at_phoenix.ie> (if that email address didn't require changing) On Mon, 1 Nov 1999 15:46:08 -0000, you wrote:

>You can only query the database using Oracle Functions. You need to convert
>your function into a procedure.
>

You can update in functions as easily as procedures. Do not convert anything to procedures.

The problem below is that they are attempting to "select" the function that writes to the database -- a select is not allowed to write to the database (except with autonomous transactions in Oracle8i, release 8.1).

They should:

declare

   return_val varchar2(255);
begin

   return_val := f(x);
end;

instead of "select f(x) from dual" or more simply:

SQL> exec dbms_output.put_line( f(x) );

to execute a function that updates the database.

>Sudheer K G wrote in message <7vect8$8ls$1_at_nnrp1.deja.com>...
>>Hello All,
>>
>>Is it possible to write an update statement in an Oracle Standalone
>>Function? When I tried it gave me the error "ORA-06571: Function X_UPD
>>does not guarantee not to update database" What does it really mean? Is
>>it any other way to do this using functions? (ORACLE 7.3)
>>
>>Appreciate all the helps..
>>
>>Sudheer
>>
>>
>>Sent via Deja.com http://www.deja.com/
>>Before you buy.
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Nov 01 1999 - 10:19:39 CST