CODEWITHSUNDEEP
plsqloracle11g
Messady
Messady

Reputation: 45

A generic procedure that can execute any procedure/function

input
Package name (IN)
procedure name (or function name) (IN)
A table indexed by integer, it will contain values that will be used to execute the procedure (IN/OUT).

E.g
let's assume that we want to execute the procedure below

utils.get_emp_num(emp_name IN VARCHAR
                  emp_last_name IN VARCHAR
                  emp_num OUT NUMBER
                  result  OUT VARCHAR);

The procedure that we will create will have as inputs:

package_name = utils
procedure_name = get_emp_num
table = T[1] -> name
        T[2] -> lastname
        T[3] -> 0   (any value) 
        T[4] -> N   (any value)

run_procedure(package_name,
              procedure_name,
              table)

The main procedure should return the same table that has been set in the input, but with the execution result of the procedure 

table =  T[1] -> name
         T[2] -> lastname
         T[3] -> 78734 (new value)
         T[4] -> F     (new value)

any thought ?

Upvotes: 1

Views: 636

Answers (2)

Messady
Messady

Reputation: 45

Get from the all_arguments table the argument_name, data_type, in_out, and the position

Build the PLSQL block

DECLARE
    loop over argument_name and create the declare section
    argument_name data_type if in_out <> OUT then := VALUE OF THE INPUT otherwise NULL
BEGIN

--In the case of function create an additional argument 
function_var:= package_name.procedure_name( loop over argument_name);

--use a table of any_data, declare it as global in the package

if function then
    package_name.ad_table.EXTEND;
    package_name.ad_table(package_name.ad_table.LAST):= function_var;
end if

--loop over argument_name IF IN_OUT <> IN
package_name.ad_table.EXTEND;
package_name.ad_table(package_name.ad_table.LAST):= 

if data_type = VARCHAR2 then := ConvertVarchar2(argument_name)
else if NUMBER then ConvertNumber 
else if DATE then ConvertDate
...

END;

The result is stored in the table. To get value use Access* functions

Upvotes: 0

Codo
Codo

Reputation: 78815

You can achieve it with EXECUTE IMMEDIATE. Basically, you build a SQL statement of the following form:

sql := 'BEGIN utils.get_emp_num(:1, :2, :3, :4); END;';

Then you execute it:

EXECUTE IMMEDIATE sql USING t(1), t(2), OUT t(3), OUT t(4);

Now here comes the tricky part: For each number of parameters and IN/OUT combinations you need a separate EXECUTE IMMEDIATE statement. And to figure out the number of parameters and their direction, you need to query the ALL_ARGUMENTS table first.

You might be able to simplify it by passing the whole table as a bind argument instead of a separate bind argument for each table element. But I haven't quite figured out how you would do that.

And the next thing you should consider: the elements of the table T your using will have a type: VARCHAR, NUMBER etc. So the current mixture where you have both numbers and strings won't work.

BTW: Why do you want such a dynamic call mechanism anyway?

Upvotes: 1

Related Questions