-
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
-
Because you have to use either 9i or dynamic SQL.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Using 8.1.7
Thanks Julian and Jmodic.
-
ssame problem with 10g
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>
-
NVL2 is an SQL function, not a PL/SQL function.
I think coalesce is another (and there may be a few more).
-
plus you are using 9i not 10g
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|