How to format OracleNumber in general

Question

I built a small query tool for Oracle with the CSV export function using OracleDataAdapter to populate the dataset that appears in the DataGrid (WinForms). At first I used .NET types (DateTime, decimal, string, ...), but in some cases I got overflow exceptions due to the greater precision in Oracle ( NUMBER(30,10)). Therefore, I had to set the property ReturnProviderSpecificTypes = truein OracleDataAdapter, so now it uses the OracleClient types (OracleNumber, OracleBoolean, OracleString, ...).

The problem is that everything is always localized (on the screen and during CSV output) in formats in the USA, and I'm in the nl-BE area (we use a comma as a decimal separator and specify it as a thousands separator). The column separator in CSV is a semicolon, so there is no decimal point comma interference.

So, is there a way to override the ToString method this way for these types? Some kind of formatting injection?

thank

Answer 1

There seems to be no good general way to do this. For CSV output, I found a workaround, but not for screen output.

OracleNumber ( INullable) . :

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

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

DataTable , OracleDataAdapter, .NET CLR, , , , ...

Answer 2

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

, dot ad ( ):

alter session set NLS_NUMERIC_CHARACTERS='. '
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'

Answer 3

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 possible parameters