Oracle nvl2 not working in stored procedure PLS-00201: identifier 'NVL2' must be declared

Question

As title, I'm writing some Stored Procedure on Oracle, first I checked the version

SELECT * FROM v$version;

with result

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0  Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

...

And, tried NVL2 with regular SQL query

select 'Test: ' || nvl2('...', 'things', 'nothing') from dual;
select 'Test: ' || nvl2('', 'things', 'nothing') from dual;

result seems right

Test: things
Test: nothing

...

So I confirmed 11g did support NLV2 function, I can now begin to write my stored procedure, like following :

create or replace procedure my_schema.SP_READ_MEMBER(noP in varchar2, nameP in varchar2, idNoP in varchar2, birthdayP in varchar2, resultP out sys_refcursor)
is
v_prg_name varchar2(20) := 'SP_READ_MEMBER';
sys_sql    varchar2(1000);

begin
  Insertlog(SYSDATE, v_prg_name, '1.0 Start');
  sys_sql :=  sys_sql || 'select a.no, a.name, a.id_no, to_char(a.birthday, ''yyyy/MM/dd'') as birthday, ''REGISTERED'' as type, email, mobile from rep where 1=1 ';
  sys_sql :=  sys_sql || nvl2(noP,'and no='''|| noP ||'''', ''); --PLS-00201

  open resultP for sys_sql;
  Insertlog(SYSDATE, v_prg_name, '2.0 Finished w/o error');

  exception
  when others then
    declare
      error_time VARCHAR2(30) := RTRIM(TO_CHAR(SYSDATE, 'YYYY/MM/DD, HH24:MI:SS'));
      error_code NUMBER := SQLCODE;
      error_msg  VARCHAR2(300) := SQLERRM;
    begin
      rollback;
      DBMS_OUTPUT.PUT_LINE(error_time || ',' || TO_CHAR(error_code) || ',' || error_msg);
      Insertlog(SYSDATE, v_prg_name,  error_msg || ', 3.0 ERROR, sql:' || sys_sql);
    end;
end;
/

Oracle told me it compiled with error, which is

PLS-00201: identifier 'NVL2' must be declared

How come a function works in regular query, but become undeclared in Stored Procedure ?

Cowrie

You have an interesting use case. I'm pretty sure nvl2 has not been mapped as a PL/SQL function. Try coalesce. Haven't looked at MOS yet, but I consider a lack of that mapping to be a bug. But if it's not going to be fixed, then it's probably because coalesce is a much better name than nvl2. – Cates

NVL2 doesn't work in 12.2.0.1 either (so that you'd know that database upgrade won't fix that problem). – Enumerate

Not all SQL functions are supported in PL/SQL. decode is another. – Unquestionable

Answer 1

NVL2 works in SQL but not PL/SQL unless you are on version 21c or higher.

Not every SQL keyword also works in PL/SQL, which is silly, but it's happened a few times before. Oracle has been gradually unifying SQL and PL/SQL, and chances are they'll get to this function eventually. For now, I think this issue is covered by the My Oracle Support document Note 359506.1 Pls-00201 Assigning The Result of NVL2() To A Variable In a PLSQL Block. Although that document is not available, even to people with a support access.

For now, I recommend using a different syntax. Personally, I find the CASE version to be clearer, even if it is a bit wordier.

Change:

sys_sql :=  sys_sql || nvl2(noP,'and no='''|| noP ||'''', '');

To:

sys_sql :=  sys_sql || case when nop is not null then 'and no='''|| noP ||'''' else '' end;

Wyndham

So it was Oracle 11g's PL/SQL support limitation... I finally understand. Also the case when solution helps, thank you~ – Cowrie

Answer 2

NVL2 works in SQL but not PL/SQL.

Change:

sys_sql :=  sys_sql || nvl2(noP,'and no='''|| noP ||'''', '');

To:

select sys_sql || nvl2(noP,'and no='''|| noP ||'''', '') into sys_sql from dual;

Ukulele