Hi tom, I have question regarding a scenario.
I have a table named test2

create table test2(
  id number(10),country varchar2(100)
);

BEGIN
  insert into test2 values(1,'INDIA');
  insert into test2 values(2,'INDIA');
  insert into test2 values(3,'INDIA');
  insert into test2 values(4,'INDIA');
  insert into test2 values(5,'INDIA');
  insert into test2 values(6,'INDIA');
  insert into test2 (ID)values(7);
  insert into test2 values(8,'US');
  insert into test2 values(9,'US');
  COMMIT;
END;
/

I have written a procedure.

create or replace procedure test3 (
  p_currency in varchar2 default null
) as
  v_currency varchar2 (100);
begin
  v_currency := upper (p_currency);
  select id
  into v_id
  from vinay_test2
  where country in
    case
      when upper (p_currency) in (
        'US',
        'INDIA'
      ) then
        upper (p_currency)
      when (upper (p_currency) is null)
           or (upper (p_currency) not in (
        'INDIA',
        'US'
      )) then
        in ('US','INDIA')
      else
        in ('US',india)
    end;

  dbms_output.put_line ('v_id' || v_id);
end;
/

My doubt is how I can use multiple values in single variable in where clause as I have shown above.

Suppose p_currrency is my user input. If input will be india or us then it will show desired result as per where clause.

but if input is null/other than india and us then I want result which contains both india and us record.

Please let me know how I can achieve this.

if input is us it

var curr varchar2(10);

select * from test2
where  (    
    country in ( 'US', 'INDIA' ) 
and ( :curr is null or :curr not in ( 'US', 'INDIA' ) )
) or (
  :curr = country
);

ID    COUNTRY   
    1 INDIA      
    2 INDIA      
    3 INDIA      
    4 INDIA      
    5 INDIA      
    6 INDIA      
    8 US         
    9 US 

exec :curr := 'UK';

select * from test2
where  (    
    country in ( 'US', 'INDIA' ) 
and ( :curr is null or :curr not in ( 'US', 'INDIA' ) )
) or (
  :curr = country
);

ID    COUNTRY   
    1 INDIA      
    2 INDIA      
    3 INDIA      
    4 INDIA      
    5 INDIA      
    6 INDIA      
    8 US         
    9 US 

exec :curr := 'INDIA';

select * from test2
where  (    
    country in ( 'US', 'INDIA' ) 
and ( :curr is null or :curr not in ( 'US', 'INDIA' ) )
) or (
  :curr = country
);

ID    COUNTRY   
    1 INDIA      
    2 INDIA      
    3 INDIA      
    4 INDIA      
    5 INDIA      
    6 INDIA