NVL vs NVL2 in PL/SQL used in varchar processing

Question

I want to use NVL2 function in string processing, e.g.

some_variable := nvl2 (other_variable, '.' || other_variable, '');

For this I receive error

PLS-00201: identifier 'NVL2' must be declared

Suprisingly, works:

some_variable := nvl (other_variable, '');

Is there any help except using if-then-end?

Thanks Jan

score 3 · Answer 1 · answered Mar 07 '19 at 10:10

3

As per this link nvl2 is available only for SQL and not for PLSQL

You can use like the below

select nvl2 (other_variable, '.' || other_variable, '') into some_variable from dual;

answered Mar 07 '19 at 10:10

psaraj12

4,772
2
21
30

Thanks. Yes, that is. I use PL/SQL as "another scripting language" with SQL knowledge and this was a surprise (with `nvl` working). – Jan Kohout Mar 07 '19 at 10:36

score 1 · Answer 2 · answered Mar 07 '19 at 11:17

You could also use a case expression directly in PL/SQL (which avoids the context switching to and from the SQL engine):

DECLARE
  v_chk VARCHAR2(1);
  v_not_null VARCHAR2(1) := 'B';
  v_null VARCHAR2(1) := 'C';
  v_res VARCHAR2(1);
BEGIN
  v_res := CASE WHEN v_chk IS NOT NULL THEN v_not_null
                ELSE v_null
           END;
  dbms_output.put_line('v_chk = "'||v_chk||'", v_res = "'||v_res||'"');

  v_chk := 'A';
  v_res := CASE WHEN v_chk IS NOT NULL THEN v_not_null
                ELSE v_null
           END;
  dbms_output.put_line('v_chk = "'||v_chk||'", v_res = "'||v_res||'"');
END;
/

v_chk = "", v_res = "C"
v_chk = "A", v_res = "B"

Yes, `case` is what I have finally used. – Jan Kohout Mar 08 '19 at 11:04 — Jan Kohout, Mar 08 '19 at 11:04

score 0 · Answer 3 · answered Mar 08 '19 at 02:27

I created a function so I can use nvl2 as if it existed in plsql, it might suit your purposes too

create or replace FUNCTION NVL2
( p_value                  IN VARCHAR2,
  p_newval_if_not_null     IN VARCHAR2,
  p_newval_if_null         IN VARCHAR2 ) RETURN VARCHAR2 DETERMINISTIC IS

BEGIN

  IF p_value IS NOT NULL THEN
    RETURN(p_newval_if_not_null);
  ELSE
    RETURN(p_newval_if_null);
  END IF;

END;

It can be then recreated to use different data types if required

NVL vs NVL2 in PL/SQL used in varchar processing

3 Answers3