NVL2 in PL/SQL

[skid]

Why does NVL2 does not work in PL/SQL? Got this error:

ERROR at line 1:
ORA-06550: line 4, column 13:
PLS-00201: identifier 'NVL2' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

[julian]

Because you have to use either 9i or dynamic SQL.

[jmodic]

You should specify your Oracle version. I assume you are using 8i, where NVL2 (like some other newer functions) does not work inside PLSQL. The reason is a separate SQL parser that's built into PL/SQL, and this one does not yet support all the features available in SQL. If you want to use NVL2 in 8i PL/SQL you should use dyinamic SQL (DBMS_SQL or 'EXECUTE IMMEDIATE').

In 9i there is only one SQL parser for both SQL and PL/SQL, so NVL2 works also from inside PL/SQL.

[skid]

Thanks Julian and Jmodic.

[culonbu]

I 'm using 10g but have same problem:


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 23 15:43:16 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL> l
1 CREATE OR REPLACE FUNCTION ORACLE_NVL2 ( VALUE1 VARCHAR2,NOT_NULL_RET VARCHAR2,NULL_RET VARCHAR
2 AS
3 L_RET VARCHAR2(4000);
4 BEGIN
5 L_RET := NVL2(VALUE1, NOT_NULL_RET,NULL_RET);
6 RETURN L_RET;
7 END;
8*
SQL> /

Warning: Function created with compilation errors.

SQL> show errors
Errors for FUNCTION ORACLE_NVL2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5 PL/SQL: Statement ignored
5/14 PLS-00201: identifier 'NVL2' must be declared
SQL>

[gamyers]

NVL2 is an SQL function, not a PL/SQL function.
I think coalesce is another (and there may be a few more).

[davey23uk]

plus you are using 9i not 10g