Home » SQL & PL/SQL » SQL & PL/SQL » nullif and zero_divide
nullif and zero_divide [message #563732] Thu, 16 August 2012 12:26 Go to next message
ora1980
Messages: 247
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 #563733 is a reply to message #563732] Thu, 16 August 2012 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you post a SMALLER test case of what you want to show?
Not a bunch of tables and lines of code just to point us to nullif function.

For instance:
SQL> select 1/nullif(0,0) from dual;
1/NULLIF(0,0)
-------------


1 row selected.

Isn't that answer the question?

Regards
Michel

Re: nullif and zero_divide [message #563737 is a reply to message #563733] Thu, 16 August 2012 12:42 Go to previous messageGo to next message
ThomasG
Messages: 3099
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 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
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". Wink

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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2009
Registered: January 2010
Senior Member
Michel Cadot wrote on Thu, 16 August 2012 14:57
So 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 Very Happy :

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

Re: nullif and zero_divide [message #563754 is a reply to message #563750] Thu, 16 August 2012 14:42 Go to previous message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good catch!

Regards
Michel
Previous Topic: PL/SQL question
Next Topic: character translation
Goto Forum:
  


Current Time: Mon Sep 01 10:41:21 CDT 2014

Total time taken to generate the page: 0.11440 seconds