Re: PL/SQL: Dynamically calling a function., Need help

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/04/16
Message-ID: <33542cf8.4243812@newshost>#1/1

On Wed, 16 Apr 1997 00:51:59 UNDEFINED, aj_at_knoware.nl (Allard Siemelink) wrote:

>
>Hi,
>We're currently working on a large project and need to call
>stored procedures and functions dynamically from PL/SQL.
>All functions have the same input and output parameters and
>return a number. e.g.:
>
>function ruleX (an_object_id in number,
> as_error_msg out varchar) return number
>begin
> --BODY
>end;
>
>
>Is it possible to call such a function dynamically? (Meaning
>retrieving the name of the function from a database column into
>a local varchar variable, compose the function call syntax
>'execute ruleX(...)' in a varchar variabele and then call it)
>And if it is, could some kind soul provide an example showing
>how to call such a function, passing the input parameters and
>retrieving the output parameters and return value?
>
>TIA,
>
>-Allard

something like the following package will work. You will want to investigate the use of bind variables in your real application as it will make much better use of shared sql. In the following you would be able to:

begin

   theRule := 'Myfunction( 5, 5, ''SomeString'' )';    someValue := value_of.someThing( theRule ); end;
/

If you would like a larger example of doing something similar but with bind variable support, goto http://govt.us.oracle.com/ and follow the quick pick to 'downloadable utilities'. From there, get the OWA extensions. One of the routines in there is an owa_sql.cells_from_query. This uses dbms_sql to dynamically prepare a sql select statement with a variable number of bind variables input into it...

create or replace package value_of
as

    theValue varchar2(2000);  

    function something( p_fieldname in varchar2 ) return varchar2; end;
/  

create or replace package body value_of
as  

function something( p_fieldname in varchar2 ) return varchar2 is

    l_theCursor     integer;
    l_columnValue   varchar2(2000);
    l_status        integer;

begin  

    l_theCursor := dbms_sql.open_cursor;  

    dbms_sql.parse( l_theCursor,

                    'begin value_of.theValue := ' || p_fieldname ||
                    ';end;',
                     dbms_sql.native );

    l_status := dbms_sql.execute(l_theCursor);     dbms_sql.close_cursor(l_theCursor);
    return theValue;
end something;  

end value_of;
/

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 Wed Apr 16 1997 - 00:00:00 CDT