Re: SQL TYPE MAP such as SQL_CHAR, SQL_NUMERIC , etc

On fim, 2006-02-23 at 16:31 -0500, Emi Lu wrote:

> We use perl DBI to read table names, column names, and column types from
> Oracle rdb 7.3 through ODBC, and then try to create tables into postgresql.
>
> Through perl DBI, we got:
>
>
> Column Name Type Precision Scale Nullable?
> ------------------------------ ---- --------- ----- ---------
>
> col1 1 4 0 Yes
> col2 1 4 0 Yes
> col3 1 2 0 Yes
> col4 4 11 0 Yes
> col5 3 4 2 Yes
> col6 93 13 0 Yes
> ...
> ...
>
> I'd like to know how to map the integer type value "1, 3, 4, 93, etc" to
> SQL_type?
>
>
> >>>For example, if I have value 1 , so that I know 1 is mapped to
> >>>SQL_CHAR; if I have value 3, so that I know 3 is mapped to SQL_NUMERIC.
> >>>
> >>>
> >>> Data Types
> >>>
> >>> The following data types are supported:
> >>>
> >>> SQL_CHAR
> >>>
> >>> SQL_VARCHAR
> >>>
> >>> SQL_LONGVARCHAR
> >>>
> >>> SQL_NUMERIC
> >>>
....

these are not really Oracle type names so I guess these are ODBC type
names, and the mapping you talk of is
maybe some ODBC thing.

in DBD::Oracle found on CPAN I find this nice little SQL to generate
columns listing, which might give you some clues:

my $Sql = <<"SQL";
SELECT *
FROM
(
SELECT /*+ RULE*/
to_char( NULL ) TABLE_CAT
, tc.OWNER TABLE_SCHEM
, tc.TABLE_NAME TABLE_NAME
, tc.COLUMN_NAME COLUMN_NAME
, $typecase decode( tc.DATA_TYPE
, 'MLSLABEL' , -9106
, 'ROWID' , -9104
, 'UROWID' , -9104
, 'BFILE' , -4 -- 31?
, 'LONG RAW' , -4
, 'RAW' , -3
, 'LONG' , -1
, 'UNDEFINED', 0
, 'CHAR' , 1
, 'NCHAR' , 1
, 'NUMBER' , decode( tc.DATA_SCALE, NULL, 8, 3 )
, 'FLOAT' , 8
, 'VARCHAR2' , 12
, 'NVARCHAR2', 12
, 'BLOB' , 30
, 'CLOB' , 40
, 'NCLOB' , 40
, 'DATE' , 93
, NULL
) $typecaseend DATA_TYPE -- ...
, tc.DATA_TYPE TYPE_NAME -- std.?
, decode( tc.DATA_TYPE
, 'LONG RAW' , 2147483647
, 'LONG' , 2147483647
, 'CLOB' , 2147483647
, 'NCLOB' , 2147483647
, 'BLOB' , 2147483647
, 'BFILE' , 2147483647
, 'NUMBER' , decode( tc.DATA_SCALE
, NULL, 126
, nvl( tc.DATA_PRECISION, 38 )
)
, 'FLOAT' , tc.DATA_PRECISION
, 'DATE' , 19
, tc.DATA_LENGTH
) COLUMN_SIZE
, decode( tc.DATA_TYPE
, 'LONG RAW' , 2147483647
, 'LONG' , 2147483647
, 'CLOB' , 2147483647
, 'NCLOB' , 2147483647
, 'BLOB' , 2147483647
, 'BFILE' , 2147483647
, 'NUMBER' , nvl( tc.DATA_PRECISION, 38 ) + 2
, 'FLOAT' , 8 -- ?
, 'DATE' , 16
, tc.DATA_LENGTH
) BUFFER_LENGTH
, decode( tc.DATA_TYPE
, 'DATE' , 0
, tc.DATA_SCALE
) DECIMAL_DIGITS -- ...
, decode( tc.DATA_TYPE
, 'FLOAT' , 2
, 'NUMBER' , decode( tc.DATA_SCALE, NULL, 2, 10 )
, NULL
) NUM_PREC_RADIX
, decode( tc.NULLABLE
, 'Y' , 1
, 'N' , 0
, NULL
) NULLABLE
, cc.COMMENTS REMARKS
, tc.DATA_DEFAULT COLUMN_DEF -- Column is LONG!
, decode( tc.DATA_TYPE
, 'MLSLABEL' , -9106
, 'ROWID' , -9104
, 'UROWID' , -9104
, 'BFILE' , -4 -- 31?
, 'LONG RAW' , -4
, 'RAW' , -3
, 'LONG' , -1
, 'UNDEFINED', 0
, 'CHAR' , 1
, 'NCHAR' , 1
, 'NUMBER' , decode( tc.DATA_SCALE, NULL, 8, 3 )
, 'FLOAT' , 8
, 'VARCHAR2' , 12
, 'NVARCHAR2', 12
, 'BLOB' , 30
, 'CLOB' , 40
, 'NCLOB' , 40
, 'DATE' , 9 -- not 93!
, NULL
) SQL_DATA_TYPE -- ...
, decode( tc.DATA_TYPE
, 'DATE' , 3
, NULL
) SQL_DATETIME_SUB -- ...
, to_number( NULL ) CHAR_OCTET_LENGTH -- TODO
, tc.COLUMN_ID ORDINAL_POSITION
, decode( tc.NULLABLE
, 'Y' , 'YES'
, 'N' , 'NO'
, NULL
) IS_NULLABLE
FROM ALL_TAB_COLUMNS tc
, ALL_COL_COMMENTS cc
WHERE tc.OWNER = cc.OWNER
AND tc.TABLE_NAME = cc.TABLE_NAME
AND tc.COLUMN_NAME = cc.COLUMN_NAME
)
WHERE 1 = 1
SQL