Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PLS-00201: identifier 'NVL2' must be declared

UtsavSep 1 2015 — edited Sep 1 2015

I came across a scenario where I've seen that NVL2 is a PL/SQL function but when I tried to use this function directly with plsql variable it gives me error. Although it is working find within SQL.

Below is the sample I am posting its strange behaviour that I am seeing.

I am under banner PL/SQL Release 11.2.0.4.0 - Production

SQL> --See when we use NVL2 within SQL it works fine

SQL> select nvl2('IamNotNull', 'IamForNotNull', 'IamForNull') for_not_null

  2            ,nvl2(null, 'IamForNotNull', 'IamForNull') for_null

  3  from dual;

FOR_NOT_NULL  FOR_NULL

------------- ----------

IamForNotNull IamForNull

SQL> --Problem occurs when we assign the resultant directly into variable in anonymous block

SQL> declare

  2      for_not_null varchar2(200);

  3  begin

  4      for_not_null :=NVL2('IamNotNull', 'IamForNotNull', 'IamForNull');

  5  end;

  6  /

    for_not_null :=NVL2('IamNotNull', 'IamForNotNull', 'IamForNull');

                   *

ERROR at line 4:

ORA-06550: line 4, column 20:

PLS-00201: identifier 'NVL2' must be declared

ORA-06550: line 4, column 5:

PL/SQL: Statement ignored

SQL>

This post has been answered by BluShadow on Sep 1 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 29 2015
Added on Sep 1 2015
7 comments
5,029 views