 "field in datafile exceeds maximum length" for number field [message #132291] |
Thu, 11 August 2005 14:24  |
pswathi26@rediffmail.com
Messages: 4
Registered: August 2005
|
Junior Member |
|
|
Hello,
I am trying to import data from a txt file into oracle using sql*loader. After i execute the sqlldr with the control file and oracle username/password, i am getting an error which I am able to see in the log file as
"field in datafile exceeds maximum length"
I am getting the above error for a field ID and datatype is NUMBER.My data in that field does not contain more than 4 digit numbers. I have specified my table structure in oracle as ID
number(19,0).However, when I am trying to load the datafile into oracle I am getting this error
"field in datafile exceeds maximum length"
Please help.
Thanks
|
|
|
|
|
|
| Re: "field in datafile exceeds maximum length" for number field [message #132295 is a reply to message #132291] |
Thu, 11 August 2005 14:57  |
pswathi26@rediffmail.com
Messages: 4
Registered: August 2005
|
Junior Member |
|
|
Hi Mahesh,
Thanks for the help. It worked. Now, I have an other problem. I have a field called MEMBER_ID which is of type varchar. As per the data that I have I don't have any null values in that particular column. However I am getting this error while I am trying to load the data into oracle
"column not found before end of record(use TRAILING NULLCOLS)"
I am not sure what this error means and what I should do to overcome this problem.
Can you help me..I appreciate your help
Thanks
|
|
|
|
|
|
| Re: "field in datafile exceeds maximum length" for number field [message #132299 is a reply to message #132291] |
Thu, 11 August 2005 16:35 |
pswathi26@rediffmail.com
Messages: 4
Registered: August 2005
|
Junior Member |
|
|
Hi Mahesh,
Thanks.It worked.
Sorry to bother you, but I have an other problem regarding my first post on this particular topic, I changed the datatype of the field ID to CHAR(2000) from NUMBER.
However, now I am getting the error
Rejected - error on table xyz, column ID
ora-01722:invalid number
I searched for this error on web, and It has to do with the datatype. So what should I do now? If I change it back to number, I get the previous error "field in datafile exceeds maximum length" and if I don't change then I am getting "invalid number"
Please help!!
Thanks
|
|
|
|
|
|
| Re: "field in datafile exceeds maximum length" for number field [message #132580 is a reply to message #132291] |
Sun, 14 August 2005 22:17 |
pswathi26@rediffmail.com
Messages: 4
Registered: August 2005
|
Junior Member |
|
|
Hi Mahesh,
The process that I followes is:
1. I imported a table called AQUALIFIER into ACCESS whose table structures are given below
The table structure in oracle used to import into access and also to import data from a text file:
AQUALIFIER:
ID NUMBER(19,0)
M_ID VARCHAR2(255)
PERF_IN NUMBER(10,0)
DIM_ID VARCHAR2(255)
PERF_LINE NUMBER(19,0)
Q_ID NUMBER(19,0)
Q_INDEX NUMBER(10,0)
The access table that I am using to export data into a text file is given below
AQUALIFIER:
ID NUMBER(19,0)--DECIMAL
M_ID TEXT(255)
PERF_IN NUMBER(10,0)--DECIMAL
DIM_ID TEXT(255)
PERF_LINE NUMBER(19,0)--DECIMAL
Q_ID NUMBER(19,0)--DECIMAL
Q_INDEX NUMBER(10,0)--DECIMAL
2. I exported the data in Access into a text file which is attached to this post.Since the Access table structures has decimal in the field size property I used the sprcified format to export into the text file.
3. Then from this text file, I am trying to import data into oracle which the same above table structure(first structure )
The control file that I am using for the sql*loader is given below
LOAD DATA
INFILE 'AQUALIFIER.TXT'
BADFILE 'AQUALIFIER.BAD'
INSERT INTO TABLE AQUALIFIER
fields terminated by ',' optionally enclosed by '"' trailing nullcols
(ID, M_ID, PERF_INDEX, DIM_ID, PERF_LINE, Q_ID,Q_INDEX)
Please let me know if I did anything wrong.
Thanks once again
|
|
|
|
| Re: "field in datafile exceeds maximum length" for number field [message #132586 is a reply to message #132580] |
Mon, 15 August 2005 00:03 |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You do not have any commas or double quotes, so you should not be using:
fields terminated by ',' optionally enclosed by '"'
Your enclosing characcter is | so you should be using:
fields terminated by whitespace optionally enclosed by '|'
You also need to add a when clause to only load the rows that begin with | and not the rows of hyphens.
You have also used perf_index in your control file, but you have perf_in in your table.
So, your corrected SQL*Loader control file would be:
LOAD DATA
INFILE 'AQUALIFIER.TXT'
BADFILE 'AQUALIFIER.BAD'
INSERT INTO TABLE AQUALIFIER
WHEN 1:1 = '|'
fields terminated by whitespace optionally enclosed by '|'
(ID, M_ID, PERF_IN, DIM_ID, PERF_LINE, Q_ID,Q_INDEX)
|
|
|
|
 Re: "field in datafile exceeds maximum length" for number field [message #132604 is a reply to message #132586] |
Mon, 15 August 2005 02:39 |
a_mir_ahsan
Messages: 1
Registered: August 2005
Location: hyderabad
|
Junior Member |
|
|
| Barbara Boehmer wrote on Mon, 15 August 2005 00:03 |
You do not have any commas or double quotes, so you should not be using:
fields terminated by ',' optionally enclosed by '"'
Your enclosing characcter is | so you should be using:
fields terminated by whitespace optionally enclosed by '|'
You also need to add a when clause to only load the rows that begin with | and not the rows of hyphens.
You have also used perf_index in your control file, but you have perf_in in your table.
So, your corrected SQL*Loader control file would be:
LOAD DATA
INFILE 'AQUALIFIER.TXT'
BADFILE 'AQUALIFIER.BAD'
INSERT INTO TABLE AQUALIFIER
WHEN 1:1 = '|'
fields terminated by whitespace optionally enclosed by '|'
(ID, M_ID, PERF_IN, DIM_ID, PERF_LINE, Q_ID,Q_INDEX)
|
:x
|
|
|
|
| Re: "field in datafile exceeds maximum length" for number field [message #136179 is a reply to message #132291] |
Tue, 06 September 2005 09:22 |
arrufus
Messages: 1
Registered: September 2005
Location: Lagos
|
Junior Member |
|
|
The problem occurs because the default size for sqlldr is char(255). Find the column(s) you suspect might be very large and specify in the ctl file
COL1 CHAR(500) NULLIF COL1=BLANKS,
COL2 CHAR(500) NULLIF COL2=BLANKS,
where COL1 and COL2 are the columns that are wider than the default 255 xters.
Hope this works.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
