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

On Wed, 7 May 1997, Thomas Kyte wrote:

> 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:
>
> SQL> l
> 1 declare
> 2 function foo( x in varchar2 ) return varchar2
> 3 is
> 4 begin
> 5 return upper(x);
> 6 end foo;
> 7 function bar( y in varchar2 ) return varchar2
> 8 is
> 9 begin
> 10 return initcap( y );
> 11 end bar;
> 12 begin
> 13 dbms_output.put_line( bar( foo( 'Hello!' ) ) );
> 14* end;
> SQL> /
> Hello!

This is not passing a function as argument this is passing a varchar2. In pl/sql there are no function pointers.

I beleive Brian Gastineau wanted to do something like this:

(using c-function pointers)

function test1(var in varchar2) /* test whether var = 'yes' */   return boolean
is
begin
  return var = 'yes';
end;

function test2(var in varchar2) /* test whether var = 'no' */   return boolean
is
begin
  return var = 'no';
end;

procedure test_it(var in varchar2, boolean (*test_func)(varchar2)) is
begin
  if test_func(var)
  then
/* do something */
  else
/* do something else*/
  end if;
end;

procedure main
is
begin
  test_it('yes', test1);
  test_it('yes', test2);
end;

It is not possible to do this in PL/SQL, you could (perhaps) do this using the dbms_sql package.

Lets say you want to test if a varhcar2 is equal to something.

A rewrite of the above

procedure test1(var in varchar2, res out varchar2) is
begin
  if var = 'yes'
  then
   res := 'Y';
  else
   res := 'N';
  end if;
end;  

procedure test2(var in varchar2, res out varchar2) is
begin
  if var = 'no'
  then
   res := 'Y';
  else
   res := 'N';
  end if;
end;

procedure test_it(var in varchar2, test_func in varchar2) is
  ign integer;
  crs integer;
  test varchar2(1);
  sqlstmt varchar2(2000);
begin
  sqlstmt := 'begin '||test_func||'(:var, :test); end;';   crs := dbms_sql.open_cursor;

  dbms_sql.parse(crs, sqlstmt, dbms_sql.native);
  dbms_sql.bind_variable(crs, 'var', :var);
  dbms_sql.bind_variable(crs, 'test', :test);

  ign := dbms_sql.execute(crs);
  dbms_sql.close_cursor(crs);

  if test = 'Y'
  then
/* do something */
  else
/* do something else*/
  end if;
end;

procedure main
is
begin
  test_it('yes', 'test1'); /* will do something */   test_it('yes', 'test2'); /* will do something else */ end;

I havn't tried this and I have never tried to execute pl/sql through the dbms_sql package, however, I don't see any particular reason why it would not work. You would probably want to encapsulate the dbms_sql stuff in a function returning a boolean. How effective this is I don't know, but it wouldn't surprise me if pl/sql it self does something like this internally when a procedure or function is called.

Tomas Andersson, tomas_at_comedia.se Received on Thu May 08 1997 - 00:00:00 CDT