Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> SQL*Loader - Variable length field was truncated

SQL*Loader - Variable length field was truncated

From: Julia S <urbannoizz_at_yahoo.com>
Date: 7 Apr 2003 11:32:24 -0700
Message-ID: <f2ba9e09.0304071032.6751182d@posting.google.com>


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:        0
Received on Mon Apr 07 2003 - 13:32:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US