This discussion is archived
6 Replies Latest reply: Dec 13, 2012 7:13 PM by sb92075 RSS

"Field in data file exceeds maximum length" : sqlloader

vasanth Newbie
Currently Being Moderated
Hi
When trying to load to Northwind_CHECK_CONSTRAINTS_A3 table in dbo_a3 schema using sqlloader im getting the below error in the log file of the loader

Control file :

italics
load data
infile /home/oracle/Northwind_CHECK_CONSTRAINTS.csv
into table Northwind_CHECK_CONSTRAINTS_A3
fields terminated by ','optionally enclosed by '"' trailing nullcols
(CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CHECK_CLAUSE)

table :

create table Northwind_CHECK_CONSTRAINTS_A3
(
CONSTRAINT_CATALOG char(1000),
CONSTRAINT_SCHEMA char(1000),
CONSTRAINT_NAME char(1000),
CHECK_CLAUSE char(1000)
);


sqlloader command : sqlldr dbo_a3/dbo_a3 control=/home/oracle/control/Northwind_CHECK_CONSTRAINTS.ctl log=/home/oracle/check.log


Log file :


SQL*Loader: Release 11.2.0.2.0 - Production on Fri Dec 14 01:07:02 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Control File: /home/oracle/control/Northwind_CHECK_CONSTRAINTS.ctl
Data File: /home/oracle/Ext_tables/Northwind_CHECK_CONSTRAINTS.csv
Bad File: /home/oracle/control/Northwind_CHECK_CONSTRAINTS.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table NORTHWIND_CHECK_CONSTRAINTS_A3, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CONSTRAINT_CATALOG FIRST * , O(") CHARACTER
CONSTRAINT_SCHEMA NEXT * , O(") CHARACTER
CONSTRAINT_NAME NEXT * , O(") CHARACTER
CHECK_CLAUSE NEXT * , O(") CHARACTER

Record 1: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
Field in data file exceeds maximum length
Record 2: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
Field in data file exceeds maximum length
Record 3: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
Field in data file exceeds maximum length
Record 4: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
Field in data file exceeds maximum length
Record 5: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
Field in data file exceeds maximum length
Record 6: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
Field in data file exceeds maximum length
Record 7: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
Field in data file exceeds maximum length
Record 8: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
Field in data file exceeds maximum length
Record 9: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
Field in data file exceeds maximum length
Record 10: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
Field in data file exceeds maximum length
Record 11: Discarded - all columns null.

Table NORTHWIND_CHECK_CONSTRAINTS_A3:
1 Row successfully loaded.
10 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
1 Row not loaded because all fields were null.


Space allocated for bind array: 66048 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 12
Total logical records rejected: 10
Total logical records discarded: 1

Run began on Fri Dec 14 01:07:02 2012
Run ended on Fri Dec 14 01:07:02 2012

Elapsed time was: 00:00:00.10
CPU time was: 00:00:00.02


h1 Please help.........

Edited by: vasanth on Dec 14, 2012 2:12 AM
  • 1. Re: "Field in data file exceeds maximum length" : sqlloader
    vasanth Newbie
    Currently Being Moderated
    I Even tried these below options in control file still the same error persists
    _
    COL1 CHAR(500) NULLIF COL1=BLANKS,
    COL2 CHAR(500) NULLIF COL2=BLANKS,
    _

    +
    CUSTRE_REMARK_TEXT CHAR(1000) nullif CUSTRE_REMARK_TEXT
    = '(null)'
    +

    Am i giving wrong datatype for the column h1 CONSTRAINT_CATALOG h1

    but in the csv file all the 8 rows for the column CONSTRAINT_CATALOG is Northwind ....I think because of error in this column only whole loading is not happening..


    Kindly help guys.....i m getting exhausted !
  • 2. Re: "Field in data file exceeds maximum length" : sqlloader
    vasanth Newbie
    Currently Being Moderated
    Hi Friends

    I somehow managed to shrugg off the errors while loading and loaded the datas , but still only one column is populated in the table ..

    Pls see my log below

    SQL*Loader: Release 11.2.0.2.0 - Production on Fri Dec 14 03:18:27 2012

    *Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

    Control File: /home/oracle/control/Northwind_CHECK_CONSTRAINTS.ctl
    Data File: /home/oracle/Ext_tables/Northwind_CHECK_CONSTRAINTS.csv
    Bad File: /home/oracle/control/Northwind_CHECK_CONSTRAINTS.bad
    Discard File: none specified

    (Allow all discards)

    Number to load: ALL
    Number to skip: 0
    Errors allowed: 50
    Bind array: 64 rows, maximum of 256000 bytes
    Continuation: none specified
    Path used: Conventional

    Table NORTHWIND_CHECK_CONSTRAINTS_A3, loaded from every logical record.
    Insert option in effect for this table: INSERT
    TRAILING NULLCOLS option in effect

    Column Name Position Len Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    CONSTRAINT_CATALOG FIRST 6000 , O(") CHARACTER
    CONSTRAINT_SCHEMA NEXT * , O(") CHARACTER
    CONSTRAINT_NAME NEXT * , O(") CHARACTER
    CHECK_CLAUSE NEXT * , O(") CHARACTER

    value used for ROWS parameter changed from 64 to 37
    Record 11: Discarded - all columns null.

    Table NORTHWIND_CHECK_CONSTRAINTS_A3:
    11 Rows successfully loaded.
    0 Rows not loaded due to data errors.
    0 Rows not loaded because all WHEN clauses were failed.
    1 Row not loaded because all fields were null.


    Space allocated for bind array: 250712 bytes(37 rows)
    Read buffer bytes: 1048576

    Total logical records skipped: 0
    Total logical records read: 12*


    Only the row CONSTRAINT_CATALOG which i declared as long in table definition is populated ..
  • 3. Re: "Field in data file exceeds maximum length" : sqlloader
    rp0428 Guru
    Currently Being Moderated
    >
    When trying to load to Northwind_CHECK_CONSTRAINTS_A3 table in dbo_a3 schema using sqlloader im getting the below error in the log file of the loader
    . . .
    create table Northwind_CHECK_CONSTRAINTS_A3
    (
    CONSTRAINT_CATALOG char(1000),
    CONSTRAINT_SCHEMA char(1000),
    CONSTRAINT_NAME char(1000),
    CHECK_CLAUSE char(1000)
    );
    . . .
    Record 1: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
    Field in data file exceeds maximum length
    . . .
    Only the row CONSTRAINT_CATALOG which i declared as long in table definition is populated ..
    >
    You've posted enough times to know you need to provide your 4 digit Oracle version.

    Why are you using CHAR datatypes instead of VARCHAR2?

    The error says that the data is too long for the column. You didn't post any data for us to look at to see what you are loading.

    Can you manually insert one of the rejected records into the table?
  • 4. Re: "Field in data file exceeds maximum length" : sqlloader
    jgarry Guru
    Currently Being Moderated
    If you don't specify the length of each field in the control file, the default is 255.

    See MOS SQL*Loader Problems Loading Variable Length Fields Using CHAR and VARCHAR Datatypes [ID 160002.1]
  • 5. Re: "Field in data file exceeds maximum length" : sqlloader
    vasanth Newbie
    Currently Being Moderated
    Hi dB version is

    SQL*Plus: Release 11.1.0.7.0 - Production on Fri Dec 14 07:42:05 2012


    I AM ABLE TO INSERT DATA MANUALLY.......
  • 6. Re: "Field in data file exceeds maximum length" : sqlloader
    sb92075 Guru
    Currently Being Moderated
    vasanth wrote:
    Hi dB version is

    SQL*Plus: Release 11.1.0.7.0 - Production on Fri Dec 14 07:42:05 2012


    I AM ABLE TO INSERT DATA MANUALLY.......
    Different clients have different limitations.
    I can be a data dependent error.


    how ca we reproduce what you report?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points