CREATE TABLE OT_JOB_STAT ( JOB_NO VARCHAR2(12),JOB_STATUS VARCHAR2(12),JOB_EQUIP VARCHAR2(12),JOB_TYPE VARCHAR2(12))

insert into ot_job_stat ( JOB_NO ,JOB_STATUS ,JOB_EQUIP ,JOB_TYPE ) values ('0001','Open','CNC1','Prev')

insert into ot_job_stat ( JOB_NO ,JOB_STATUS ,JOB_EQUIP ,JOB_TYPE ) values ('0002','Close','CNC2','Brk')

select * from ot_job_stat where nvl(job_status) in ('Open') or nvl(job_status)='All' --All should bring both the records.

select * from ot_job_stat where job_status = :param or :param is null;

CREATE TABLE OT_JOB_STAT ( JOB_NO VARCHAR2(12),JOB_STATUS VARCHAR2(12),JOB_EQUIP VARCHAR2(12),JOB_TYPE VARCHAR2(12),JOB_DATE DATE);

insert into ot_job_stat ( JOB_NO ,JOB_STATUS ,JOB_EQUIP ,JOB_TYPE,JOB_DATE ) values ('0001','Open','CNC1','Prev',TO_DATE('01/01/2014'));

insert into ot_job_stat ( JOB_NO ,JOB_STATUS ,JOB_EQUIP ,JOB_TYPE , JOB_DATE ) values ('0002','Close','CNC1','Brk',TO_DATE('01/01/2014'));

insert into ot_job_stat ( JOB_NO ,JOB_STATUS ,JOB_EQUIP ,JOB_TYPE , JOB_DATE ) values ('0003','Close','CNC2','Prev',TO_DATE('02/02/2014'));

select * from ot_job_stat
WHERE
   JOB_DATE between TO_DATE(:F_JOB_DT,'DD/MM/RRRR') AND  TO_DATE(:T_JOB_DT,'DD/MM/RRRR') 
  and job_type in :job_type or :job_type is null   


--I am passing job_type as null and :F_JOB_DT = '01/01/2014' AND :T_JOB_DT = '01/01/2014'

--its getting me all the three records , but actually i dont want the record of 02/02/2014.

--result is


JOB_NO	JOB_STATUS	JOB_EQUIP	JOB_TYPE	JOB_DATE
0002	Close	          CNC1	           Brk	        1/1/2014
0003	Close	          CNC2	          Prev	        2/2/2014
0001	Open	          CNC1	          Prev	        1/1/2014

and ( job_type in :job_type or :job_type is null )