ORA-01722 in SQL loader (decimal sign) - elegant way to solve this?

I run into the famous 'invalid number' error when importing a text file with SQL Loader. The text file contains decimal numbers with a period in them. I already do an ALTER SESSION SET NLS_NUMERIC_CHARACTERS=".," in my app to set the proper decimal separator (so that my SQL statements work), but obviously this does not work when my app calls SQL loader. SQL loader uses the global setting from the registry (there's a NLS_LANG setting there: DUTCH_THE NETHERLANDS.WE8MSWIN1252, no specific NLS_NUMERIC_CHARACTERS. If I do a SELECT * FROM NLS_SESSION_PARAMETERS I see that NLS_NUMERIC_CHARACTERS=",." which explains the problem).

My app has full control over the text file and the CTL file. I cannot change anything to the machine settings, it's not mine ;-)

What can I do here so that it reliably works on any machine? I could do SELECT * FROM NLS_SESSION_PARAMETERS and then write the text file with the decimal separator returned in NLS_NUMERIC_CHARACTERS, but isn't there a more elegant way?

TIA
Jan Received on Wed Dec 28 2005 - 10:51:37 CET