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

[Jan Doggen]

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

[Sybrand Bakker]

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

[Volker Pohlers]

Jan Doggen schrieb:

you can use the to_number function in ctl-file

example:

options (silent=(discards), skip=1)
load data
characterset WE8PC850
infile "zzkalist.csv" badfile "zzkalist.bad"
replace
into table EXT_ZZKALIST
when (1)<>X'1A'
fields terminated by ';' optionally enclosed by '"'
trailing nullcols
(
FAKTOR NULLIF FAKTOR=BLANKS
"to_number(ltrim(rtrim(:FAKTOR)),'9999999999999D99999',
'NLS_NUMERIC_CHARACTERS = '', '' ')",
)

or - simpler - start sqlload with an batch file and
use
set NLS_NUMERIC_CHARACTERS=,.
before the sql loader command.

best regards,
Volker

[jeff.van]

Sybrand, I used your suggestion on the after logon trigger and it
saved me from several (more) hours of frustration. Thanks!