Let's begin with MS ODBC for Oracle!
The biggest problem is: it's a Level 2 driver:
- the column names conform to Level 1
- most Level 3 columns are missing
- the old datatypes are used (DATE=11)
The REMARKS field is empty. I don't know if the driver or the test
tool purges that string.
The PRECISION = 15 for FLOATS is dubious. This value is defined in
the ODBC spec:
<http://msdn.microsoft.com/library/en-us/odbc/htm/odbccolumn_size.asp>
(the purpose is unclear to me), but does not correspond to the
declared value.
The LENGTH for FLOATS looks dubious. How fit 8 bytes into 126 bits?
But the SQL (grabbed from V$SQLAREA) looks very interesting!
Steffen
MS ODBC for Oracle:
===================
MS ODBC Test 2.60.0905
----------------------
Get Info All:
SQL_DBMS_NAME = 17, 6, "Oracle"
SQL_DBMS_VER = 18, 69, "08.01.0000 Oracle8i Enterprise Edition Release
8.1.7.2.1 - Production"
SQL_DM_VER = 171, 15, "03.52.6019.0000"
SQL_DRIVER_NAME = 6, 12, "msorcl32.dll"
SQL_DRIVER_ODBC_VER = 77, 5, "02.50"
SQL_DRIVER_VER = 7, 11, "02.573.6019"
SQL_ODBC_VER = 10, 10, "03.52.0000"
SQLColumns:
In:
StatementHandle = 0x008A1860
CatalogName = SQL_NULL_HANDLE, NameLength1 = 0
SchemaName = "TST" , NameLength2 = 3
TableName = "TYPETEST" , NameLength3 = 8
ColumnName = SQL_NULL_HANDLE, NameLength4 = 0
Return:
SQL_SUCCESS = 0
Get Data All:
TABLE TABLE TABLE COLUMN_NAME DATA TYPE PRECISION LENGTH SCALE RADIX
NULLABLE REMARKS ORDINAL
QUALIFIER OWNER NAME TYPE NAME
POSITION
--------- ----- -------- ----------------- ---- ------ --------- ------ ------ ------
-------- ------- --------
<Null> TST TYPETEST CINTEGER 3 NUMBER 38 40 0 10
0 "" 1
<Null> TST TYPETEST CNUMBER 3 NUMBER 15 8 <Null> 10
1 <Null> 2
<Null> TST TYPETEST CNUMBER_38_0 3 NUMBER 38 40 0 10
1 <Null> 3
<Null> TST TYPETEST CNUMBER_11_2 3 NUMBER 11 13 2 10
1 <Null> 4
<Null> TST TYPETEST CNUMBER_XX_2 3 NUMBER 38 40 2 10
1 <Null> 5
<Null> TST TYPETEST CFLOAT 8 FLOAT 15 8 <Null> 10
1 <Null> 6
<Null> TST TYPETEST CFLOAT_11 8 FLOAT 15 8 <Null> 10
1 <Null> 7
<Null> TST TYPETEST CREAL 8 FLOAT 15 8 <Null> 10
1 <Null> 8
<Null> TST TYPETEST CDOUBLE_PRECISION 8 FLOAT 15 8 <Null> 10
1 <Null> 9
<Null> TST TYPETEST CDATE 11 DATE 19 16 0 10
1 <Null> 10
<Null> TST TYPETEST CCHAR 1 CHAR 1 1 <Null> <Null>
1 <Null> 11
<Null> TST TYPETEST CVARCHAR 12 VARCHAR2 7 7 <Null> <Null>
1 <Null> 12
<Null> TST TYPETEST CVARCHAR2 12 VARCHAR2 7 7 <Null> <Null>
1 <Null> 13
select null
, co.owner
, co.table_name
, co.column_name
, decode( data_type
, 'CHAR' , 1
, 'DATE' , 11
, 'FLOAT' , 8
, 'LONG' , -1
, 'LONG RAW', -4
, 'NUMBER' , 3
, 'RAW' , -3
, 'VARCHAR2', 12
, 'ROWID' , 1
, 0
)
, data_type
, decode( data_type
, 'DATE' , 19
, 'FLOAT' , 15
, 'LONG' , 2147483647
, 'LONG RAW', 2147483647
, 'ROWID' , 18
, 'NUMBER' , decode( data_precision
, null, decode( data_scale
, null, 15
, 0 , 38
, 38
)
, data_precision
)
, data_length
)
, decode( data_type
, 'LONG' , 2147483647
, 'FLOAT' , 8
, 'NUMBER' , decode( data_precision
, null, decode( data_scale
, null, 8
, 0 , 40
, 40
)
, data_precision + 2
)
, 'DATE' , 16
, 'ROWID' , 18
, data_length
)
, decode( data_type
, 'DATE' , 0
, 'FLOAT' , null
, data_scale
)
, decode( data_type
, 'NUMBER' , 10
, 'FLOAT' , 10
, 'DATE' , 10
, null
)
, decode( nullable
, 'N', 0
, 'Y', 1
, 2
)
, cm.comments
, co.column_id
from all_tab_columns co
, all_col_comments cm
where co.table_name = cm.table_name
and co.column_name = cm.column_name
and co.owner = cm.owner
and co.owner like 'TST' escape '\'
and co.table_name like 'TYPETEST' escape '\'
order by 1, 2, 3, 13
