Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> SQL*Loader - Variable length field was truncated
Hi,
I am trying to load data from delimited file to Oracle via SQL*Loader.
It gives me warning "Record 1: Warning on table <my_table>, column
SYNOPSIS Variable length field was truncated.".
All rows from the data file get loaded and that SYNOPSIS field gets
truncated.
In my table it is a CLOB field where large piece of text stored.
Is there anything wrong in my SQL*Loader control file?
Thank you
J
OPTIONS (bindsize=19021900, readsize=19021900, errors=10000)
LOAD DATA
INFILE 'update_file.dat' "str X'7c0a'"
APPEND
INTO TABLE MYTABLE
FIELDS TERMINATED by X'09' ENCLOSED BY '^'
(
pkey SEQUENCE(MAX,1), -- auto-increment primary key change_date SYSDATE, -- set date to today media_id CHAR(4000) NULLIF media_id=BLANKS, title CHAR(4000) NULLIF title=BLANKS, places CHAR(4000) NULLIF places=BLANKS, personalities CHAR(4000) NULLIF personalities=BLANKS, reporters CHAR(4000) NULLIF reporters=BLANKS, subjects CHAR(4000) NULLIF subjects=BLANKS, hit_time CHAR(4000) NULLIF hit_time=BLANKS, "LENGTH" CHAR(4000) NULLIF "LENGTH"=BLANKS, dates CHAR(4000) NULLIF dates=BLANKS, assignments CHAR(4000) NULLIF assignments=BLANKS, media_type CHAR(4000) NULLIF media_type=BLANKS, synopsis VARCHAR(1000000) NULLIF synopsis=BLANKS)
Here is what it says in the log file:
(in this example I am trying to load one row where SYNOPSIS field is
3920 characters long)
...
Number to load: ALL Number to skip: 0 Errors allowed: 10000 Bind array: 64 rows, maximum of 19021900 bytes Continuation: none specified Path used: Conventional
Table MYTABLE, loaded from every logical record. Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- PKEY SEQUENCE
(MAX, 1)
CHANGE_DATE SYSDATE MEDIA_ID FIRST 4000 WHT ^ CHARACTER NULL if MEDIA_ID = BLANKS TITLE NEXT 4000 WHT ^ CHARACTER NULL if TITLE = BLANKS PLACES NEXT 4000 WHT ^ CHARACTER NULL if PLACES = BLANKS PERSONALITIES NEXT 4000 WHT ^ CHARACTER NULL if PERSONALITIES = BLANKS REPORTERS NEXT 4000 WHT ^ CHARACTER NULL if REPORTERS = BLANKS SUBJECTS NEXT 4000 WHT ^ CHARACTER NULL if SUBJECTS = BLANKS HIT_TIME NEXT 4000 WHT ^ CHARACTER NULL if HIT_TIME = BLANKS "LENGTH" NEXT 4000 WHT ^ CHARACTER NULL if "LENGTH" = BLANKS DATES NEXT 4000 WHT ^ CHARACTER NULL if DATES = BLANKS ASSIGNMENTS NEXT 4000 WHT ^ CHARACTER NULL if ASSIGNMENTS = BLANKS MEDIA_TYPE NEXT 4000 WHT ^ CHARACTER NULL if MEDIA_TYPE = BLANKS SYNOPSIS NEXT ***** VARCHAR
Maximum field length is 1000002
NULL if SYNOPSIS = BLANKS
Record 1: Warning on table MYTABLE, column SYNOPSIS Variable length field was truncated.
Table MYTABLE:
1 Row successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 18792954 bytes(18 rows) Space allocated for memory besides bind array: 0 bytes Total logical records skipped: 0 Total logical records read: 1 Total logical records rejected: 0 Total logical records discarded: 0Received on Mon Apr 07 2003 - 13:32:24 CDT