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

On Wed, 28 Dec 2005 10:51:37 +0100, "Jan Doggen" <j.doggen_at_BLOCKqsa.nl> wrote:

>Hello all
>
>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
>

after logon trigger
the trigger queries
select program from v$session where
audsid=sys_context('userenv','sessionid');

if the program column returns 'sqlloader' (or sqlloader.exe) execute immediate 'alter session set nls_numeric_characters= ... '

--
Sybrand Bakker, Senior Oracle DBA