Re: INSERT in PL/SQL function

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/08/03
Message-ID: <33e71128.4343355@newshost>#1/1

You can't insert/update/delete any table in a procedure that is called from a SELECT statement. Period. It won't work.

If you are working in an environment that only allows inserts/updates/deletes (not sure why you can't call pl/sql directly), you might consider doing all of your checking in a trigger. that is, instead of selecting on the inputs, insert the inputs into a table, let the trigger call foo, and have the trigger raise an error if the procedure fails. for example:

create table foo_inputs( in_data varchar2(200) );

create trigger foo_inputs_aifer
after insert on foo for each row
begin

   foo( :new.in_data );
end;

create trigger foo_inputs_ai
after insert on foo
begin

   delete from foo_inputs;
end;

Now, instead of running foo via a select, you'll run foo by inserting its inputs into foo_inputs. the trigger foo_inputs_aifer (After Insert For Each Row) will call foo. If foo fails, the insert into foo_inputs will fail, and the application will know. If foo suceeds, the insert into foo_inputs will succeed and the after trigger will remove the row from foo_inputs.

Now maybe your application can:

$return_code = insert into foo_inputs( data ) values ( $data );

Now, you can call a procedure in the trigger that does inserts/updates/deletes...

On Wed, 30 Jul 1997 17:18:29 +0200, Susanne Heymann <susanne_at_metronet.de> wrote:

>Hi all,
>
>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
>
>Thanks,
> Susanne
>
>--------------------------------------------------------------------
>Susanne Heymann phone: (+49) (0)221 3091 251
>metronet GmbH fax: (+49) (0)221 3091 298
>Bonner Str. 172-176 e-mail: susanne_at_metronet.de
>D-50968 Köln-Bayenthal www: http://www.metronet.de/~susanne
>--------------------------------------------------------------------
>New address and phone! -- Susanne
>--------------------------------------------------------------------

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Aug 03 1997 - 00:00:00 CDT