oracle - Dynamic binding for dynamic query on Native Dynamic SQL -

i find myself in situation code needs execute dynamic statement formed unknown number (at least one, more) of other dynamic statements concatenated 'intersect' , 'union' operators.

here's example 3 querys (i know resolved 1 query, i'm trying keep simple):

sql1 varchar2(500) := 'select empno emp deptno = :1'; sql2 varchar2(500) := 'select empno emp sal > :2 , hiredate >=:3';  sql3 varchar2(500) := 'select empno emp sal <= :2 , hiredate =:3' realstatement varchar(1500) := sql1 || ' insersect ' || sql2 || ' union ' sql3; 

now, given number of sub-statements it's unknown @ run time, binding variables values known (i.e. deptno, sal , hiredate :1, :2 , :3 respectively). can't use 'execute immediate realstatement using' form because it's bindings positional and, example, should pass sal , hiredate parameters twice, resulting in statement:

execute immediate realstatement using l_deptno,l_sal,l_hiredate,l_sal,l_hiredate; 

which there's no way know beforehand include repetitions every sub-statement.

i know use dbms_sql package bind() function performance it's 1.5 3 times worst native dynamic (from oracle docs), , in case performance relevant.

so i'm doing replacing ':1' occurrences l_deptno, ':2' occurrences l_sal, , ':3' occurrences 'to_date(''' || l_hiredate || ''',''dd/mm/yyyy'')' in realstatement string before executing this:

realstatement := replace(realstatement,':1',l_deptno); realstatement := replace(realstatement,':2',l_sal); realstatement := replace(realstatement,':3','to_date(''' || l_hiredate || ''',''dd/mm/yyyy'')'); execute immediate realstatement; 

but i'm not sure best solution, questions:

1. is there way improve performance or pass bindings dynamically using native dynamic sql?

2. will use of dbsm_sql package result in better performance of chosen solution?

rewrite statements:

sql1 varchar2(500) := 'select empno emp deptno = :1'; sql2 varchar2(500) := 'select empno emp sal > :2 , hiredate >=:3';  sql3 varchar2(500) := 'select empno emp sal <= :2 , hiredate =:3' realstatement varchar(1500) := sql1 || ' insersect ' || sql2 || ' union ' sql3; 

to utilise with clause follows:

sql0 varchar2(500) := 'with par (select :1 p1, :2 p2, :3 p3 dual)'; sql1 varchar2(500) := '(select empno emp join par deptno = par.p1)'; sql2 varchar2(500) := '(select empno emp join par sal > par.p2 , hiredate >=par.p3)';  sql3 varchar2(500) := '(select empno emp join par sal <= par.p2 , hiredate = par.p3)'; realstatement varchar(2000) := sql0 || ', sql1 ' || sql1 || ', sql2 ' || sql2 || ', sql3 ' || sql3 || ' select * sql1 intersect select * sql2 union select * sql3'; 

or (when no reuse of subqueries):

sql0 varchar2(500) := 'with par (select :1 p1, :2 p2, :3 p3 dual)'; sql1 varchar2(500) := 'select empno emp join par deptno = par.p1'; sql2 varchar2(500) := 'select empno emp join par sal > par.p2 , hiredate >=par.p3';  sql3 varchar2(500) := 'select empno emp join par sal <= par.p2 , hiredate = par.p3'; realstatement varchar(2000) := sql0 || ' ' || sql1 || ' intersect ' || sql2 || ' union ' || sql3; 

then execute using 3 bind variables: execute immediate realstatement using l_deptno,l_sal,l_hiredate