Home » RDBMS Server » Server Utilities » SQL*Loader: Error inserting number with U.S. formatting using modified CHARACTERSET (merged 2) (10G)
|
|
|
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 |
|
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 2265 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 |
|
Michel Cadot
Messages: 68729 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 #526751 is a reply to message #526745] |
Wed, 12 October 2011 15:28 |
|
Barbara Boehmer
Messages: 9102 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;
/
|
|
|
|
|
Goto Forum:
Current Time: Sun Jan 12 17:55:14 CST 2025
|