I have a dynamic query , even it done using bind variables , since it
is dynamic every time the condition keeps changing and the query
parses , loading the db.
How to make this as a Generic Query to aviod parsing
Show us the query. While reducing the amount of unnecessary parsing is a good thing, you can take it too far. For example, sometimes users search for employees by name, sometimes by department, and sometimes by employee number:
select * from emp where ename = :n;
select * from emp where deptno = :d;
select * from emp where empno = :e;
You could create a generic query to answer all 3 questions:
select * from emp
where ename = NVL(:n,ename)
and deptno = NVL(:d,deptno)
and empno = NVL(:e,empno);
This means you will only parse once rather than 3 times, but the much bigger downside is that all your queries will be slower and use more resources. The cost of this will far outweigh the cost of the 2 extra parses if the queries are used a few times.
If its dynamic but you are using bind variables for all the criteria which the user can change then that is the best you can do. Is the parsing really the problem or is it that the query runs inefficiently?
I haven't looked hard at the details, but that looks just fine as a concept - it is something I often do myself. You have a search screen where the user can choose from lots of possible criteria. Rather than have a single query that tries to handle everything, with an inevitable full table scan regardless of how selective the criteria given are, you build the most appropriate query for the criteria given.
The number of different queries generated can be fairly high, but it is finite - and they really are different queries. This is not like failing to use bind variables, in which case the number of different queries is effectively infinite.
Oracle's own client software like Oracle Forms builds queries dynamically, just like this.