Reuse bind variables in the dynamic SQL

• 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