Anything like a 'function pointer' in PL/SQL?

[sausti...@my-deja.com]

Anyone know of a way in Oracle 8i PL/SQL to make a FUNCTION or
PROCEDURE call by specifying the function name as a variable? That is,
say I have the following defined:

PROCEDURE abc_proc ;
FUNCTION xyz_func ( in1 CHAR ) RETURN BOOLEAN ;

and I have a table like this

CREATE TABLE Processing_Config (
measurementType VARCHAR2(128) ,
functionToProcess VARCHAR2(128)
) ;
INSERT INTO Processing_Config VALUES ( 'abc_val' , 'abc_proc' ) ;
INSERT INTO Processing_Config VALUES ( 'xyz_val' , 'xyz_func' ) ;

then in my PL/SQL program, when I encounter a measurementType
of 'abc_val' I want to call abc_proc. I realize I can do a
giant IF-THEN-ELSE statement, but I'd rather just pull the
functionToProcess from the table and have some way of invoking it.

A built-in package, perhaps, or REF, or something? I don't think
dynamic SQL exactly does what I want - I need "dynamic PL/SQL!"

In C I would have an array of pointers to functions to do this
(jump table). BTW I can standardize so that they're all FUNCTION
calls with the same parameter list, if this helps.

Thanks in advance!!

Cheers

Sent via Deja.com http://www.deja.com/
Before you buy.

[Joel R. Kallman]

Using native dynamic SQL in Oracle8i, this is very easy to do.

As a short example:

--------
create or replace procedure foo1( p_name in varchar2 )
as
begin
dbms_output.put_line( 'Howdy, ' || p_name );
end;
/

create or replace procedure foo2( p_name in varchar2 )
as
begin
dbms_output.put_line( 'Bon jour, ' || p_name );
end;
/

create or replace procedure callit( p_username in varchar2 )
as
l_stmt varchar2(255) := NULL;
begin
if p_username = 'Joel' then
l_stmt := 'begin foo1( p_name=>:p_username ); end;';
elsif p_username = 'Francois' then
l_stmt := 'begin foo2( p_name=>:p_username ); end;';
end if;
--
if l_stmt is not null then
execute immediate l_stmt using p_username;
end if;
end;
/
--------

SQL> set serveroutput on
SQL> exec callit('Joel');
Howdy, Joel

PL/SQL procedure successfully completed.

SQL> exec callit('Francois');
Bon jour, Francois

PL/SQL procedure successfully completed.

SQL> exit

You still have to have your conditional logic somewhere, to actually
build the statement for the procedure you want to call. But using the
EXECUTE IMMEDIATE statement makes this almost trivial.

Thanks!

Joel

Joel R. Kallman
Oracle Service Industries
Columbus, OH http://govt.us.oracle.com
jkal...@us.oracle.com http://www.oracle.com

----
The statements and opinions expressed here are my own
and do not necessarily represent those of Oracle Corporation.