| External Table and Blank spaces [message #652532] |
Mon, 13 June 2016 01:49  |
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
|
Senior Member |
|
|
Hi,
I have a table called material_number with material_number of varchar2(18).
I have a text file with data. Below is the script I used to load the data.
But I got few records not loaded in to the table saying the log as value is too large for column.
Attached is my text file for your reference also added the log details in quote.
Request your help to resolve this issue.
create table mat_num
(
MATERIAL_NUMBER varchar2(18))
organization external
(type oracle_loader
default directory MIGRATION_DIR
access parameters
(
records delimited BY '\n'
BADFILE MIGRATION_DIR:'mat_num_xt.bad'
LOGFILE MIGRATION_DIR:'mat_num_xt.log'
NODISCARDFILE
CHARACTERSET UTF8
STRING SIZES ARE IN CHARACTERS
READSIZE 134217728
SKIP 1
fields optionally enclosed by '"' LRTRIM
missing field values are null
(
MATERIAL_NUMBER CHAR(4000)
)
)
location ('mat_#.txt')
)
parallel 2
reject limit unlimited
nomonitoring;
LOG file opened at 06/13/16 02:37:58
Field Definitions for table MAT_NUM
Record format DELIMITED, delimited by
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
MATERIAL_NUMBER CHAR (4000)
Trim whitespace from left and right
error processing column MATERIAL_NUMBER in row 266 for datafile /var/opt/oracle/prsdata/mat_#.txt
ORA-12899: value too large for column MATERIAL_NUMBER (actual: 20, maximum: 24)
Thanks for your support.
-
Attachment: mat_#.txt
(Size: 20.89KB, Downloaded 1220 times)
|
|
|
|
|
|
|
|
|
|
|
|
| Re: External Table and Blank spaces [message #652539 is a reply to message #652534] |
Mon, 13 June 2016 02:56  |
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
|
Senior Member |
|
|
Hi,
for the below two records if I changed the size to varchar2(24) I'm able to insert those records.
But again one record is failing which is captured in the bad file and the size of the rejected record is comes within the varchar2(24) limit.
And unable to find where it execeeds the size.
Request your help.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: External Table and Blank spaces [message #652604 is a reply to message #652584] |
Tue, 14 June 2016 09:55 |
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
|
Senior Member |
|
|
Hi Mike,
Many thanks for your guidance. It works well.
I'm now able to load the data with the original size with your inputs.
Between that how did you find the ctl spaces?
As I have connected to VPN and couldn't check the forum from last day, apologies for late reply.
Thanks.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
