Attached is a possible implementation for DBD::Oracle.
Steffen
*** DBD-Oracle-1.12/Oracle.pm Fri Aug 31 18:27:18 2001
--- Oracle.pm Sun Oct 28 15:53:27 2001
***************
*** 442,447 ****
--- 442,578 ----
$sth;
}
+
+ sub column_info {
+ my $dbh = shift;
+ my $attr = ( ref $_[0] eq 'HASH') ? $_[0] : {
+ 'TABLE_SCHEM' => $_[1],'TABLE_NAME' => $_[2],'COLUMN_NAME' => $_[3] };
+ my $Sql = <<'SQL';
+ SELECT *
+ FROM
+ (
+ SELECT to_char( NULL ) TABLE_CAT
+ , tc.OWNER TABLE_SCHEM
+ , tc.TABLE_NAME TABLE_NAME
+ , tc.COLUMN_NAME COLUMN_NAME
+ , 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
+ ) 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 -- ...
+ , 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
+ my @BindVals = ();
+ while ( my ( $k, $v ) = each %$attr ) {
+ if ( $v ) {
+ $Sql .= " AND $k LIKE ?\n";
+ push @BindVals, $v;
+ }
+ }
+ $Sql .= " ORDER BY TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION\n";
+ my $sth = $dbh->prepare( $Sql ) or return undef;
+ $sth->execute( @BindVals ) or return undef;
+ $sth;
+ }
+
sub type_info_all {
my ($dbh) = @_;
my $names = {
***************
*** 903,908 ****
--- 1034,1061 ----
Oracle returns it.
See L</table_info()> for more detailed information.
+ =head2 C<column_info()>
+
+ Oracle does not support catalogs so TABLE_CAT is ignored as
+ selection criterion.
+ The TABLE_CAT field of a fetched row is always NULL (undef).
+ See L</table_info()> for more detailed information.
+
+ The CHAR_OCTET_LENGTH field is (currently) always NULL (undef).
+
+ Don't rely on the values of the BUFFER_LENGTH field!
+ Especially the length of FLOATs may be wrong.
+
+ Datatype codes for non-standard types are subject to change.
+
+ Attention! The DATA_DEFAULT (COLUMN_DEF) column is of type LONG.
+
+ The result set is ordered by TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION.
+
+ An identifier is passed I<as is>, i.e. as the user provides or
+ Oracle returns it.
+ See L</table_info()> for more detailed information.
+
=head1 International NLS / 8-bit text issues
