Home » RDBMS Server » Server Utilities » SQL loader Variable length field was truncated
| SQL loader Variable length field was truncated [message #74759] |
Sun, 13 February 2005 17:59  |
Thomas
Messages: 67
Registered: September 1999
|
Member |
|
|
I try to upload raw data into table
However, the first two characters in a row are truncated and there is warning "Variable length field was truncated".
I would like to know how to resolve this problem.
Thanks
Here is my control file details:
load data
infile 'D:data.dat'
replace
into table APR_MAS_L
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( intext VARCHAR(2000)
)
|
|
|
|
| Re: SQL loader Variable length field was truncated [message #74760 is a reply to message #74759] |
Sun, 13 February 2005 18:31  |
M. Forouzangohar
Messages: 2
Registered: February 2005
|
Junior Member |
|
|
i use sql loader oracle 10g for a big string of numeric characters(or whatever) ,works as below:
load data
infile 'D:data.dat'
replace
into table APR_MAS_L
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( intext
)
and my data is like: "0,102,3,4,2,12,3,0,1,11,1,0,...."
if your Memo or text contains NewLine embedded ,the story differs.
Good Luck
|
|
|
|
 Re: SQL loader Variable length field was truncated [message #110884 is a reply to message #74759] |
Thu, 10 March 2005 16:57 |
ayang_ca
Messages: 2
Registered: March 2005
|
Junior Member |
|
|
Hi,
I'm also experiencing this problem using SQL*Loader: Release 8.1.7.0.0 - Production
Here is my control file (it's actually split into separate control and data files, but the result is the same)
--------------------
LOAD DATA
INFILE *
APPEND INTO TABLE test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(
first_id,
second_id,
third_id,
language_code,
display_text VARCHAR(2000)
)
begindata
2,1,1,"eng","Type of Investment Account"
-----------------------------------------
The TEST table is defined as:
Name Null? Type
-------------------- -------- --------------
FIRST_ID NOT NULL NUMBER(4)
SECOND_ID NOT NULL NUMBER(4)
THIRD_ID NOT NULL NUMBER(4)
LANGUAGE_CODE NOT NULL CHAR(3)
DISPLAY_TEXT VARCHAR2(2000)
QUESTION_BLOB BLOB
The log file displays:
Record 1: Warning on table "USER"."TEST", column DISPLAY_TEXT
Variable length field was truncated.
And the results of the insert are:
FIRST_ID SECOND_ID THIRD_ID LANGUAGE_CODE DISPLAY_TEXT
-------- --------- -------- ------------- ------------
2 1 1 eng ype of Investment Account"
The language_code field is imported correctly, but display_text keeps the closing delimiter, and loses the first character of the string.
I've also tried the following:
---------------------------------------
LOAD DATA
INFILE *
APPEND INTO TABLE bsst221
FIELDS TERMINATED BY '|'
(
first_id,
second_id,
third_id,
language_code,
display_text VARCHAR(2000)
)
begindata
2|1|1|eng|Type of Investment Account
----------------------------------------
In this case, display_text is imported as:
pe of Investment Account
Am I missing something totally obvious in my control and data files? I can't believe that SQL*Loader would have such a glaring problem with delimiters.
Any help would be greatly appreciated!
|
|
|
|
 Re: SQL loader Variable length field was truncated [message #110979 is a reply to message #74759] |
Fri, 11 March 2005 11:46 |
ayang_ca
Messages: 2
Registered: March 2005
|
Junior Member |
|
|
I got a response to this in the OTN forums:
http://forums.oracle.com/forums/thread.jsp?forum=75&thread=293680&tstart=0&trange=15
The solution was to specify the input field as CHAR instead of VARCHAR:
LOAD DATA
INFILE *
APPEND INTO TABLE test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(
first_id,
second_id,
third_id,
language_code,
display_text CHAR(2000) <----- CHANGED THIS
)
begindata
2,1,1,"eng","Type of Investment Account"
|
|
|
|
Goto Forum:
Current Time: Sat Nov 09 02:47:16 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
