Dynamic argument binding in dynamic stored procedure call oracle

Question

I have requirement not only to call a stored procedure dynamically but also to pass the parameters dynamically to that stored procedure.

Arguments are coming from the ALL_ARGUMENTS table which is Oracle data dictionary table. These arguments are going to vary based on rpt_wrapper_name.

For example sp1 will look like as below:

sp1 (v1, v2, v3)

Stored procedure sp2 will look like as below:

sp2 (v1, v2, v3, v4, v5)

Stored procedure sp3 will look like this:

sp3 (v1, v2, v3, v4, v5, v6, v7,...)

Currently I am trying a dynamic SQL call, but this is not working for me

EXECUTE IMEDIATE 'BEGIN ' || p_Rpt_wrapper_name|| '(' || p_ParamList || ') ; END;'
Using  p_ParamListUsing;

where  
p_ParamList := :v1,:v2,:v3
p_ParamListUsing:= v1,v2,v3

Error:

Not All variables as are bound ORA-01008

Parameter list is going to vary based on rpt_wrapper_name

How we can I achieve this? Please help

Try something like this: execute immediate 'begin '|| p_Rpt_wrapper_name||'('||v1||','||v2||....||'); end;'. — gsalem, Mar 04 '20 at 19:22
@gsalem : you mean not to use using clause ?static argument pass is working for me but i want to have dynamic argument pass.. — Nilesh Kadam, Mar 04 '20 at 19:27
I can't see how dynamic passing like you're targeting will work. — gsalem, Mar 05 '20 at 08:28

score 0 · Answer 1 · answered Mar 05 '20 at 13:05

0

Method 1:

Change the procedures so that they take a table of arguments.

type r_para is record(
    parameter_name   varchar2(32),
    parameter_vc     varchar2(32000),
    parameter_nb     number,
    parameter_dt     date);
type t_para is table of r_para;

procedure p1(p_parameters t_para);

And call

v_para_list.append( r_para ('ref_time',null,null,sysdate);
execute immediate 'proc2(:pin_para)' using v_para_list;

Method 2:

Use dynamic sql https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/dynamic-sql.html#GUID-1D6A4302-CBA0-47A6-B4A7-80B089DF4E61

c := DBMS_SQL.OPEN_CURSOR(true);
DBMS_SQL.PARSE(c, 'BEGIN get_employee_info(:id); END;', DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(c, ':id', 176);
n := DBMS_SQL.EXECUTE(c);

answered Mar 05 '20 at 13:05

Thomas Strub

1,275
7
20

@ Thomas : I didn't get Method 1. Could you please elaborate more on this? – Nilesh Kadam Mar 06 '20 at 04:23
Where can I help you? How to create an pl/sql table? How to fill the table? – Thomas Strub Mar 06 '20 at 07:46
@ Thomas : Any sample working code will help me to understand execution flow, calling & parsing – Nilesh Kadam Mar 06 '20 at 10:43

Dynamic argument binding in dynamic stored procedure call oracle

1 Answers1