nvl2 in 8.1.7.3.0 [Archive]

EdwardP

03-22-02, 17:05

Hello everyone,

Could you explain the following?

There is no problem here:
SQL> select nvl2(null,1,2) from dual;

NVL2(NULL,1,2)
--------------
2

And I get this here:

SQL> begin
2 dbms_output.put_line(nvl2(null,1,2));
3 end;
4 /
dbms_output.put_line(nvl2(null,1,2));
*
ERROR at line 2:
ORA-06550: line 2, column 23:
PLS-00201: identifier 'NVL2' must be declared
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored

Thanks in advance.
Edward

alligatorsql.com

03-23-02, 06:14

Hello,

I am not sure, but could it be, that you have to use the schema name before the function name ?

something like dbms_output.put_line(TO_CHAR(user.nvl2(null, 1, 2));

Hope this helps ?

Greetings

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

Todd Barry

04-01-02, 19:58

Until 9i, PL/SQL does not support all of the SQL functions and syntax (including features such as analytic functions, CAST, and others).

But, you can use NVL2 in PL/SQL by executing it as dynamic SQL:

declare
x integer;
begin
execute immediate 'select nvl2(null, 1, 2) from dual' into x;
dbms_output.put_line( x );
end;