Reuse bind variables in the dynamic SQL
Jump to navigation
Jump to search
- The bind variables are bound by POSITION, not by variable name.
- Sample - it doesn't matter what the variable names are, all bind variables must be bound in order.
v_query := 'select job from emp where ename = :1 and sal > :500 and ename = :7'; execute immediate v_query into v_job using p_ename, p_sal, p_ename;
- Use EXECUTE IMMEDIATE with a WITH clause
v_query := 'with p as (select :1 p1, :2 p2 from dual) '; v_query := v_query || 'select job from emp, p where ename = p.p1 and sal > p.p2 and ename = p.p1'; execute immediate v_query into v_job using p_ename, p_sal;
- Use DBMS_SQL instead of EXECUTE IMMEDIATE
Reference: http://stackoverflow.com/questions/11660564/re-using-bind-variables-in-oracle-pl-sql