Re: INSERT in PL/SQL function

Susanne Heymann wrote:
>
> I'm trying to find a way to insert or update table data within a
> function. Background: The functions are to be called from another
> application that needs a return code to verify that the function
> worked.
>
> I've tried cheating Oracle by encapsulating the insert statement in a
> procedure or hiding it behind dbms_sql but Oracle still tells me that
> the function violates its pragma.
>
> How can I work around it?
>
> Again: what I want to do is the following:
> create or replace function foo (in_data in whatever) return number is
> begin
> if (checks on in_data return TRUE) then
> insert into table values(in_data);
> return 1;
> else
> return 0;
> end if;
> when others then
> return 0;
> end;
>
> The calling applcation should be able to do something like
> $return_code = (SELECT foo($data) from dual;
> and $return_code would then be either 1 or 0

No, no. Oracle will not allow this. Any function that appears in a select statement must make a solemn oath to never make changes to the underlying database. And it must keep that promise! That is the reason the pragma restrict_references exist in the first place.

-- 
Tomm Carr
---- ----
Hunting for a job is like hunting for an elephant.
If you're not *very* careful, you might find one!