Problem with numeric in Oracle

Code patches written by our users to solve certain "problems" that were not solved, yet.

Moderators: gto, cipto_kh, EgonHugeist, mdaems

DR59
Fresh Boarder
Fresh Boarder
Posts: 2
Joined: 18.03.2009, 13:20

Problem with numeric in Oracle

Postby DR59 » 21.10.2009, 09:02

We had a problem when declaric a table like
[font=Courier New]
create table test (
val1 numeric,
val2 numeric);

[/font]using val1 for integer values and val2 for floats.
like
[font=Courier New]
val1 val2
1 1.12
2 123.45
[/font]

If you do a select like

select * from test

retruning
1 - 1
2 - 123

while doing it like

select val2 from test

returns the expexted values.

We figured out that it is a problem in ZDbcOracleMetadata.pas where the SQL:

[font=Courier New] SQL := 'SELECT NULL, OWNER, TABLE_NAME, COLUMN_NAME, NULL, DATA_TYPE,'
+ ' DATA_LENGTH, NULL, DATA_PRECISION, DATA_SCALE, NULLABLE, NULL,'
+ ' DATA_DEFAULT, NULL, NULL, NULL, COLUMN_ID, NULLABLE'
+ ' FROM SYS.ALL_TAB_COLUMNS'
+ ' WHERE OWNER LIKE ''' + ToLikeString(SchemaPattern) + ''' AND TABLE_NAME LIKE '''
+ ToLikeString(TableNamePattern) + ''' AND COLUMN_NAME LIKE '''
+ ToLikeString(ColumnNamePattern) + '''';
[/font]
returns 0 for Integers and NULL for numeric without specification instead of the defaults (38,12) for numeric in Oracle.
We solved it by replacing the SQL with:
[font=Courier New] SQL := 'SELECT NULL, OWNER, TABLE_NAME, COLUMN_NAME, NULL, DATA_TYPE,'
+ ' DATA_LENGTH, NULL,'
+ ' case '
+ ' when DATA_PRECISION is NULL and DATA_SCALE is NULL and DATA_TYPE=''NUMBER'' then 38'
+ ' else DATA_PRECISION'
+ ' end as DATA_PRECISION,'
+ ' case '
+ ' when DATA_PRECISION is NULL and DATA_SCALE is NULL and DATA_TYPE=''NUMBER'' then 12'
+ ' else DATA_SCALE'
+ ' end as DATA_SCALE,'
+ ' NULLABLE, NULL,'
+ ' DATA_DEFAULT, NULL, NULL, NULL, COLUMN_ID, NULLABLE'
+ ' FROM SYS.ALL_TAB_COLUMNS'
+ ' WHERE OWNER LIKE ''' + ToLikeString(SchemaPattern) + ''' AND TABLE_NAME LIKE '''
+ ToLikeString(TableNamePattern) + ''' AND COLUMN_NAME LIKE '''
+ ToLikeString(ColumnNamePattern) + '''';
[/font]

I hope we can help others having similar problems.

Return to “User Patches”

Who is online

Users browsing this forum: No registered users and 1 guest