Skip to Main Content
Forums

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
4,794 views