Home » RDBMS Server » Server Utilities » SQLLDR Issue with NUMBER data type
SQLLDR Issue with NUMBER data type [message #614324] |
Tue, 20 May 2014 20:28  |
aimy
Messages: 225 Registered: June 2006
|
Senior Member |
|
|
Hi all.
I'm really stuck with this basic issue.
My table:
CREATE TABLE S13018_SQLLDR_DATA (
CATEGORY CHAR(3) NULL,
REGION VARCHAR2(36) NULL,
STATE VARCHAR2(36) NULL,
DISTRICT VARCHAR2(36) NULL,
ZONE_NAME VARCHAR2(36) NULL,
BUILDING_ID VARCHAR2(36) NULL,
TOTAL_COUNT NUMBER(6,0) NULL
)
/
The data
TEL|EASTERN REGION|PAHANG|PH|ZONE TERUNTUM|AAR|5206
TEL|SOUTHERN REGION|JOHOR|JS|ZONE SKUDAI / PONTIAN|AB|395
TEL|SOUTHERN REGION|JOHOR|JS|ZONE SENAI|ABN|229
TEL|SOUTHERN REGION|MELAKA|MK|ZONE MELAKA UTARA|ABU|635
TEL|SOUTHERN REGION|MELAKA|MK|ZONE MELAKA UTARA|AG|2986
TEL|SOUTHERN REGION|JOHOR|JU|ZONE KLUANG/MERSING|AH|1016
My control file is
load data
append
into table s13018_SQLLDR_DATA
fields terminated by "|"
trailing nullcols
(
CATEGORY CHAR(3),
REGION CHAR(36),
STATE CHAR(36),
DISTRICT CHAR(36),
ZONE_NAME CHAR(36),
BUILDING_ID CHAR(36),
TOTAL_COUNT INTEGER EXTERNAL
)
But when I execute the loading, all records were rejected with error:
[/code]Record 1: Rejected - Error on table S13018_SQLLDR_DATA, column TOTAL_COUNT.
ORA-01722: invalid number
Record 2: Rejected - Error on table S13018_SQLLDR_DATA, column TOTAL_COUNT.
ORA-01722: invalid number
Record 3: Rejected - Error on table S13018_SQLLDR_DATA, column TOTAL_COUNT.
ORA-01722: invalid number
Record 4: Rejected - Error on table S13018_SQLLDR_DATA, column TOTAL_COUNT.
ORA-01722: invalid number
Record 5: Rejected - Error on table S13018_SQLLDR_DATA, column TOTAL_COUNT.
ORA-01722: invalid number[/code]
Thank you.
|
|
|
|
|
|
|
|
Re: SQLLDR Issue with NUMBER data type [message #614334 is a reply to message #614324] |
Tue, 20 May 2014 23:20   |
 |
Barbara Boehmer
Messages: 9106 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Your original control file with your posted data and table works for me as well. However, I have seen similar problems before. Typically, there is some sort of invisible control character appended to the number, so that it is not recognized as a number. This frequently happens at the end of a line, due to differences in end of line characters between different operating systems. One method of checking this is to change your data type in your table to varchar2 and in your control file to char, confirm that it loads that way, then use dump to see what was loaded and look for the extra characters. Once you have confirmed that, then you can add code in your control file to trim the characters and put your data types back to number and integer external. This may or may not be the problem, but I strongly suspect it is.
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jul 16 09:31:16 CDT 2025
|