Re: Executing stored procedures in dynamic SQL

Dolf-Jan Mulder wrote:

> Hello,
> I want to execute procedures in dynamic SQL. The name of the procedures are
> stored in my application database. Depending on the situation a want to
> execute one of them. It would be nice if I could execute the procedure in
> dynamic SQL and get the results back in my program. Is this possible? Does
> somebody have a sample?
> DJ

  What you want is the dbms_sql package. Feurstein describes how to use the package in his
books (rave recommendations!). You can also get his freeware which does something like what
you want.

The big trick is to parse, bind and execute the sql or pl/sql block that you construct dyamically.

I have a quick and dirty function whose purpose is to execute a function whose name is a variable.
(the functions take no arguments, but that shouldnt be any problem to add)

Here's the source

Function XEQFN ( pp_name IN varchar2) return varchar2 IS
-- XEQ:

• Executes the function named, returns the functions return value.
• pp_name contains the name of a FUNCTION to be executed.
• pp_name may be calculated at run time (or taken from a varchar2 field
• of a database. It need NOT be known at coding time.
  --
  
• The VALUE of xeqfn is the value of the function represented by the
• input argument.
  --
  --
  
• USAGE:
• lv_name := 'someFunction';
• lv_ret := xeqfn( lv_name);
• Equivalent to :
• lv_ret := someFunction;
• --------------------------------------------------------------
• 12jun98 LMH First cut
• -------------------------------------------------------------- retval varchar2(2000); fdbk varchar2(2000); cur integer;
  --
  BEGIN cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE( cur, 'BEGIN :val := '||pp_name||'; End; ', DBMS_SQL.V7); DBMS_SQL.BIND_VARIABLE(cur, 'val', 'a', 2000 ); fdbk := DBMS_SQL.EXECUTE(cur); DBMS_SQL.VARIABLE_VALUE(cur,'val',retval); return retval;

   EXCEPTION
    WHEN others THEN

• dbms_output.put_line('XEQ : '||substr(sqlerrm,1,200) ) ; return 'NG'; END; -- Function