Re: PL/SQL function as a parameter to another PL/SQL function?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/05/08
Message-ID: <337122b7.807501@newshost>#1/1

On Wed, 7 May 97 22:57:39 +0200, ms_at_dream.hb.north.de (Martin Schroeder) wrote:

>In <3371d278.13991168_at_newshost> tkyte_at_us.oracle.com (Thomas Kyte) writes:
>>On 6 May 1997 21:15:14 GMT, "Brian Gastineau" <bgastine_at_giveblood.org> wrote:
 

>>>Can a PL/SQL function be passed into another PL/SQL function? My situation
>>>is that during conversion of data, several different tests will be run
>>>against each record, but the response to the true/false condition will be
>>>the same.
>>>
>>>Suggestions for workarounds would also be appreciated.
>>>
>>>Thanks,
>>>Brian Gastineau
>>>bgastine_at_giveblood.org
 

>>Yes, pl/sql functions may be used as IN parameters to other pl/sql functions,
>>for example:
>
>[Example of use of functions in expression snipped]
>
>What Thomas means is called 'procedure variable' in Modula-2 and
>'function pointer' in C.
>
>AFAIK it's impossible in PL/SQL.

Ahh, Ok, if you read it that way then.........

Perhaps a package like eval below will help. For example, given the eval package you could code something like the following. Testit is simply a boolean function that takes upto 5 inputs. It's there just for the exmaple. Eval is a package with a function 'func'. 'Func' takes as input a function to execute and optionally variables to bind with the function....

so, while you can't pass in a pointer to a function, you can dynamically interpret code at run time....

create or replace function testit( a in number := 0, 
                                   b in number := 0, 
                                   c in number := 0, 
                                   d in number := 0, 
                                   e in number := 0 ) return boolean

is
begin

    return a+b+c+d+e < 10;
end;
/

begin

    if ( eval.func( 'testit' ) ) then

        dbms_output.put_line( 'Is True' );     else

        dbms_output.put_line( 'Is False' );     end if;
end;
/
begin

    if ( eval.func( 'testit( a=>:a )', 'a', 9 ) ) then

        dbms_output.put_line( 'Is True' );     else

        dbms_output.put_line( 'Is False' );     end if;
end;
/
begin

    if ( eval.func( 'testit( a=>:a, c=>:c, e=>:e )',

                            'a', 9,
                            'c', -5, 
                            'e', 10 ) ) 
    then
        dbms_output.put_line( 'Is True' );
    else
        dbms_output.put_line( 'Is False' );

    end if;
end;
/

create or replace package eval
as

    g_bool boolean;

    function func

    ( p_function in varchar2,
      p_var1     in varchar2 default NULL, p_val1 in varchar2 default NULL,
      p_var2     in varchar2 default NULL, p_val2 in varchar2 default NULL,
      p_var3     in varchar2 default NULL, p_val3 in varchar2 default NULL,
      p_var4     in varchar2 default NULL, p_val4 in varchar2 default NULL,
      p_var5     in varchar2 default NULL, p_val5 in varchar2 default NULL)

    return boolean;

end eval;
/

create or replace package body eval
as

procedure bv( c in integer, n in varchar2, v in varchar2 ) is
begin

    if ( n is NOT NULL ) then

        dbms_sql.bind_variable( c, n, v );     end if;
end bv;

function func

( p_function in varchar2,
  p_var1     in varchar2 default NULL, p_val1 in varchar2 default NULL,
  p_var2     in varchar2 default NULL, p_val2 in varchar2 default NULL,
  p_var3     in varchar2 default NULL, p_val3 in varchar2 default NULL,
  p_var4     in varchar2 default NULL, p_val4 in varchar2 default NULL,
  p_var5     in varchar2 default NULL, p_val5 in varchar2 default NULL)

return boolean
is

    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
    boolean_var boolean;
begin

    dbms_sql.parse(exec_cursor,

                  'begin eval.g_bool := ' || p_function || '; end;',
                   dbms_sql.native );

    bv( exec_cursor, p_var1, p_val1 );
    bv( exec_cursor, p_var2, p_val2 );
    bv( exec_cursor, p_var3, p_val3 );

    bv( exec_cursor, p_var4, p_val4 );
    bv( exec_cursor, p_var5, p_val5 );

    rows_processed := dbms_sql.execute(exec_cursor);     dbms_sql.close_cursor( exec_cursor );     return g_bool;
exception

    when others then

      if dbms_sql.is_open(exec_cursor) then
        dbms_sql.close_cursor(exec_cursor);
      end if;
      raise;

end func;

end eval;
/

>
>Best regards
> Martin
>
>--
> Martin Schr"oder, MS_at_Dream.HB.North.DE
>They that can give up essential liberty to obtain a little temporary
>safety deserve neither liberty nor safety. (Benjamin Franklin)

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 Thu May 08 1997 - 00:00:00 CDT