select * from Table1 T1 where 1=1 and ( ((T1.COL1, T1.COL2) in (select TEXT1, TEXT2 FROM Table2) and :P_BATCH_TYPE = 'C') or :P_BATCH_TYPE != 'C')
So, you want the condition to be EITHER that :P_BATCH_TYPE is not 'C' (including the case when it's NULL), or else you want the IN condition to apply.
This is pretty much the condition in Mustafa's answer (Reply 1), except that you don't need the condition = 'C' in the first branch of the OR condition, and you must handle the case when the bind variable is NULL. The cleanest way to do that in Oracle is with LNNVL. https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions078.htm
The WHERE clause can be written like so:
where LNNVL(:P_BATCH_TYPE = 'C') or (T1.COL1, T2.COL2) in (select TEXT1, TEXT2 from TABLE2)
(obviously, there's no need for a 1 = 1 condition).
Note - syntax corrected, see Reply 5 below. Thank you Mustafa!
For very complex conditions, I'd do something like below:
from Table1 T1
WHEN :P_BATCH_TYPE = 'C' and
exists (select 1 from FROM Table2 t2 where T1.COL1=t2.text1 and T1.COL2=t2.text2) THEN 1
-- when ... --- other conditions if necessary
when :P_BATCH_TYPE = 'C' then 0