Skip Menu |
 

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the DBD-Oracle CPAN distribution.

Report information
The Basics
Id: 78700
Status: resolved
Priority: 0/
Queue: DBD-Oracle

People
Owner: Nobody in particular
Requestors: DOUGW [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: 1.47_00
Fixed in:
  • 1.51_00
  • 1.52

Attachments


Subject: column_info reports wrong size for char semantic char type columns
Download (untitled) / with headers
text/plain 147b
Nearly the same as rt 13865 (VARCHAR2 columns with CHAR semantics report COLUMN_SIZE in bytes), but for CHAR type columns that have CHAR semantics.
Download (untitled) / with headers
text/plain 350b
On Tue Jul 31 19:38:40 2012, DOUGW wrote: Show quoted text
> Nearly the same as rt 13865 (VARCHAR2 columns with CHAR semantics report > COLUMN_SIZE in bytes), but for CHAR type columns that have CHAR semantics.
column_info for CHAR type columns with CHAR instead of BYTES report 4 times the actual size. Also, NVARCHAR2 columns report twice the size of the column.
Download (untitled) / with headers
text/plain 4.8k
On Thu Sep 06 14:49:34 2012, DOUGW wrote: Show quoted text
> On Tue Jul 31 19:38:40 2012, DOUGW wrote:
> > Nearly the same as rt 13865 (VARCHAR2 columns with CHAR semantics report > > COLUMN_SIZE in bytes), but for CHAR type columns that have CHAR
semantics. Show quoted text
> > column_info for CHAR type columns with CHAR instead of BYTES report 4 > times the actual size. > > Also, NVARCHAR2 columns report twice the size of the column.
I don't have the tuits to look at this right now - sorry. You could always try fixing it yourself and sending a patch. The code you want is in Oracle.pm (check it out via subversion - see DBD::Oracle pod) and look in lib/DBD/Oracle.pm for column_info. It simply runs the 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 , 'VARCHAR2' , tc.CHAR_LENGTH , 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 where $typecase and $typecasend depend on Oracle version like so: if (ora_server_version($dbh)->[0] >= 8) { $typecase = <<'SQL'; CASE WHEN tc.DATA_TYPE LIKE 'TIMESTAMP% WITH% TIME ZONE' THEN 95 WHEN tc.DATA_TYPE LIKE 'TIMESTAMP%' THEN 93 WHEN tc.DATA_TYPE LIKE 'INTERVAL DAY% TO SECOND%' THEN 110 WHEN tc.DATA_TYPE LIKE 'INTERVAL YEAR% TO MONTH' THEN 107 ELSE SQL $typecaseend = 'END'; } Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 104b
Patch attached. Also added tests to rt13865.t since the change is basically an extension of that ticket.
Subject: DBD-Oracle-1.50.rt78700.patch
--- DBD-Oracle-1.50.old/lib/DBD/Oracle.pm Wed Aug 15 13:15:48 2012 +++ DBD-Oracle-1.50.new/lib/DBD/Oracle.pm Fri Sep 7 14:53:48 2012 @@ -675,6 +675,9 @@ , 'FLOAT' , tc.DATA_PRECISION , 'DATE' , 19 , 'VARCHAR2' , tc.CHAR_LENGTH + , 'CHAR' , tc.CHAR_LENGTH + , 'NVARCHAR2', tc.CHAR_LENGTH + , 'NCHAR' , tc.CHAR_LENGTH , tc.DATA_LENGTH ) COLUMN_SIZE , decode( tc.DATA_TYPE diff -r -u DBD-Oracle-1.50.old/t/rt13865.t DBD-Oracle-1.50.new/t/rt13865.t --- DBD-Oracle-1.50.old/t/rt13865.t Wed Aug 15 13:15:48 2012 +++ DBD-Oracle-1.50.new/t/rt13865.t Fri Sep 7 15:27:12 2012 @@ -17,7 +17,7 @@ plan skip_all => q{requires permissions 'CREATE TABLE' and 'DROP TABLE'}; } -plan tests => 5; +plan tests => 9; $dbh->do( 'DROP TABLE RT13865' ); @@ -29,7 +29,11 @@ COL_DECIMAL NUMBER(9,2), COL_FLOAT FLOAT(126), COL_VC2 VARCHAR2(67), - COL_VC2_69CHAR VARCHAR2(69 CHAR) + COL_VC2_69CHAR VARCHAR2(69 CHAR), + COL_NVC2 NVARCHAR2(69), + COL_NC NCHAR(69), + COL_CHAR CHAR(67), + COL_CHAR_69CHAR CHAR(69 CHAR) ) END_SQL @@ -54,7 +58,22 @@ is $col_h->fetchrow_hashref->{COLUMN_SIZE} => 69, "VARCHAR(69)"; +$col_h = $dbh->column_info( undef, undef, 'RT13865', 'COL_NVC2' ); +is $col_h->fetchrow_hashref->{COLUMN_SIZE} => 69, + "NVARCHAR2(69)"; +$col_h = $dbh->column_info( undef, undef, 'RT13865', 'COL_NC' ); +is $col_h->fetchrow_hashref->{COLUMN_SIZE} => 69, + "NCHAR(69)"; + +$col_h = $dbh->column_info( undef, undef, 'RT13865', 'COL_CHAR' ); +is $col_h->fetchrow_hashref->{COLUMN_SIZE} => 67, + "CHAR(67)"; + +$col_h = $dbh->column_info( undef, undef, 'RT13865', 'COL_CHAR_69CHAR' ); +is $col_h->fetchrow_hashref->{COLUMN_SIZE} => 69, + "CHAR(69)"; + $dbh->do( 'DROP TABLE RT13865' ); # utility functions
Download (untitled) / with headers
text/plain 264b
On Fri Sep 07 18:33:42 2012, DOUGW wrote: Show quoted text
> Patch attached. Also added tests to rt13865.t since the change is > basically an extension of that ticket.
Thank you. Applied to subversion trunk and will be in the next release. Martin -- Martin J. Evans Wetherby, UK


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.