Re: "-." in number field

> 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       

--
http://www.freelists.org/webpage/oracle-l