3

I built a small query tool for Oracle with CSV export function with an OracleDataAdapter to fill a dataset that is displayed in a DataGrid (WinForms). At first I used .NET types (DateTime, decimal, string,...) but I got overflow exceptions in some cases because of larger precision in Oracle (NUMBER(30,10)). Therefore I had to set the property ReturnProviderSpecificTypes = true in the OracleDataAdapter so now it uses OracleClient (OracleNumber, OracleBoolean, OracleString,...) types instead.

The problem is everything is always localized (on screen and during CSV output) to en-US formats while I'm in nl-BE region (we use comma as decimal separator and point as thousand separator). The column separator in CSV is semicolon btw, so there's no interference with the comma in decimal numbers.

So is there a way to override the ToString method in a way for those types? Some sort of injecting a formatting engine?

Thanks

Koen
  • 3,626
  • 1
  • 34
  • 55

3 Answers3

1

You can use ALTER SESSION SET NLS_.... Look: http://download.oracle.com/docs/cd/B28359_01/olap.111/b28126/dml_options072.htm

There is example of how it works with dot ad decimal point and space as thousand separator (also note datetime setting):

alter session set NLS_NUMERIC_CHARACTERS='. '
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'
Michał Niklas
  • 53,067
  • 18
  • 70
  • 114
  • Works in Oracle SQL developer but not from code. I tried sending this as a separate command before the actual statement but it does not affect anything. The data is still wrapped in those Oracle specific types and their ToString methods do exactly the same... – Koen Nov 22 '10 at 12:48
  • For me it works with ODBC (both from Delphi and Jython via JDBC-ODBC bridge). What technology do you use to connect to Oracle db? – Michał Niklas Nov 22 '10 at 13:42
1

Seems like there is no good generic way to do this. For the CSV output I found a workaround but not for the output on screen.

During output I have to test every value for type OracleNumber (I have to test for INullable anyway) and cast it accordingly. Then there are two options:

// Replace: not clean and could be a heavy operation
writer.Write(myOracleNumber.ToString().Replace('.', ','));

or

// Returns decimal but result is not exact (which is acceptable in my case)
writer.Write(OracleNumber.Round(myOracleNumber, 10).Value);

For output on screen we could use a similar technique and load a new DataTable in memory from the one filled by the OracleDataAdapter but then with .NET CLR types, but since the resultsets could be very large, that could be a dangerously heavy trick to do, so we'll just live with english notation there...

Koen
  • 3,626
  • 1
  • 34
  • 55
0
SQL>select to_char(1000001/3, '999G999G990D999999', 'NLS_NUMERIC_CHARACTERS = '',.''')
from dual;

33.3333,666667

ADDED:

private si As OracleGlobalization; 
si.DateFormat = "YYYY-MM-DD";
conn.SetSessionInfo(si);

See http://cs.felk.cvut.cz/10gr2/win.102/b14307/OracleGlobalizationClass.htm#i1010070 for the possible parameters

Rob van Laarhoven
  • 8,737
  • 2
  • 31
  • 49
  • It's a query tool. Users can input any query. I have no control over the query at all... – Koen Nov 22 '10 at 12:05
  • From the FAQ : Above all, be honest. If you see _misinformation_, vote it down. – Rob van Laarhoven Nov 22 '10 at 12:39
  • The OracleGlobalization class is part of the Oracle Data Access Provider (which I preferred not to use). It will not help anyway because it would do the same thing as first sending the command "alter session set NLS_NUMERIC_CHARACTERS=', '" but I already tested that... – Koen Nov 22 '10 at 17:00