Let's continue with Oracle's ODBC driver!
It seems, that many column values are processed at the client
side. It's a pity, because in this way the SQL statement contains
less useful information.
REMARKS are omitted at all! (There is no join with ALL_COL_COMMENTS.)
COLUMN_DEFs are omitted! Maybe, because ALL_TAB_COLUMNS.DATA_DEFAULT
is a LONG?
CHAR_OCTET_LENGTHs are omitted!
The BUFFER_LENGTH for FLOATS looks dubious. How fit 8 bytes into
126 bits ... er ... vice versa: how fit 126 bits (the max. binary
precision of FLOATs) into an 8 byte buffer?
In addition to base tables, they provide the column information for
SYNONYMS too.
Steffen
Oracle ODBC 8.1.7:
==================
MS ODBC Test 2.60.0905
----------------------
Get Info All:
SQL_DBMS_NAME = 17, 6, "Oracle"
SQL_DBMS_VER = 18, 10, "08.01.0721"
SQL_DM_VER = 171, 15, "03.52.6019.0000"
SQL_DRIVER_NAME = 6, 11, "SQORA32.DLL"
SQL_DRIVER_ODBC_VER = 77, 5, "03.51"
SQL_DRIVER_VER = 7, 10, "08.01.0704"
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 DATA TYPE COLUMN BUFFER DECIMAL NUM_PREC
NULLABLE REMARKS COLUMN SQL_DATA SQL_DATE CHAR_OCTET ORDINAL IS
CAT SCHEM NAME NAME TYPE NAME SIZE LENGTH DIGITS RADIX
DEF TYPE TIME_SUB LENGTH POSITION NULLABLE
----- ----- -------- --------------- ---- ------------ ------ ------ ------- --------
-------- ------- ------ -------- -------- ---------- -------- --------
"" TST TYPETEST CINTEGER 3 DECIMAL 38 40 0 10
0 "" "" 3 1 NO
"" TST TYPETEST CNUMBER 8 DOUBLE PRECISION 0 40 <Null> 10
1 "" "" 8 2 YES
"" TST TYPETEST CNUMBER_38_0 3 DECIMAL 38 40 0 10
1 "" "" 3 3 YES
"" TST TYPETEST CNUMBER_11_2 3 DECIMAL 11 13 2 10
1 "" "" 3 4 YES
"" TST TYPETEST CNUMBER_XX_2 3 DECIMAL 38 40 2 10
1 "" "" 3 5 YES
"" TST TYPETEST CFLOAT 8 DOUBLE PRECISION 38 8 <Null> 10
1 "" "" 8 6 YES
"" TST TYPETEST CFLOAT_11 8 DOUBLE PRECISION 4 8 <Null> 10
1 "" "" 8 7 YES
"" TST TYPETEST CREAL 8 DOUBLE PRECISION 19 8 <Null> 10
1 "" "" 8 8 YES
"" TST TYPETEST CDOUBLE_PRECISION 8 DOUBLE PRECISION 38 8 <Null> 10
1 "" "" 8 9 YES
"" TST TYPETEST CDATE 93 DATE 19 16 <Null> <Null>
1 "" "" 9 3 10 YES
"" TST TYPETEST CCHAR 1 CHAR 1 1 <Null> <Null>
1 "" "" 1 11 YES
"" TST TYPETEST CVARCHAR 12 VARCHAR2 7 7 <Null> <Null>
1 "" "" 12 12 YES
"" TST TYPETEST CVARCHAR2 12 VARCHAR2 7 7 <Null> <Null>
1 "" "" 12 13 YES
13 rows fetched from 18 columns.
SELECT /*+ RULE */
''
, owner
, table_name
, column_name
, 0
, data_type
, data_precision
, decode( data_type
,'DATE' , 16
,'FLOAT' , 8
,'LONG RAW', 2147483647
,'LONG' , 2147483647
,'CLOB' , 2147483647
,'BLOB' , 2147483647
,'BFILE' , 2147483647
,'NUMBER' , NVL( data_precision + 2, 40 )
, data_length
)
, data_scale
, 0
, decode( nullable, 'Y', 1, 'N', 0 )
, ''
, ''
, 0
, 0
, 0
, column_id
, decode( nullable, 'Y', 'YES', 'N', 'NO')
FROM all_tab_columns
WHERE TABLE_NAME LIKE 'TYPETES%'
AND OWNER = 'TST'
UNION
SELECT /*+ RULE */
''
, b.owner
, b.synonym_name
, a.column_name
, 0
, a.data_type
, a.data_precision
, a.data_length
, a.data_scale
, 0
, decode(a.nullable, 'Y', 1, 'N', 0)
, ''
, ''
, 0
, 0
, 0
, a.column_id
, decode(a.nullable, 'Y', 'YES', 'N', 'NO')
FROM all_tab_columns a
, all_synonyms b
WHERE a.table_name = b.table_name
and a.owner = b.table_owner
AND b.synonym_name LIKE 'TYPETES%'
AND b.OWNER = 'TST'
ORDER BY 2,3,17
