Tim Bunce wrote:
>
> I'd be interested to know what the Oracle ODBC driver returns for
> various forms of Oracle NUMBER and happiest to do the same in DBD::Oracle.
Even, if the driver is very ugly? We have the chance to
make it better!
>
> Tim.
>
Here our test table (I omit negative scale):
CREATE TABLE TypeTest
(
cINTEGER INTEGER
, cNUMBER NUMBER
, cNUMBER_38_0 NUMBER ( 38, 0 )
, cNUMBER_11_2 NUMBER ( 11, 2 )
, cNUMBER_xx_2 NUMBER ( *, 2 )
, cFLOAT FLOAT
, cFLOAT_11 FLOAT ( 11 )
, cREAL REAL
, cDOUBLE_PRECISION DOUBLE PRECISION
)
/
SQL> desc typetest
Name Null? Type
------------------ -------- ----------------
CINTEGER NUMBER(38)
CNUMBER NUMBER
CNUMBER_38_0 NUMBER(38)
CNUMBER_11_2 NUMBER(11,2)
CNUMBER_XX_2 NUMBER(38,2)
CFLOAT FLOAT(126)
CFLOAT_11 NUMBER(11)
CREAL NUMBER(63)
CDOUBLE_PRECISION FLOAT(126)
Note: CFLOAT_11 (binary prec.) vs. NUMBER(11) (decimal prec.)
This seems to be a bug in SQL*Plus DESCRIBE.
Now the information from the data dictionary:
select COLUMN_NAME
, DATA_TYPE
, DATA_PRECISION
, DATA_SCALE
from dba_tab_columns
where table_name = 'TYPETEST'
order by COLUMN_ID
COLUMN_NAME DATA_TYPE DATA_PRECI DATA_SCALE
------------------ ---------- ---------- ----------
CINTEGER NUMBER 0
CNUMBER NUMBER
CNUMBER_38_0 NUMBER 38 0
CNUMBER_11_2 NUMBER 11 2
CNUMBER_XX_2 NUMBER 2
CFLOAT FLOAT 126
CFLOAT_11 FLOAT 11
CREAL FLOAT 63
CDOUBLE_PRECISION FLOAT 126
The Oracle ODBC driver 8.1.7 returns (via DBD::ODBC! I hope
they do not mangle the results!):
TYPE PRECISION SCALE NAME
---- --------- ----- ------------------
3 38 0 CINTEGER
8 38 0 CNUMBER
3 38 0 CNUMBER_38_0
3 11 2 CNUMBER_11_2
3 38 2 CNUMBER_XX_2
8 38 0 CFLOAT
8 4 0 CFLOAT_11
8 19 0 CREAL
8 38 0 CDOUBLE_PRECISION
Note: Oracle returns decimal precision in all cases
(and not a binary prec. for FLOAT's).
Steffen
