Saturday, July 25, 2009

On Numbers

I somehow lost my posting privileges to oracle-l [probably due to the read-only mode I'm in]...
You are not currently authorized to post messages to oracle-l.

New subscribers to this list are not able to post messages at first. To get this privilege turned on, you must email the list administrator at oracle-l-admins@freelists.org. If you are using an anonymous email address, you must also identify yourself. Please be sure to send the request from the address for which you require the privilege, not from some other address.
However to avoid lost of information I'd put it in here. This post is in reply to the question about oracle numbers, namely:
anyone every see this or know how it could happen:
SQL> select * from toto;

NUM
----------
0
-.
-.
-.
-.
-.
0

7 rows selected.

SQL> desc toto;
Name Null? Type
----------------------------------------- -------- --------------
NUM NUMBER(18,5)
Here is my reply:

Oracle engine can construct numbers, valid or "invalid" ones. It can be due to the bugs (OCI, jdbc, PL/SQL) or because one was playing foolish games like I am doing below. Both positive and negative zero, as well as positive and negative infinity do exist for years, with the introduction of "new numbers" (BINARY_FLOAT) all that magic became documented (in some sense), however for TRUE numeric (NUMBER) columns the anomalies your faced below are primarily due to the bugs in client software or due to misuse of PL/SQL. I have to warn the readers that the example below must not be used on any production environment, moreover one must not try to insert those "numbers" into any table that is considered part of any production environment -- the results are unpredictable and can crash clients and damage users' experiences. Be careful. Vladimir
REM Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

CREATE TABLE t (i NUMBER, n NUMBER, h RAW(10));
BEGIN
FOR i IN 0..255
LOOP
BEGIN
INSERT INTO t VALUES(
i
, utl_raw.cast_to_number(HEXTORAW(TO_CHAR(i, 'FM0X')))
, HEXTORAW(TO_CHAR(i, 'FM0X'))
);
EXCEPTION
WHEN OTHERS THEN NULL;
END;
BEGIN
INSERT INTO t VALUES(
-i
, utl_raw.cast_to_number(HEXTORAW(TO_CHAR(i, 'FM0X') || '66'))
, HEXTORAW(TO_CHAR(i, 'FM0X') || '66')
);
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END LOOP;
END;
/
SET TRIMSPOOL ON TAB OFF PAGES 1000 LINES 10000
COLUMN dmp FORMAT A30
COLUMN h FORMAT A20
SPOOL /tmp/kyle.lst
SELECT ABS(i) i, n, h, DUMP(n, 16) dmp FROM t ORDER BY ABS(i), i;
SPOOL OFF

No comments: