Home » RDBMS Server » Server Utilities » Newbie: SQL*loader: ORA-01722
| Newbie: SQL*loader: ORA-01722 [message #128986] |
Thu, 21 July 2005 07:57  |
joeuser
Messages: 3
Registered: July 2005
|
Junior Member |
|
|
Hi guys
New to oracle, playing around for days with this problem, without any luck finding a solution on the web which works. (Knowhow in SQL from MS SQL, MySQL, Interbase, aso.)
Here is the problem: I am trying to import a CSV file with comma as separator into an oracle table. The file is produced from an old system which will be replaced soon, so it should be possible to import the file as it is, without changing anything, for example the separator.
Here is a simplified version of the table:
CREATE TABLE LOADER_TEST
(
STRING VARCHAR2(25 BYTE),
NUMMER NUMBER(28,14)
)
TABLESPACE TEST
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;
Here is the CSV file (simplified):
abcdef,100.2
abcdef,101.202
abcdef,102.2
abcdef,1033.27
abcdef,10580.2
abcdef,10.402
abcdef,155.25
abcdef,100.225
and here is the control file to load the data:
LOAD DATA
INFILE 'loader_test.dat'
INTO TABLE loader_test
TRUNCATE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
String,
nummer
)
So no rocket science, I thought  but here are the error messages from the log file (it is german, but means invalid number):
...
Satz 1: Abgelehnt - Fehler in Tabelle LOADER_TEST, Spalte NUMMER.
ORA-01722: Ungültige Zahl
Satz 2: Abgelehnt - Fehler in Tabelle LOADER_TEST, Spalte NUMMER.
ORA-01722: Ungültige Zahl
...
Can anyone give me a helping hand on this one, please? I would really appreciate it!
Thanks for reading, Joe
|
|
|
|
| Re: Newbie: SQL*loader: ORA-01722 [message #128998 is a reply to message #128986] |
Thu, 21 July 2005 08:18  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Appreciate your detailed posting with DDL,sampledata and control file. Thank you.
though i cannot reproduce the problem.
Do you have a very different characterset or somthing?>
1 CREATE TABLE LOADER_TEST
2 (
3 STRING VARCHAR2(25 BYTE),
4 NUMMER NUMBER(28,14)
5 )
6 TABLESPACE users
7 PCTUSED 0
8 PCTFREE 10
9 INITRANS 1
10 MAXTRANS 255
11 STORAGE (
12 INITIAL 64K
13 MINEXTENTS 1
14 MAXEXTENTS 2147483645
15 PCTINCREASE 0
16 BUFFER_POOL DEFAULT
17 )
18 LOGGING
19 NOCACHE
20* NOPARALLEL
scott@9i > /
Table created.
scott@9i > !cat loader_test.dat
abcdef,100.2
abcdef,101.202
abcdef,102.2
abcdef,1033.27
abcdef,10580.2
abcdef,10.402
abcdef,155.25
abcdef,100.225
scott@9i > !sqlldr userid=scott/tiger control=somectl.ctl
SQL*Loader: Release 9.2.0.4.0 - Production on Thu Jul 21 09:13:51 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 8
scott@9i > select * from loader_test;
STRING NUMMER
------------------------- ----------
abcdef 100.2
abcdef 101.202
abcdef 102.2
abcdef 1033.27
abcdef 10580.2
abcdef 10.402
abcdef 155.25
abcdef 100.225
8 rows selected.
|
|
|
|
| Re: Newbie: SQL*loader: ORA-01722 [message #129011 is a reply to message #128986] |
Thu, 21 July 2005 09:26 |
joeuser
Messages: 3
Registered: July 2005
|
Junior Member |
|
|
How/Where can I check this? Sorry, totally new to Oracle....
I did now another test, and changed the separater and the decimal separator:
abcdef|100,2
abcdef|101,202
abcdef|102,2
abcdef|1033,27
abcdef|10580,2
abcdef|10,402
abcdef|155,25
abcdef|100,225
If I use it like this, it works without any problem. Looks like the "." is not recognized as a decimal separator.
Thanks for helping me!
btw. I am running Oracle on a Windows 2003 Server box, not on Unix/Linux like you do. But this shouldn't make any difference, right?
|
|
|
|
| Re: Newbie: SQL*loader: ORA-01722 [message #129029 is a reply to message #128986] |
Thu, 21 July 2005 10:54 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Yes, that was an excellent way to ask a question.
The following quote is in chapter 3 of the oracle globalization guide (one of the standard books in the freely available oracle documentation).
| Quote: |
Numeric Formats
The database must know the number-formatting convention used in each session to interpret numeric strings correctly. For example, the database needs to know whether numbers are entered with a period or a comma as the decimal character (234.00 or 234,00). Similarly, applications must be able to display numeric information in the format expected at the client site.
Examples of numeric formats are shown in Table 3-9.
Table 3-9 Examples of Numeric Formats
Country Numeric Formats
Estonia 1 234 567,89
Germany 1.234.567,89
Japan 1,234,567.89
UK 1,234,567.89
US 1,234,567.89
Numeric formats are derived from the setting of the NLS_TERRITORY parameter, but they can be overridden by the NLS_NUMERIC_CHARACTERS parameter.
See Also:
"NLS_TERRITORY"
|
|
|
|
|
| Re: Newbie: SQL*loader: ORA-01722 [message #129163 is a reply to message #128986] |
Fri, 22 July 2005 04:47 |
joeuser
Messages: 3
Registered: July 2005
|
Junior Member |
|
|
Ok, I changed in NLS_DATABASE_PARAMETERS the parameter NLS_NUMERIC_CHARACTERS to ",." which was the other way around before. But still, also after a reboot, it does not work....
Is there anything else I am missing?
|
|
|
|
Goto Forum:
Current Time: Thu Jul 04 04:31:14 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
