3

I am using a Control File to import data from a CSV file into a table, using sqlldr to import the data, where a large number of fields are numeric values with integer and decimal part separated by dots. The Control File works just fine because I cast the number to the input my table expects by using

"TO_NUMBER(:MyVariable, '9999999999D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')"

Although the control file works just fine I would like to know: Is it possible to use: 

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ","

In this way I don't mind about differences between character used in NLS and the one used to create the file, nor i mind about the actual number of decimal significative number.

eckes
  • 1,456
  • 10
  • 18
gRomano
  • 31
  • 1
  • 3

2 Answers2

1

I had the same question and I find a solution that works for me!

You have to put the sentence set NLS_NUMERIC_CHARACTERS=,. in the beginning of the .bat file. This must be before you call the load in the .ctl file.

Nacho Robayna
  • 11
  • 1
0

If you are certain the decimal separator is dot, you can directly use it in format pattern, for example

"TO_NUMBER(:MyVariable, '9999999999.999999')"

I was solving similar problem and found this is simpler solution. (In my case I generate content of .ctl file from SQL query to all_tab_cols table where data_precision and data_scale - if present - determine number of 9s in format pattern.)

Tomáš Záluský
  • 101
  • 1