NVL2 in PL/SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: NVL2 in PL/SQL

  1. #1
    Join Date
    Apr 2002
    Location
    Philippines
    Posts
    77
    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


  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Because you have to use either 9i or dynamic SQL.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Philippines
    Posts
    77

    Using 8.1.7

    Thanks Julian and Jmodic.

  5. #5
    Join Date
    Dec 2000
    Posts
    126

    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>

  6. #6
    Join Date
    Feb 2005
    Posts
    158
    NVL2 is an SQL function, not a PL/SQL function.
    I think coalesce is another (and there may be a few more).

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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
  •  



Click Here to Expand Forum to Full Width