| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Calling procedures from PL/SQl functions (called from a SQL stmt)
A copy of this was sent to Ananth Rani <arani_at_ix.netcom.com>
(if that email address didn't require changing)
On Fri, 01 Jan 1999 09:21:44 -0800, you wrote:
>Hi,
>
>This is a bit convoluted but:
>
>- I am using an application that can only execute SQL statements
>- The SQL statement can call user defined functions
>- The function in turn can call procedures
>
>However, I noticed that the procedure that is called cannot do any
>updates, inserts or deletes. Is there a trick to get past this ?
>
sure, don't use SELECT, use INSERTS+TRIGGERS.
Lets say you have a procedure:
create or replace procedure foo( x int number, y in varchar2 )
as
.....
and you can only use SQL to call it. you might:
create table foo_table ( x int, y varchar2 );
create trigger foo_table_trigger
after insert on foo_table
for each row
begin
foo( :new.x, :new.y );
end;
/
create trigger foo_table_trigger2
after insert on foo_table
begin
delete from foo_table;
end;
/
Then a simple:
insert into foo values ( 1, 'Hello' )
will run your procedure. this works in a multi-user environment as well (many people concurrently inserting into foo_table) as no one will ever see anyone elses rows in foo_table and the table will always appear empty.
If you need to call a function to get a return value, its not much harder. Lets say foo was a function that returned a value. We could then add to the creates:
create package my_state_package
as
foos_return_value number;
end;
/
and rewrite the trigger as:
create trigger foo_table_trigger
after insert on foo_table
for each row
begin
my_state_package.foos_return_value := foo( :new.x, :new.y );
end;
/
and then add a function:
create function get_foos_return_value return number
as
begin
return my_state_package.foos_return_value;
end;
/
Then, you would:
insert into foo values ( 1, 'Hello' );
select get_foos_return_value from dual;
to run and get the results of running the function.
>select myfunc(parent) from dual;
>
>Function myfunc
>.....
>bom_exploder(parent) <---- this proc does a BOM explosion which inserts
>
>records into a temp table.
>
>End;
>
>Thanks,
>AR
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jan 01 1999 - 12:38:15 CST
 |
 |