Re: sqlldr: Field in data file exceeds maximum length?

Phil Lawrence schrieb:
> Can anyone see why sqlldr thinks the last field in the below example
> record exceeds maximum length?
>
> I show the last field as 1001 characters, including the newline. That
> equals 1000 without the newline, and that is the fieldsize,
> CHAR(1000).
>
> For grins, I also deleted 1, then 12, then 50 spaces from the middle
> of the record it is complaining about, and sqlldr still gave the same
> error each time.
>
> Following is a record of the run with 1 of the spaces removed from the
> offending record. Following that is a dump of the record itself so
> you can see it.
>
> $ ls -1
> ex_cust_remarks.ctl
> ex_cust_remarks.foo
> ex_cust_remarks.foo-1
> ex_cust_remarks_cre.sql
>
> $ cat ex_cust_remarks_cre.sql
>
> CREATE TABLE EX_CUST_REMARKS&1 (
> CUSTRE_CUST_ID CHAR(15),
> CUSTRE_REMARK_DT DATE,
> CUSTRE_REMARK_TM CHAR(08),
> CUSTRE_REMARK_TYPE_CD CHAR(03),
> CUSTRE_AUTO_DISPLAY_FLAG CHAR(01),
> CUSTRE_PERSON_ID CHAR(15),
> CUSTRE_REMARK_TEXT CHAR(1000)
> )
> TABLESPACE DATA04
> STORAGE(
> INITIAL 512M
> NEXT 512M
> MINEXTENTS 3
> );
>
> CREATE INDEX EX_CUST_REMARKS_INDEX_1&1 ON EX_CUST_REMARKS&1
> (CUSTRE_CUST_ID)
> TABLESPACE DATA04
> ;
>
> $ cat ex_cust_remarks.ctl
> -- Name : ex_cust_remarks.ctl
> --
> OPTIONS (SILENT=(FEEDBACK,DISCARDS) DIRECT=TRUE)
> LOAD DATA
> APPEND
> INTO TABLE EX_CUST_REMARKS
> FIELDS TERMINATED BY '^|^'
> -- OPTIONALLY ENCLOSED BY '\\^/'
> OPTIONALLY ENCLOSED BY '"'
> (
>
> CUSTRE_CUST_ID CHAR nullif CUSTRE_CUST_ID
> = '(null)',
> CUSTRE_REMARK_DT DATE "DD-MON-YYYY
> HH24:MI:SS" nullif CUSTRE_REMARK_DT = '(null)',
> CUSTRE_REMARK_TM CHAR nullif
> CUSTRE_REMARK_TM = '(null)',
> CUSTRE_REMARK_TYPE_CD CHAR nullif
> CUSTRE_REMARK_TYPE_CD = '(null)',
> CUSTRE_AUTO_DISPLAY_FLAG CHAR nullif
> CUSTRE_AUTO_DISPLAY_FLAG = '(null)',
> CUSTRE_PERSON_ID CHAR nullif
> CUSTRE_PERSON_ID = '(null)',
> CUSTRE_REMARK_TEXT CHAR nullif
> CUSTRE_REMARK_TEXT = '(null)'
> )
>

If you don't specify length of the input character data, sqlldr uses default of 255 characters.
If you change the controlfile definition to

CUSTRE_REMARK_TEXT                 CHAR(1000) nullif CUSTRE_REMARK_TEXT 

you should be able to load the data without problems. Also consider the Daniels remark about the use of CHAR datatype in your table - i can't imagine a case when using VARCHAR2 instead would not be beneficial.

Best regards

Maxim Received on Tue May 22 2007 - 21:24:26 CEST