奇怪的NVL2问题，提示NOT DECLARE

今天碰到一个同事问的NVL2函数使用的问题，简单记录一下（中间那会感觉不会这样，郁闷了好一会儿，有同样情况的同学要注意了），现象如下：

SQL> Create Or Replace Function fn_tnvl2(fv_info Varchar2) Return Varchar2 Is  2    lv_msginfo Varchar2(2000);  3  Begin  4    lv_msginfo := Nvl2(fv_info, fv_info||':', '')||To_Char(Sysdate, 'yyyy-mm-dd hh24:mi:ss');  5    Return lv_msginfo;  6  End;  7  /Warning: Function created with compilation errorsSQL> show err;Errors for FUNCTION OSS03.FN_TNVL2:LINE/COL ERROR-------- ---------------------------------------------4/17     PLS-00201: identifier 'NVL2' must be declared4/3      PL/SQL: Statement ignoredSQL> SQL> Create Or Replace procedure p_tnvl2 Is  2  Begin  3    dbms_output.put_line(Nvl2(Null, 2, 1));  4  End;  5  /Warning: Procedure created with compilation errorsSQL> show err;Errors for PROCEDURE OSS03.P_TNVL2:LINE/COL ERROR-------- ---------------------------------------------3/24     PLS-00201: identifier 'NVL2' must be declared3/3      PL/SQL: Statement ignoredSQL> SQL> Create Or Replace procedure p_tnvl2_v2 Is  2    v_info Varchar2(20);  3  Begin  4    Select Nvl2(Null, 'Not NULL', 'NULL') Into v_info From dual; --正常  5    dbms_output.put_line(v_info);  6  End;  7  /Procedure created

可以发现一个规律，就是NVL2函数在表达式上使用的时候，会提示NVL2函数没有被定义，但是NVL没有这个问题，NVL2只能在SQL语句上面是没有问题的，但是官方文档上也没有明确说明，有点坑啊。

Description of the illustration nvl2.gif

Purpose

NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2returns expr3.

The argument expr1 can have any data type. The arguments expr2 and expr3 can have any data types except LONG.

If the data types of expr2 and expr3 are different, then Oracle Database implicitly converts one to the other. If they cannot be converted implicitly, then the database returns an error. Ifexpr2 is character or numeric data, then the implicit conversion is implemented as follows:

• If expr2 is character data, then Oracle Database converts expr3 to the data type of expr2 before returning a value unless expr3 is a null constant. In that case, a data type conversion is not necessary, and the database returns VARCHAR2 in the character set of expr2.

• If expr2 is numeric data, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.