| nullif and zero_divide [message #563732] |
Thu, 16 August 2012 12:26  |
ora1980
Messages: 243
Registered: May 2008
|
Senior Member |
|
|
Does the below example illustrate that if I use NULLIF, then I can safely NOT code any handling of zero_divide exception specifically ?
create table bigger(cnumber number, ynumber number);
create table smaller(x number, y number);
create table results(x number);
insert into bigger values(1,1);
insert into bigger values(2,2);
insert into bigger values(3,2);
insert into smaller values(1,1);
insert into smaller values(2,0);
insert into smaller values(3,3);
commit;
procedure that loops through bigger and smaller table, notice x/y (usage of nullif for y)
create or replace procedure tproc is
v_sql varchar2(4000);
type t_ref_cur is ref cursor;
post_cur t_ref_cur;
type t_post_rec is record(x number, y number);
post_rec t_post_rec;
begin
for intr_rec in (select cnumber, ynumber from bigger) loop
v_sql := 'select x,y from smaller where x ='||intr_rec.cnumber||' ';
OPEN post_cur FOR v_Sql;
LOOP
FETCH post_cur INTO Post_Rec;
EXIT WHEN post_cur%NOTFOUND;
dbms_output.put_line('post_rec.x: '||post_rec.x);
dbms_output.put_line('post_rec.y: '||post_rec.y);
IF (post_rec.x/nullif(post_rec.y,0))=1 then
insert into results values (post_rec.x);
commit;
else
dbms_output.put_line('nothihg');
null;
END IF;
end loop;
end loop;
exception
when others then
dbms_output.put_line('in exception now');
null;
end tproc;
executing above procedure gives me 1 and 3 as rows in results table.....2/0 did not give me zero_divide exception
so can I dare to use nullif and forget about handling zero_divide specifically ?
|
|
|
|
|
|
| Re: nullif and zero_divide [message #563737 is a reply to message #563733] |
Thu, 16 August 2012 12:42  |
ThomasG
Messages: 2886
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
I just found this possible problem if the input parameter is NULL:
SQL> select 1 / nullif(NULL,0) from dual;
select 1 / nullif(NULL,0) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
SQL>
Edit:
Ah, ok from the documentation:
Purpose
NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1. You cannot specify the literal NULL for expr1.
It should be OK if the "NULL" is in a variable with a declared data type.
[Updated on: Thu, 16 August 2012 12:44] Report message to a moderator |
|
|
|
| Re: nullif and zero_divide [message #563747 is a reply to message #563737] |
Thu, 16 August 2012 13:57 |
 |
Michel Cadot
Messages: 54223
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> var x number
SQL> select 1 / nullif(:x,0) from dual;
1/NULLIF(:X,0)
--------------
1 row selected.
Yes but...
SQL> var v varchar2(10)
SQL> select 1 / nullif(:v,0) from dual;
select 1 / nullif(:v,0) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
So the sentence is "It should be OK if the "NULL" is in a variable with a declared appropriate data type". 
Weirdly:
SQL> select 1 / nullif(to_number(null),0) from dual;
select 1 / nullif(to_number(null),0) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got NUMBER
does not work when the following does:
SQL> exec :x := 0;
PL/SQL procedure successfully completed.
SQL> select 1 / nullif(nullif(:x,0),0) from dual;
1/NULLIF(NULLIF(:X,0),0)
------------------------
1 row selected.
So some inconsistencies in Oracle code...
Regards
Michel
[Edit: forgot to paste "x" assignment]
[Updated on: Thu, 16 August 2012 13:59] Report message to a moderator |
|
|
|
| Re: nullif and zero_divide [message #563750 is a reply to message #563747] |
Thu, 16 August 2012 14:18 |
Solomon Yakobson
Messages: 1404
Registered: January 2010
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 16 August 2012 14:57So some inconsistencies in Oracle code...
No inconsistencies. AFAIK Oracle added it to conform to SQL standard. And it was a "quick and dirty" add. "Dirty" is a sense Oracle docs are not telling what exactly NULLIF is. But no one can hide the truth  :
SQL> explain plan for
2 select 1 from dual
3 where nullif(dummy,'X') is null;
Explained.
SQL> @?\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter(CASE "DUMMY" WHEN 'X' THEN NULL ELSE "DUMMY" END IS NULL)
13 rows selected.
SQL>
As you can see, NULLIF(X,Y) is nothing but shortcut to
CASE X WHEN Y THEN NULL ELSE X END
And CASE rules are - expressions must be of same type. That's why it fails for bind variable of VARCHAR2 datatype. If you use '0' instead of 0 if will work:
SQL> var v varchar2(10)
SQL> select 1 / nullif(:v,'0') from dual;
1/NULLIF(:V,'0')
----------------
SQL>
SY.
[Updated on: Thu, 16 August 2012 14:23] Report message to a moderator |
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
