Home » RDBMS Server » Server Utilities » nls_numeric_characters
nls_numeric_characters [message #187020] Thu, 10 August 2006 09:44 Go to next message
bibber
Messages: 38
Registered: August 2006
Member
Hi, I have to insert a lot of data from a csv-file. It contains xyz data, looking like this: 135744.66 484625.81 6.31

So, they are decimal numbers, separated by whitespace. The problem is that our database is expecting comma as a decimal character and these files has a period. Can a change the nls_numeric_characters, only for the duration of sql loader import without having to make changes on the server? (tell sql loader which decimal character to expect, so the database will change it into comma...)

thnx
Re: nls_numeric_characters [message #187127 is a reply to message #187020] Fri, 11 August 2006 00:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
You can replace it in your control file:

col_name "replace (:col_name, '.', ',')"

Re: nls_numeric_characters [message #187140 is a reply to message #187127] Fri, 11 August 2006 01:58 Go to previous messageGo to next message
bibber
Messages: 38
Registered: August 2006
Member
where needs it to be inserted in the controlefile?

load data
truncate
into table test2
fields terminated by whitespace
trailing nullcols
  (
    x  decimal external,
    y  decimal external,
    z  decimal external
   ) 
Re: nls_numeric_characters [message #187239 is a reply to message #187140] Fri, 11 August 2006 07:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
load data
truncate
into table test2
fields terminated by whitespace
trailing nullcols
  (
    x  "replace (:x, '.', ',')",
    y  "replace (:y, '.', ',')",
    z  "replace (:z, '.', ',')" 
   ) 



Re: nls_numeric_characters [message #187736 is a reply to message #187020] Tue, 15 August 2006 05:10 Go to previous message
bibber
Messages: 38
Registered: August 2006
Member
Many thanks Barbara! It saved a lot of time...
Previous Topic: Using VARCHAR in external table field list results in "field too long for datatype"
Next Topic: insert records via dump
Goto Forum:
  


Current Time: Sat Dec 03 12:09:43 CST 2016

Total time taken to generate the page: 0.08910 seconds