PLS-00201: identifier 'NVL2' must be declared

Having just tried to use NVL2 inside PLSQL I have discovered it isn't there! This is one of those functions that only exist for SQL not PLSQL. Like DECODE.

NVL2 is quite simple and easily rewritten with IF or CASE statements but these can sometimes make code messy. NVL2 in SQL can just be concatinated within a string and is one command. (Not always a good reason for using it but in some cases...)

Anyway, to get around the problem in these few circumstances, you can create your own function.

CREATE OR REPLACE FUNCTION NVL2 ( p_value IN VARCHAR2, p_newValueIfNotNull IN VARCHAR2, p_newValueIfNull IN VARCHAR2 ) RETURN VARCHAR2 DETERMINISTIC IS BEGIN IF p_value IS NOT NULL THEN RETURN(p_newValueIfNotNull); ELSE RETURN(p_newValueIfNull); END IF; END; /

The function will be used when called from PLSQL, the built-in will be used when called from SQL.

Sorted