Oracle PL/SQL - Passing several bind variables

Introduction

You can pass more than one bind variable as by using the following syntax:

execute immediate
    '...:var1,:var2,:var3...' (code with bind variables)
using
    v_A,v_B,'Y'...(comma-separated list of values/variables)

drop table emp;
create table emp(
  empno    number(4,0),
  ename    varchar2(10),
  job      varchar2(9),
  mgr      number(4,0),
  hiredate date,
  sal      number(7,2),
  comm     number(7,2),
  deptno   number(2,0)
);

insert into emp values(7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
insert into emp values(7698, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);


create or replace function f_getMax
     (i_empNo1 NUMBER,i_empNo2 NUMBER,i_column_tx VARCHAR2)
return VARCHAR2
is
     v_out_tx VARCHAR2(2000);
    v_sql_tx VARCHAR2(2000);

begin
      v_sql_tx :=
          'select max('||i_column_tx||')'||                                  --9
          ' from emp ' ||
          'where empNo in (:var01, :var02)';                               --11
      execute immediate v_sql_tx
      into v_out_tx
      using i_empNo1, i_empNo2;                                            --14
      return v_out_tx;
exception
      when others then return null;
end f_getMax;

Oracle PL/SQL - Passing several bind variables

Introduction

Related Topic