Re: nvl2() can not be used in a cursor?

Jochen Van den Bossche wrote:

> 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
> **************************************************************************

Write it as native dynamic SQL.

Daniel Morgan Received on Fri Aug 30 2002 - 13:35:04 CDT