Reuse bind variables in the dynamic SQL


 * The bind variables are bound by POSITION, not by variable name.

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;
 * Sample - it doesn't matter what the variable names are, all bind variables must be bound in order.

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 EXECUTE IMMEDIATE with a WITH clause


 * Use DBMS_SQL instead of EXECUTE IMMEDIATE

Reference: http://stackoverflow.com/questions/11660564/re-using-bind-variables-in-oracle-pl-sql