nvl2 in 8.1.7.3.0

EdwardP · #1 (**permalink**) 03-22-02, 16: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 · #2 (**permalink**) 03-23-02, 05: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 · #3 (**permalink**) 04-01-02, 18: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:

Code:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech