check if a value exists in array of constants in oracle

Question

I have some predefined values like

declare 
v_type varchar2(20);
begin
    select type into v_type from GenericTypes where id =5 ;
   // say v_type = 'XYZ'

end;

Now, i have to check if v_type is equal to 'ABC' or 'DEF' or 'ASD' and 5 more items.

so i used: if v_type = 'ABC' or v_type ='DEF' or v_type='ASD' and 5 more conditions

If there is any more efficient method to do this like using select exist etc.

Chrisrs2292 · Accepted Answer · 2015-05-21 09:49:23Z

3

You should be able to use the IN clause for example:

if v_type IN ('ABC','DEF','ASD') then
  do things;
end if;

answered May 21, 2015 at 9:49

Chrisrs2292

1,1041 gold badge12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

donstack Over a year ago

much more simple than expected. feeling like dumb

Lalit Kumar B · Accepted Answer · 2015-05-21 10:09:14Z

1

You could use CASE expression and check the condition in the query itself.

For example,

DECLARE
  v_type NUMBER;
BEGIN
  SELECT
    CASE
      WHEN TYPE IN ('ABC','DEF','ASD')
      THEN 1
      ELSE 0
    END GenericTypes
  INTO v_type
  WHERE ID  =5;
  IF v_type = 1 THEN
    -- do somethins
  END IF;
END;
/

answered May 21, 2015 at 10:09

Lalit Kumar B

49.5k13 gold badges105 silver badges132 bronze badges

Collectives™ on Stack Overflow

check if a value exists in array of constants in oracle

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related