By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (value NUMBER);
INSERT INTO table_name (value)
SELECT POWER(10, 5 * (LEVEL - 1) - 20) FROM DUAL CONNECT BY LEVEL <= 10 UNION ALL
SELECT -POWER(10, 5 * (LEVEL - 1) - 20) FROM DUAL CONNECT BY LEVEL <= 10
20 rows affected
WITH find_byte (value, dump_value, first_byte) AS (
SELECT value,
DUMP(value) AS dump_value,
TO_NUMBER(REGEXP_SUBSTR(DUMP(value), ': (\d+)', 1, 1, NULL, 1)) AS first_byte
FROM table_name
)
SELECT value,
dump_value,
first_byte,
CASE
WHEN BITAND(first_byte, 128) = 128
THEN 'Y'
ELSE 'N'
END AS is_positive,
CASE
WHEN BITAND(first_byte, 128) = 128
THEN first_byte
ELSE BITXOR(first_byte, 255)
END - 128 - 65 AS exponent,
FLOOR(LOG(100, ABS(value))) AS expected_exponent
FROM find_byte;
| VALUE | DUMP_VALUE | FIRST_BYTE | IS_POSITIVE | EXPONENT | EXPECTED_EXPONENT |
|---|---|---|---|---|---|
| .00000000000000000001 | Typ=2 Len=2: 183,2 | 183 | Y | -10 | -10 |
| .000000000000001 | Typ=2 Len=2: 185,11 | 185 | Y | -8 | -8 |
| .0000000001 | Typ=2 Len=2: 188,2 | 188 | Y | -5 | -5 |
| .00001 | Typ=2 Len=2: 190,11 | 190 | Y | -3 | -3 |
| 1 | Typ=2 Len=2: 193,2 | 193 | Y | 0 | 0 |
| 100000 | Typ=2 Len=2: 195,11 | 195 | Y | 2 | 2 |
| 10000000000 | Typ=2 Len=2: 198,2 | 198 | Y | 5 | 5 |
| 1000000000000000 | Typ=2 Len=2: 200,11 | 200 | Y | 7 | 7 |
| 100000000000000000000 | Typ=2 Len=2: 203,2 | 203 | Y | 10 | 10 |
| 10000000000000000000000000 | Typ=2 Len=2: 205,11 | 205 | Y | 12 | 12 |
| -.00000000000000000001 | Typ=2 Len=3: 72,100,102 | 72 | N | -10 | -10 |
| -.000000000000001 | Typ=2 Len=3: 70,91,102 | 70 | N | -8 | -8 |
| -.0000000001 | Typ=2 Len=3: 67,100,102 | 67 | N | -5 | -5 |
| -.00001 | Typ=2 Len=3: 65,91,102 | 65 | N | -3 | -3 |
| -1 | Typ=2 Len=3: 62,100,102 | 62 | N | 0 | 0 |
| -100000 | Typ=2 Len=3: 60,91,102 | 60 | N | 2 | 2 |
| -10000000000 | Typ=2 Len=3: 57,100,102 | 57 | N | 5 | 5 |
| -1000000000000000 | Typ=2 Len=3: 55,91,102 | 55 | N | 7 | 7 |
| -100000000000000000000 | Typ=2 Len=3: 52,100,102 | 52 | N | 10 | 10 |
| -10000000000000000000000000 | Typ=2 Len=3: 50,91,102 | 50 | N | 12 | 12 |