nvl2() can not be used in a cursor?

From: Jochen Van den Bossche <jochen.vandenbossche_at_proximus.net>
Date: 30 Aug 2002 04:54:49 -0700
Message-ID: <2bbd972e.0208300354.4f51e05c@posting.google.com>

I tried to use nvl2 in the select statement of a cursor declaration in a procedure. It won't compile.
This is on "Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production"

spool of error reproduction:

---

SQL> set echo on
SQL> @nvl2_test.sql
SQL> create table test

  2 (col1 number,
  3 col2 varchar2(50));

Table created.

SQL>
SQL> insert into test (col1) values (1);

1 row created.

SQL> insert into test (col1,col2) values (2,'The other record is null');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select col1, col2, nvl(col2,'{NULL}') nvl_func from test;

      COL1 COL2

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

NVL_FUNC

---

         1
{NULL}
         2 The other record is null
The other record is null

SQL> select col1, col2, nvl2(col2,'NOT NULL: '||col2,'{NULL}') nvl2_func from test;

      COL1 COL2

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

NVL2_FUNC

---

         1
{NULL}
         2 The other record is null
NOT NULL: The other record is null

SQL>
SQL> create or replace procedure p_test is   2 cursor cur_nvl is select col1, col2, nvl(col2,'{NULL}') nvl_func from test;
  3 begin

  4  	     for rec_nvl in cur_nvl loop
  5  		     dbms_output.put_line(rec_nvl.nvl_func);
  6  	     end loop;

  7 end;
  8 /

Procedure created.

SQL> 
SQL> set serveroutput on
SQL> execute p_test;

{NULL}

The other record is null

PL/SQL procedure successfully completed.

SQL>
SQL> create or replace procedure p_test is   2 cursor cur_nvl2 is select col1, col2, nvl2(col2,'NOT NULL: '||col2,'{NULL}') nvl2_func from test;
  3 begin

  4  	     for rec_nvl2 in cur_nvl2 loop
  5  		     dbms_output.put_line(rec_nvl2.nvl2_func);
  6  	     end loop;

  7 end;
  8 /

Warning: Procedure created with compilation errors.

SQL>
SQL> show errors
Errors for PROCEDURE P_TEST:

LINE/COL ERROR

-------- -----------------------------------------------------------------
2/21     PL/SQL: SQL Statement ignored
2/40     PLS-00201: identifier 'NVL2' must be declared
5/3      PL/SQL: Statement ignored
5/24     PLS-00364: loop index variable 'REC_NVL2' use is invalid
SQL> set serveroutput off
SQL> 

SQL> set echo off
SQL> spool off

---

Received on Fri Aug 30 2002 - 06:54:49 CDT