|
|
|
|
|
|
|
|
| Re: NVL2 -> why doesn't this work in a package??? Oracle [message #164144 is a reply to message #164143] |
Wed, 22 March 2006 00:08   |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I've noticed the same thing (10.1). I just put it down to Oracle being Oracle.
begin
dbms_output.putline(nvl2(1,2,3));
end;
/
dbms_output.putline(nvl2(1,2,3));
*
ERROR at line 2:
ORA-06550: line 2, column 21:
PLS-00201: identifier 'NVL2' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
_____________
Ross Leishman
|
|
|
|
| Re: NVL2 -> why doesn't this work in a package??? Oracle [message #164155 is a reply to message #164144] |
Wed, 22 March 2006 01:00 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
SQL> select nvl2('A', 'B', 'C') from dual;
N
-
B
SQL> declare
2 l varchar2(1);
3 begin
4 select nvl2('A', 'B', 'C') into l from dual;
5 end;
6 /
PL/SQL procedure successfully completed.
Apparently it is a sql-function only. It DOES work in a package in R9i.
In 8i there were quite some differences between the plain SQL-engine and the SQL-engine used in PL/SQL.
|
|
|
|
|
|
|
|
|
|
| Re: NVL2 -> why doesn't this work in a package??? Oracle [message #164384 is a reply to message #164372] |
Thu, 23 March 2006 04:20 |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
| Frank wrote on Thu, 23 March 2006 10:38 |
Functions like decode throw a different (clearer!) error when used in pl/sql. With nvl2 it is just like it is not recognized.
|
Ok, I cheated on this one: I had the same error once 
But this is what Tom Kyte has to say about it:
| Tom Kyte in thread "Features of 9i" |
nvl2 is a function, not sql, they apparently forgot to "map the function"
|
This would explain the different error messages.
MHE
[Updated on: Thu, 23 March 2006 04:21] Report message to a moderator |
|
|
|
|
|
|
|
| Re: NVL2 -> why doesn't this work in a package??? Oracle [message #164412 is a reply to message #164408] |
Thu, 23 March 2006 06:07 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
I would not even use the select into. Just use plain pl/sql.
I think the meaning of
if vara is null
then
varb := 'pqr';
else
varb := 'str';
end if;
is soooo much clearer then
execute immediate 'select nvl2(:vara, ''str'', ''pqr'' from dual' into varb using vara
But that could just be me
[Edit: Of course there are other constructions using CASE etc]
[Updated on: Thu, 23 March 2006 06:08] Report message to a moderator |
|
|
|
| Re: NVL2 -> why doesn't this work in a package??? Oracle [message #164423 is a reply to message #164127] |
Thu, 23 March 2006 06:37 |
chetwyn
Messages: 73
Registered: December 2005
|
Member |
|
|
Interesting.
Here is a quick trace extract on them both showing some difference in overhead.
hhmmm.. no difference on such a small query. Frank's seems easier to read thou.
TKPROF: Release 9.2.0.1.0 - Production on Thu Mar 23 23:33:37 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: op_ora_2660.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
alter session set sql_trace true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44
********************************************************************************
declare
varA VARCHAR2(10) := 'x';
varB VARCHAR2(10);
begin
EXECUTE IMMEDIATE
' SELECT nvl2( :varA,''str'',''pqr'') FROM dual ' INTO varB USING varA;
dbms_output.put_line(' varB = ' || varB );
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44
********************************************************************************
select user#
from
sys.user$ where name = 'OUTLN'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID USER$
1 INDEX UNIQUE SCAN I_USER1 (object id 44)
********************************************************************************
SELECT nvl2( :varA,'str','pqr')
FROM
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 0 3 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL
********************************************************************************
declare
varA VARCHAR2(10) := 'x';
varB VARCHAR2(10);
begin
select varA into varB from dual;
if varA is null
then
varb := 'pqr';
else
varb := 'str';
end if;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44
********************************************************************************
SELECT :b1
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 0 0 2
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.01 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 8 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.01 0.00 0 8 0 3
Misses in library cache during parse: 1
5 user SQL statements in session.
1 internal SQL statements in session.
6 SQL statements in session.
********************************************************************************
Trace file: op_ora_2660.trc
Trace file compatibility: 9.00.01
Sort options: default
1 session in tracefile.
5 user SQL statements in trace file.
1 internal SQL statements in trace file.
6 SQL statements in trace file.
6 unique SQL statements in trace file.
83 lines in trace file.
[Updated on: Thu, 23 March 2006 06:41] Report message to a moderator |
|
|
|
|
|
|
|
| Re: NVL2 -> why doesn't this work in a package??? Oracle [message #613518 is a reply to message #613515] |
Wed, 07 May 2014 12:11 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
in 8i the server users a different code base then sql@plus. I ran the following test on an 8i server
1* select nvl2(null,'its not null','its null') from dual
custom@live>/
NVL2(NUL
--------
its null
custom@live>edit
Wrote file afiedt.buf
1 declare
2 junk varchar2(20);
3 begin
4 select nvl2(null,'its not null','its null') into junk from dual;
5* end;
6 /
select nvl2(null,'its not null','its null') into junk from dual;
*
ERROR at line 4:
ORA-06550: line 4, column 8:
PLS-00201: identifier 'NVL2' must be declared
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored
[Updated on: Wed, 07 May 2014 12:12] Report message to a moderator |
|
|
|
|
|
|
|
| Re: NVL2 -> why doesn't this work in a package??? Oracle [message #613526 is a reply to message #613519] |
Wed, 07 May 2014 13:02 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
BlackSwan wrote on Wed, 07 May 2014 22:43 The newbie resurrected an EIGHT YEAR OLD zombie thread.
BS, please don't get me wrong. But there is no archive rule for old topics in this forum. Had it been there, people won't be able to post a new comment to an archived thread.
And I have seen few moderators always being positive about helping people irrespective of the fact that the thread is too old. So I am confused here with the form rules. Should I answer to people who post in an old thread?
|
|
|
|
|
|
|
|
| Re: NVL2 -> why doesn't this work in a package??? Oracle [message #613541 is a reply to message #613539] |
Wed, 07 May 2014 14:00 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Well, it was wrong answer anyway. NVL2 and COALESCE have different functionality. Most likely shulamit kerem confused it with NVL. And even though post is 8 years old, issue is still there - Oracle still maintains two separate engines for SQL & PL/SQL and some SQL functions like NVL2 are not available in PL/SQL.
SY.
|
|
|
|
|
|
|
|
| Re: NVL2 -> why doesn't this work in a package??? Oracle [message #613556 is a reply to message #613543] |
Wed, 07 May 2014 17:15 |
 |
shulamit kerem
Messages: 2
Registered: May 2014
|
Junior Member |
|
|
ohh... sorry!
I usually only read, but i searched answer for same problem... and thought, if i get this page, maybe i try to help for next person, too...
maybe, if a, b, c then number values:
nvl2(a,b,c)
equivalent (?)
coalesce(a-a+b,c)
my english. sorry again.
anyway, very thank you! 
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
