Oracle - Handle empty resultset on IF THEN ELSE statement

Question

which should be the IF condition when the variable assignation results from an empty resultset?

Example:

CREATE OR REPLACE Function get_values
     ( chv_input IN varchar2 )
     RETURN varchar2
IS
     chv_output varchar2(100);

  BEGIN

select 'value'
    into chv_output
from dual where 1=2; 

IF chv_output is null THEN --this condition is not working

    chv_output := 'null';

ELSE
     chv_output := 'not null';

END IF;

  RETURN chv_output;

END;

--select 1, get_values('112') from dual

Klas Lindbäck · Accepted Answer · 2011-07-15 14:34:49Z

2

Try this instead:

EXCEPTION
WHEN NO_DATA_FOUND
  chv_output := 'null';

answered Jul 15, 2011 at 14:34

Klas Lindbäck

33.4k5 gold badges61 silver badges85 bronze badges

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

1 Comment

MaLo Over a year ago

great. I used just after the select. Like this: select 'value' into chv_output from dual where 1=2; EXCEPTION WHEN NO_DATA_FOUND THEN NULL;

Collectives™ on Stack Overflow

Oracle - Handle empty resultset on IF THEN ELSE statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related