CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Gatsby
Gatsby

Reputation: 375

How to execute a stored procedure with parameters from a variable?

I have a variable which contains a name of a stored procedure. I want to execute this procedure with dynamic sql, so I did this :

var_procedure is the variable which contain the name of my stored procedure. firstparameter and secondone are my varchar parameters for the stored procedure.

execute immediate var_procedure||'('''||firstparameter||''','''||secondone||''')';

It's not working and I have this basic error printed : 

ORA-00900: invalid SQL statement

Do you have a solution ?

Upvotes: 2

Views: 14444

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21063

Let try it step by step.

This is your procedure.

create or replace procedure my_proc (a varchar2, b varchar2)
as
begin
  dbms_output.put_line ('a= '||a||' b = '||b);
end;
/

This is the way how to call it directly in PL/SQL 

begin 
   my_proc('x','y');
end; 
/

This is the Wrong Way how to execute is dynamically. Why? Concatenating parameters in the statement is a bad practice enabling SQL injection.

declare
a varchar2(5) := 'a';
b varchar2(5) := 'b';
begin
  execute immediate 'begin 
   my_proc('''||a||''','''||b||''');
end;';
end;
/

This is the right way using bind variables:

declare
a varchar2(5) := 'a';
b varchar2(5) := 'b';
begin
  execute immediate 'begin 
   my_proc(:a,:b);
end;' USING a,b;
end;
/

To pass the name of the procedure dynamically you have to concatenate the statement, so take some care to avoid SQL Injection if the parameter is not under your control (minimum is to limit the length to 30 characters).

declare
proc_name varchar2(30)  := 'my_proc';
a varchar2(5) := 'a';
b varchar2(5) := 'b';
begin
  execute immediate 'begin '||
   proc_name||'(:a,:b);
end;' USING a,b;
end;
/

Upvotes: 6

Related Questions