Oracle : sql select query if condition parameter is null then ignore the parameter

Question

suppose I have a oracle query

SELECT *
FROM EMPLOYEE
WHERE DEPARTMENT = ?
    AND DESIGNATION = ?
    AND DISTRICT = ?
    AND CIRCLE = ?

And quite possible any 1 or 2 or 3 of the parameter (?) can be empty or null.
so what should I do so that the empty parameters are totally "ignore" in the where clause and only search for the non-empty parameter in the table.
How can I achieve this

Please help.. The query must be compatible oracle 10g. Thanks

Answer 1

You can rewrite query like:

select * 
  from EMPLOYEE 
  where (DEPARTMENT  = p1 or p1 is null)  
    and (DESIGNATION = p2 or p2 is null) 
    and (DISTRICT    = p3 or p3 is null)
    and (CIRCLE      = p4 or p4 is null)

or:

select * 
  from EMPLOYEE 
  where DEPARTMENT  = nvl(p1, department)
    and DESIGNATION = nvl(p2, designation)
    and DISTRICT    = nvl(p3, district)
    and CIRCLE      = nvl(p4, circle)

As @mathguy mentioned in comments second version will not show null values. Please use first version.

Answer 2

NVL will be your friend here.

This function takes two input parameters and returns either the first one, or the second one if the first one is NULL.

This would work :

SELECT *
FROM EMPLOYEE
WHERE DEPARTMENT = NVL(yourParam1,DEPARTMENT)
    AND DESIGNATION = NVL(yourParam2,DESIGNATION )
    AND DISTRICT = NVL(yourParam3,DISTRICT )
    AND CIRCLE = NVL(yourParam4,CIRCLE )