Home » RDBMS Server » Server Utilities » SQL*Loader: Error inserting number with U.S. formatting using modified CHARACTERSET (merged 2) (10G)
icon4.gif  SQL*Loader: Error inserting number with U.S. formatting using modified CHARACTERSET (merged 2) [message #526690] Wed, 12 October 2011 09:22 Go to next message
mnowitz
Messages: 5
Registered: October 2011
Location: phoenix, az
Junior Member
Some background first: I am importing data using sqlldr that contains Swedish characters. Since the language environment variable on the server is in English (en_US.UTF-8) the Swedish letters are not being displayed properly. Unfortunately, the default language variable on the server cannot be modified due to other processes that expect en_US.UTF-8. To workaround this, I modified the control file adding 'CHARACTERSET WE8ISO8859P1'. The Swedish letters now display properly Razz ... Unfortunately, I have another issue Confused . The number values which are being generated from ERP are in English format (and not easily modified). For example: 1.1. The load process now expects the Swedish format 1,1 so the records are being rejected with an error of 'invalid number'. I have verified the issue by changing the number to 1,1 and the load succeeded.

Please help! Is there a way within the control file to convert fields with numbers from English to Swedish (. --> ,)?

Thanks in advance.

marcelle from stockholm

[Updated on: Wed, 12 October 2011 10:16]

Report message to a moderator

Re: SQL*Loader: Error inserting number with U.S. formatting using modified CHARACTERSET (merged 2) [message #526712 is a reply to message #526690] Wed, 12 October 2011 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 66851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/mv/msg/174249/521011/102589/#msg_521011

Regards
Michel
Re: SQL*Loader: Error inserting number with U.S. formatting using modified CHARACTERSET (merged 2) [message #526726 is a reply to message #526712] Wed, 12 October 2011 12:09 Go to previous messageGo to next message
mnowitz
Messages: 5
Registered: October 2011
Location: phoenix, az
Junior Member
Thanks Michael. Funny because I was just reading that other post too... Appreciate your quick response! Will try and let you know Razz
Re: SQL*Loader: Error inserting number with U.S. formatting using modified CHARACTERSET (merged 2) [message #526743 is a reply to message #526712] Wed, 12 October 2011 14:23 Go to previous messageGo to next message
mnowitz
Messages: 5
Registered: October 2011
Location: phoenix, az
Junior Member
Hi again,

Unfortunately that syntax "TO_NUMBER (REPLACE (:sell_units, ' ', ''), '999G999G999C', 'NLS_NUMERIC_CHARACTERS=''.,'' NLS_ISO_CURRENCY=''AMERICA''')" is not working. I have included all three files (.txt, .log & .ctl) so you can have a closer look. I could only attach one though so I attached the control file. The records with a . in the sell_units field are causing the problem. The datatype in the database for this field is number(18,5). Perhaps the characterset WE8ISO8859P1 is overriding everything (including the to_number)? Thanks again for your help.

Text File Contents
ACTION_TYPE|EXT_PRODUCT_ID|SHORT_NAME|LONG_NAME|L1_NUMBER|L1_DESCRIPTION|L2_NUMBER|L2_NAME|L3_NUMBER|L3_NAME|L4_NUMBER|L4_NAME|L5_NUM BER|L5_NAME|L6_NUMBER|L6_NAME|L7_NUMBER|L7_NAME|L8_NUMBER|L8_NAME|L9_NUMBER|L9_NAME|L10_NUMBER|L10_NAME|EXT_BRAND_ID|BRAND_NAME|EXT_M FG_ID|MANUFACTURER_NAME|SKU_NUMBER|EXT_PRICE_FAMILY_ID|EXT_PRODUCT_LINE_ID|EXT_DEMAND_GROUP_ID|SELL_UNITS|SELL_UOM|ISDISCONTINUED|DAT E_DISCONTINUED|STYLE|COLOR|SIZE|ISREGULAR|ISMARKDOWN|ISADPLANNING
A|888888882/ST|Ännu en egengjord sås|HEM SÅS 3P HOLLANDAISE|10|SPECERIER|1008|SMAKSÄTTNING/VÅT OCH TORR SÅS|100818|SÅS TILL KÖTT/FISK/FÅGEL|||||||||||||||HEMKÖP|HEMKÖP|||7311041023174||||0.078|KG|0|||||1|0|0
A|888888883/ST|Öbornas sås|HEM SÅS 3P CAFE DE PARIS|10|SPECERIER|1008|SMAKSÄTTNING/VÅT OCH TORR SÅS|100818|SÅS TILL KÖTT/FISK/FÅGEL|||||||||||||||HEMKÖP|HEMKÖP|||7311041023143||||0.102|KG|0|||||1|0|0

Log File Content

SQL*Loader: Release 10.2.0.1.0 - Production on Wed Oct 12 20:42:11 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Control File: product.ctl
Character Set WE8ISO8859P1 specified for all input.

Data File: product.txt
Bad File: product.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 500000
Continuation: none specified
Path used: Direct

Table IMP_PRODUCT, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ACTION_TYPE FIRST * | O(") CHARACTER
SQL string for column : "upper(trim(:ACTION_TYPE))"
EXT_PRODUCT_ID NEXT * | O(") CHARACTER
SQL string for column : "trim(:EXT_PRODUCT_ID)"
SHORT_NAME NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:SHORT_NAME))"
LONG_NAME NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:LONG_NAME))"
L1_NUMBER NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L1_NUMBER))"
L1_NAME NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L1_NAME))"
L2_NUMBER NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L2_NUMBER))"
L2_NAME NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L2_NAME))"
L3_NUMBER NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L3_NUMBER))"
L3_NAME NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L3_NAME))"
L4_NUMBER NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L4_NUMBER))"
L4_NAME NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L4_NAME))"
L5_NUMBER NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L5_NUMBER))"
L5_NAME NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L5_NAME))"
L6_NUMBER NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L6_NUMBER))"
L6_NAME NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L6_NAME))"
L7_NUMBER NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L7_NUMBER))"
L7_NAME NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L7_NAME))"
L8_NUMBER NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L8_NUMBER))"
L8_NAME NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L8_NAME))"
L9_NUMBER NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L9_NUMBER))"
L9_NAME NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L9_NAME))"
L10_NUMBER NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L10_NUMBER))"
L10_NAME NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:L10_NAME))"
EXT_BRAND_ID NEXT * | O(") CHARACTER
SQL string for column : "trim(:EXT_BRAND_ID)"
BRAND_NAME NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:BRAND_NAME))"
EXT_MFG_ID NEXT * | O(") CHARACTER
SQL string for column : "trim(:EXT_MFG_ID)"
MANUFACTURER_NAME NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:MANUFACTURER_NAME))"
SKU_NUMBER NEXT * | O(") CHARACTER
SQL string for column : "trim(:SKU_NUMBER)"
EXT_PRICE_FAMILY_ID NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:EXT_PRICE_FAMILY_ID))"
EXT_PRODUCT_LINE_ID NEXT * | O(") CHARACTER
SQL string for column : "trim(:EXT_PRODUCT_LINE_ID)"
EXT_DEMAND_GROUP_ID NEXT * | O(") CHARACTER
SQL string for column : "trim(:EXT_DEMAND_GROUP_ID)"
SELL_UNITS NEXT * | O(") CHARACTER
SQL string for column : "TO_NUMBER(REPLACE(:SELL_UNITS, ' ', ''),'999G999G999C','NLS_NUMERIC_CHARACTERS=''.,'' NLS_ISO_CURRENCY=''AMERICA''')"
SELL_UOM NEXT * | O(") CHARACTER
SQL string for column : "trim(:SELL_UOM)"
ISDISCONTINUED NEXT * | O(") CHARACTER
SQL string for column : "trim(:ISDISCONTINUED)"
DATE_DISCONTINUED NEXT * | O(") DATE MM/DD/YYYY
NULL if DATE_DISCONTINUED = BLANKS
STYLE NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:STYLE))"
COLOR NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:COLOR))"
ITEM_SIZE NEXT * | O(") CHARACTER
SQL string for column : "upper(trim(:ITEM_SIZE))"
ISREGULAR NEXT * | O(") CHARACTER
SQL string for column : "trim(:ISREGULAR)"
ISMARKDOWN NEXT * | O(") CHARACTER
SQL string for column : "trim(:ISMARKDOWN)"
ISADPLANNING NEXT * | O(") CHARACTER
SQL string for column : "trim(:ISADPLANNING)"

Record 1: Rejected - Error on table IMP_PRODUCT.
ORA-00604: error occurred at recursive SQL level 1
ORA-01722: invalid number

Record 2: Rejected - Error on table IMP_PRODUCT.
ORA-00604: error occurred at recursive SQL level 1
ORA-01722: invalid number


Table IMP_PRODUCT:
0 Rows successfully loaded.
2 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 500000

Total logical records skipped: 1
Total logical records read: 2
Total logical records rejected: 2
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 4
Total stream buffers loaded by SQL*Loader load thread: 0

Run began on Wed Oct 12 20:42:11 2011
Run ended on Wed Oct 12 20:42:11 2011

Elapsed time was: 00:00:00.15
CPU time was: 00:00:00.04


  • Attachment: product.ctl
    (Size: 2.30KB, Downloaded 701 times)
Re: SQL*Loader: Error inserting number with U.S. formatting using modified CHARACTERSET (merged 2) [message #526745 is a reply to message #526743] Wed, 12 October 2011 14:28 Go to previous messageGo to next message
Michel Cadot
Messages: 66851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Unfortunately that syntax "TO_NUMBER (REPLACE (:sell_units, ' ', ''), '999G999G999C', 'NLS_NUMERIC_CHARACTERS=''.,'' NLS_ISO_CURRENCY=''AMERICA''')" is not working.

This expression was specific to the question.
You have to adapt it for your case.
You don't need the currency abd C format element, it is not present in your data.
You don't need G format element, it is not present in your data.
You don't need REPLACE there is nothing to replace in your data.
Just set the correct NLS_NULMERIC_CHARS and give a correct format mask for your data.

Regards
Michel
Re: SQL*Loader: Error inserting number with U.S. formatting using modified CHARACTERSET (merged 2) [message #526750 is a reply to message #526745] Wed, 12 October 2011 15:21 Go to previous messageGo to next message
mnowitz
Messages: 5
Registered: October 2011
Location: phoenix, az
Junior Member
Thank you so so much! With your guidance I was able to determine the correct syntax and successfully load the file Smile Now I can rest easy...


For the benefit of other users in the forum, here is the syntax used:

SELL_UNITS "TO_NUMBER(:SELL_UNITS,'99999.99999','NLS_NUMERIC_CHARACTERS='',.''')"
Re: SQL*Loader: Error inserting number with U.S. formatting using modified CHARACTERSET (merged 2) [message #526751 is a reply to message #526745] Wed, 12 October 2011 15:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
As Michel said, you need to use the correct format, probably something like:

"TO_NUMBER (:sell_units, '9999999999D9999999999', 'NLS_NUMERIC_CHARACTERS=''.,''')"

or

"TO_NUMBER (:sell_units, '9999999999D9999999999', 'NLS_NUMERIC_CHARACTERS='',.''')"

Another option would be to change the nls_numeric_characters in a batch file and run SQL*Loader from that same batch file. On Windows, I can create an operating system batch file (test.bat) containing the following two lines, with no spaces or quotes around characters=,. like so:

set nls_numeric_characters=,.
sqlldr scott/tiger control=test.ctl log=test.log

or

set nls_numeric_characters=.,
sqlldr scott/tiger control=test.ctl log=test.log

then run that batch file either from the operating system or from SQL*Plus like:

host test.bat

If you are not using Windows, you should be able to do something similar in whatever operating system you are using.

Another option would be to create a trigger to detect that SQL*Loader is being run and change the nls_numeric_characters, then run SQL*Loader, then drop the trigger. I have provided an example of such a trigger below.

create or replace trigger after_logon_if_sqlldr
  after logon on database
declare
  v_program  varchar2(64);
begin
  select program
  into   v_program
  from   v$session
  where  audsid = sys_context ('userenv', 'sessionid');
  if v_program = 'sqlldr.exe' then
    -- use one or the other of the below, not both
    execute immediate 'alter session set nls_numeric_characters = ''.,''';  
    -- or:
    execute immediate 'alter session set nls_numeric_characters = '',.''';  
  end if;
end after_logon_if_sqlldr;
/ 

Re: SQL*Loader: Error inserting number with U.S. formatting using modified CHARACTERSET (merged 2) [message #526752 is a reply to message #526751] Wed, 12 October 2011 15:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
I see that you solved your problem while I was posting. I will leave the other options there for future reference for anyone who might be searching for the same problem. Some people find it easier to use a batch file than to use to_number on every numeric field. Also, I have not found a way to use the to_number on varrays, such as when trying to load Oracle spatial data into a column of type mdsys.sdo_geometry. One final alternative is to load into a staging table or use an external table, then insert into the target table from the staging or external table.

Re: SQL*Loader: Error inserting number with U.S. formatting using modified CHARACTERSET (merged 2) [message #526753 is a reply to message #526752] Wed, 12 October 2011 15:40 Go to previous message
mnowitz
Messages: 5
Registered: October 2011
Location: phoenix, az
Junior Member
Hi Barbara, thanks for your response too. This is being run on a Linux server (and not via command line). Unfortunately, neither a batch file nor a shell script can be used. But I think a good solution was determined and luckily there are not that many numeric fields to specify.

Cheers from Stockholm!

[Updated on: Wed, 12 October 2011 15:48]

Report message to a moderator

Previous Topic: IMP-00018: partial import of previous table completed
Next Topic: Excel Spreadsheet - SQLLDR - New Line Issues
Goto Forum:
  


Current Time: Wed Feb 19 13:01:04 CST 2020