Re: Can stored functions update a database?

On 27-Jan-98 18:37:12 Charles A. Christiansen, Jr. wrote:

>Hi all,

Moin ;-)

>I'm learning PL/SQL right now, and I'm a bit confused about something
>that seems to be a feature of Oracle and/or the PL/SQL language.
 

>If I create a standalone function that includes SQL UPDATE, INSERT, or
>DELETE statements and try to run it, I get an error ORA-06571:
>"Cannot execute function. Function is not guaranteed to not update
>database". If I include such a function in a package, I have to
>include the WNDS Restrict_References pragma, which also prevents me
>from updating the database.
 

>Is there any way to write a function that CAN update the database?
>Are there similar update restrictions on procedures?

Stored functions *can* update table data, *but* when they do, they cannot be used in plain SQL. So you can do the following:

create or replace function a_function(a_value in number) return number as

begin

   insert into a_table

      (a_column)
   values

      (a_value);

   return a_value*2;
end;
/

In PL/SQL you then can do

declare

my_value number;

begin

   my_value := a_function(5);
end;
/

But you cannot do

select a_function(5) from dual;

Since stored procedures cannot be called from plain SQL, there are not any such oddities.

As always, the correct syntax of statements may differ slightly because here at home I don't have Oracle or manual to check.

Hope that helps,
Lothar

--
Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |