nvl2

[d27111]

I'm using PL/SQL Developer 5.1.6.747 against an Oracle 8.1.7 database.

From a PL/SQL developer SQL Window I can successfully perform the following query:
select nvl2('1', 'not null', 'null') from dual

When I try and use nvl2 in a package body using a PL/SQL Developer Program Window as follows:
vc_column_name := vc_column_name||nvl2(vc_column_name, ',', null)||vc_column_name;

I get an error of:
PLS-00201: identifier 'NVL2' must be declared.

Am I doing something wrong?

 

[oghislain]

Hi,

I found this about nvl2 in Metalink :

NVL2 GIVES PLS-00201 WHEN USED FROM PLSQL BLOCK
--------------------------------------------------------------------------------

*** 02/24/00 08:39 am *** NVL2 function works fine from SQL*PLUS. But the same raises PLS-00201, ORA-06550 when used from plsql block. The following is the test case. It is failing from 8.0.3 to 8.1.6. . -- From SQL*PLUS SQL> select NVL2('abc', 'not null' ,'null') from dual; . NVL2('AB -------- not null . -- From PLSQL . . SQL> declare 2 temp varchar2(30); 3 begin 4 select nvl2('abc', 'not null', 'null') INTO temp from dual; 5 end; 6 / select nvl2('abc', 'not null', 'null') INTO temp from dual; * ERROR at line 4: ORA-06550: line 4, column 9: PLS-00201: identifier 'NVL2' must be declared ORA-06550: line 4, column 2: PL/SQL: SQL Statement ignored . *** 02/24/00 12:33 pm *** (CHG: Asg->NEW OWNER) *** 02/24/00 12:33 pm *** (CHG: Confirmed Flag->Y) *** 02/24/00 12:33 pm *** *** 02/24/00 12:33 pm *** (CHG: Asg->NEW OWNER) *** 02/24/00 03:20 pm *** *** 08/25/00 09:34 am *** (CHG: Sta->16) *** 08/25/00 09:34 am *** . I have a client encountering this bug. What is the status of this bug? Is there a workaround? . Thanks *** 08/25/00 12:27 pm *** Same behavior in 8.1.6 8.1.7 PLSQL_8.1_SOLARIS_000817 8.2.0 RDBMS_MAIN_SOLARIS_000823 . workaround is use dynamic pl/sql: either dbms_sql or execute immdediate . Here an an example using execute immediate set serverout on; declare temp varchar2(30); str varchar2(100); begin -- select nvl2('abc', 'not null', 'null') INTO temp from dual; str := 'select nvl2(''abc'', ''not null'', ''null'') from dual'; EXECUTE IMMEDIATE str into temp; dbms_output.put_line ('temp = '||temp); end; / . SQL> @1 temp = not null . PL/SQL procedure successfully completed. .

 

[d27111]

Thanks for finding the explanation!