Re: "-." in number field
- From: Yong Huang <yong321@xxxxxxxxx>
- To: breitliw@xxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Sat, 25 Jul 2009 12:23:51 -0700 (PDT)
> Bug 979657 - Oracle allows insert of -0 (negative zero) or other
> corrupt numbers from OCI/Pro
>
> This one is for version < 10.1.0.2
Looks like in sqlplus, a negative zero will be automatically changed to zero.
If you really want to test inserting this corrupt number with sqlplus,
utl_raw.cast_to_number or equivalent can be used:
Test in 10.2.0.4:
create table testneg0 (a number);
insert into testneg0 values (utl_raw.cast_to_number('3F66'));
insert into testneg0 values (-0);
SQL> select * from testneg0;
A
----------
-.
0
SQL> insert into testneg0 values (-0.00);
1 row created.
SQL> select * from testneg0;
A
----------
-.
0
0
SQL> select * from testneg0 where a > 0;
no rows selected
SQL> select * from testneg0 where a < 0;
A
----------
-.
SQL> select * from testneg0 where a = utl_raw.cast_to_number('3F66');
A
----------
-.
SQL> select * from testneg0 where a = 0;
A
----------
0
0
I think we can safely assume the corrupted negative zero should be zero. Then
the fix is easy:
SQL> update testneg0 set a = 0 where a = utl_raw.cast_to_number('3F66');
1 row updated.
SQL> select * from testneg0;
A
----------
0
0
0
Yong Huang
--
//www.freelists.org/webpage/oracle-l
Other related posts:
