• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 900
  • Last Modified:

sqlldr error

Error: Field in data file exceeds maximum length
0
gram77
Asked:
gram77
  • 7
  • 7
  • 3
  • +2
1 Solution
 
gram77Author Commented:
CREATE TABLE tableA
(
  SECURITY_LOCATOR     VARCHAR2(22 BYTE),
  ID_BB_COMPANY        NUMBER(22),
  ID_BB_SECURITY       NUMBER(22),
  RCODE                NUMBER(22),
  ACTION_ID            VARCHAR2(30 BYTE),
  MNEMONIC             VARCHAR2(30 BYTE),
  FLAG                 VARCHAR2(30 BYTE),
  NAME                 VARCHAR2(80 BYTE),
  SECURITY_TYP         VARCHAR2(28 BYTE),
  SECURITY_ID          VARCHAR2(28 BYTE),
  CRNCY                VARCHAR2(8 BYTE),
  MARKET_SECTOR_DES    VARCHAR2(8 BYTE),
  ID_BB_UNIQUE         VARCHAR2(30 BYTE),
  ANNOUNCE_DT          DATE,
  EFFECTIVE_DT         DATE,
  AMENDMENT_DT         DATE,
  NFIELDS              NUMBER(22),
  CP_RECORD_DT         DATE,
  CP_PAY_DT            DATE,
  CP_FREQ              VARCHAR2(4 BYTE),
  CP_NET_AMT           NUMBER,
  CP_TAX_AMT           NUMBER,
  CP_GROSS_AMT         NUMBER,
  CP_FRANKED_AMT       NUMBER,
  CP_DVD_CRNCY         VARCHAR2(4 BYTE),
  CP_DVD_TYP           VARCHAR2(4 BYTE),
  CP_BONUS_AMT         NUMBER,
  CP_FOREIGN_AMT       NUMBER,
  CP_PAR_PCT           NUMBER,
  CP_STOCK_OPT         VARCHAR2(4 BYTE),
  CP_REINVEST_RATIO    NUMBER,
  CP_PX                NUMBER,
  CP_TAX_RT            NUMBER,
  CP_ADJ               NUMBER,
  CP_ADJ_DT            DATE,
  CP_INDICATOR         VARCHAR2(4 BYTE),
  CP_DVD_DRP_DISCOUNT  NUMBER,
  CP_EUSD_TID          NUMBER,
  CP_EUSD_TID_SW       NUMBER,
  CP_NOTES             VARCHAR2(320 BYTE),
  REGION_ID            VARCHAR2(3 BYTE),
  AS_OF_DT             DATE                     DEFAULT SYSDATE               NOT NULL,
  LOAD_TS              TIMESTAMP(6)             DEFAULT SYSTIMESTAMP          NOT NULL
)


CREATE UNIQUE INDEX PK_tableA ON tableA
(ID_BB_UNIQUE, ACTION_ID, AS_OF_DT);

ALTER TABLE tableA ADD (
  CONSTRAINT PK_tableA
  PRIMARY KEY
  (ID_BB_UNIQUE, ACTION_ID, AS_OF_DT)
  USING INDEX PK_tableA);
0
 
gram77Author Commented:
OPTIONS (DIRECT=FALSE, PARALLEL=FALSE, BINDSIZE=50000, ROWS=200, READSIZE=65536)
Load DATA
INTO TABLE   tableA
      Append
      WHEN  "MNEMONIC" = 'DVD_CASH'  AND "FLAG" !='D'
FIELDS TERMINATED   BY  '|'
       TRAILING        NULLCOLS
( SECURITY_LOCATOR                                       POSITION (1) "DECODE(TRIM(:SECURITY_LOCATOR  ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:SECURITY_LOCATOR ))"                              ,
  ID_BB_COMPANY                                     "DECODE(TRIM(:ID_BB_COMPANY     ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:ID_BB_COMPANY    ))"          ,
  ID_BB_SECURITY                                    "DECODE(TRIM(:ID_BB_SECURITY    ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:ID_BB_SECURITY   ))"          ,
  RCODE                                                                     "DECODE(TRIM(:RCODE ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:RCODE))"          ,
  ACTION_ID                                         "DECODE(TRIM(:ACTION_ID         ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:ACTION_ID        ))"          ,
  "MNEMONIC"                                                            ,
  "FLAG"                                                            ,
  NAME                                                                      "DECODE(TRIM(:NAME  ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:NAME ))"          ,
  SECURITY_TYP                                      "DECODE(TRIM(:SECURITY_TYP      ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:SECURITY_TYP     ))"          ,
  SECURITY_ID                                       "DECODE(TRIM(:SECURITY_ID       ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:SECURITY_ID      ))"          ,
  CRNCY                                                                     "DECODE(TRIM(:CRNCY ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CRNCY))"          ,
  MARKET_SECTOR_DES                                 "DECODE(TRIM(:MARKET_SECTOR_DES ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:MARKET_SECTOR_DES))"          ,
  ID_BB_UNIQUE                                      "DECODE(TRIM(:ID_BB_UNIQUE      ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:ID_BB_UNIQUE     ))"          ,
  ANNOUNCE_DT                                       "DECODE(TRIM(:ANNOUNCE_DT  ),'N.A.',NULL,'N/A',null,NULL,NULL, TO_DATE(:ANNOUNCE_DT ,'YYYYMMDD'))" ,    
  EFFECTIVE_DT                                      "DECODE(TRIM(:EFFECTIVE_DT ),'N.A.',NULL,'N/A',null,NULL,NULL, TO_DATE(:EFFECTIVE_DT ,'YYYYMMDD'))" ,  
  AMENDMENT_DT                                      "DECODE(TRIM(:AMENDMENT_DT ),'N.A.',NULL,'N/A',null,NULL,NULL, TO_DATE(:AMENDMENT_DT ,'YYYYMMDD'))" ,    
  ID_BB_GLOBAL                          "DECODE(TRIM(:ID_BB_GLOBAL      ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:ID_BB_GLOBAL     ))"          ,
  ID_BB_GLOBAL_COMPANY              "DECODE(TRIM(:ID_BB_GLOBAL_COMPANY      ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:ID_BB_GLOBAL_COMPANY     ))"          ,
  ID_BB_SEC_NUM_DES                    "DECODE(TRIM(:ID_BB_SEC_NUM_DES      ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:ID_BB_SEC_NUM_DES     ))"          ,
  FEED_SOURCE                          "DECODE(TRIM(:FEED_SOURCE      ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:FEED_SOURCE     ))"          ,
  NFIELDS                                                                   "DECODE(TRIM(:NFIELDS  ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:NFIELDS ))"                              ,        
  FIELD4                                                                         FILLER      ,
      CP_RECORD_DT                                                        "DECODE(TRIM(:CP_RECORD_DT ),'N.A.',NULL,'N/A',null,NULL,NULL, TO_DATE(:CP_RECORD_DT ,'YYYYMMDD'))" ,  
  FIELD6                                                                        FILLER      ,
 CP_PAY_DT                                                                    "DECODE(TRIM(:CP_PAY_DT ),'N.A.',NULL,'N/A',null,NULL,NULL, TO_DATE(:CP_PAY_DT ,'YYYYMMDD'))" ,  
  FIELD7                                                                         FILLER      ,
  CP_FREQ              "DECODE(TRIM(:CP_FREQ           ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_FREQ           ))",
    FIELD8                                                                         FILLER      ,
  CP_NET_AMT           "DECODE(TRIM(:CP_NET_AMT        ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_NET_AMT        ))",
    FIELD9                                                                         FILLER      ,
  CP_TAX_AMT           "DECODE(TRIM(:CP_TAX_AMT        ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_TAX_AMT        ))",
    FIELD10                                                                         FILLER      ,
  CP_GROSS_AMT         "DECODE(TRIM(:CP_GROSS_AMT      ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_GROSS_AMT      ))",
    FIELD11                                                                         FILLER      ,
  CP_FRANKED_AMT       "DECODE(TRIM(:CP_FRANKED_AMT    ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_FRANKED_AMT    ))",
    FIELD12                                                                         FILLER      ,
  CP_DVD_CRNCY         "DECODE(TRIM(:CP_DVD_CRNCY      ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_DVD_CRNCY      ))",
    FIELD13                                                                         FILLER      ,
  CP_DVD_TYP           "DECODE(TRIM(:CP_DVD_TYP        ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_DVD_TYP        ))",
    FIELD14                                                                         FILLER      ,
  CP_BONUS_AMT         "DECODE(TRIM(:CP_BONUS_AMT      ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_BONUS_AMT      ))",
    FIELD15                                                                         FILLER      ,
  CP_FOREIGN_AMT       "DECODE(TRIM(:CP_FOREIGN_AMT    ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_FOREIGN_AMT    ))",
    FIELD16                                                                         FILLER      ,
  CP_PAR_PCT           "DECODE(TRIM(:CP_PAR_PCT        ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_PAR_PCT        ))",
    FIELD17                                                                         FILLER      ,
  CP_STOCK_OPT         "DECODE(TRIM(:CP_STOCK_OPT      ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_STOCK_OPT      ))",
    FIELD18                                                                         FILLER      ,
  CP_REINVEST_RATIO    "DECODE(TRIM(:CP_REINVEST_RATIO ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_REINVEST_RATIO ))",
    FIELD19                                                                         FILLER      ,
  CP_PX                "DECODE(TRIM(:CP_PX             ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_PX             ))",
    FIELD20                                                                         FILLER      ,
  CP_TAX_RT            "DECODE(TRIM(:CP_TAX_RT         ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_TAX_RT         ))",
    FIELD21                                                                        FILLER      ,
  CP_ADJ               "DECODE(TRIM(:CP_ADJ            ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_ADJ            ))",
    FIELD22                                                                         FILLER      ,
  CP_ADJ_DT            "DECODE(TRIM(:CP_ADJ_DT ),'N.A.',NULL,'N/A',null,NULL,NULL, TO_DATE(:CP_ADJ_DT ,'YYYYMMDD'))" ,            
    FIELD23                                                                         FILLER      ,
  CP_INDICATOR         "DECODE(TRIM(:CP_INDICATOR            ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_INDICATOR            ))",
    FIELD24                                                                         FILLER      ,
  CP_DVD_DRP_DISCOUNT  "DECODE(TRIM(:CP_DVD_DRP_DISCOUNT  ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_DVD_DRP_DISCOUNT ))",
  FIELD25                                                                   FILLER      ,
  CP_EUSD_TID     "DECODE(TRIM(:CP_EUSD_TID            ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_EUSD_TID   ))",
  FIELD26                                                                   FILLER      ,
   CP_EUSD_TID_SW  "DECODE(TRIM(:CP_EUSD_TID_SW            ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_EUSD_TID_SW   ))",
  FIELD27                                                                   FILLER      ,
  CP_NOTES  char(320)                           "DECODE(TRIM(:CP_NOTES),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(substr(:CP_NOTES,1,320) ))",
  REGION_ID           CONSTANT 'ZZZ',
  AS_OF_DT             CONSTANT '2-FEB-12'
  LOAD_TS              CONSTANT '2/7/2012 12:16:14.205071000 PM'
  )
0
 
gram77Author Commented:
Error: Record 12672: Rejected - Error on table tableA, column LOAD_TS.
Field in data file exceeds maximum length

Bad row1:
MERPM Equity|100199|2000|0|61983511|DVD_CASH|U|Manila Electric Co|ISIN|PHY5764J1483|PHP|Equity|EQ0010019900002000|20120227|20120320|20130204|BBG000CL9GG7|BBG001FDY7C6|MER|PM|24|CP_RECORD_DT|20120323|CP_PAY_DT|20120423|CP_FREQ|2|CP_NET_AMT|N.A.|CP_TAX_AMT|N.A.|CP_GROSS_AMT|4.10|CP_FRANKED_AMT| |CP_DVD_CRNCY|PHP|CP_DVD_TYP|1000|CP_SPPL_AMT| |CP_FOREIGN_AMT| |CP_P        AR_PCT| |CP_STOCK_OPT|U|CP_REINVEST_RATIO| |CP_PX|N.A.|CP_TAX_RT|N.A.|CP_ADJ|0.985004|CP_ADJ_DT|20120320|CP_INDICATOR|N|CP_DVD_DRP_DISCOUNT|N.A.|CP_EUSD_TID| |CP_EUSD_TID_SW| |CP_DIST_AMT_STATUS|F|CP_NOTES|The declaration of its 51% equity participation in the outstanding issued common stock of Rockwell Land Corporation as property dividend in favor of common stockholders of record as of March 23, 2012, except for foreign common shareholders who will be paid the cash equivalent of the property dividend.|
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
gram77Author Commented:
OK row:
033920  KS Equity|866572|1000|0|72152076|DVD_STOCK|U|Muhak Co Ltd|ISIN|KR7033920000|KRW|Equity|EQ0086657200001000|20121220|20121227|20130204|BBG000C1QT01|BBG001G5FD94|033920|KS|10|CP_REC      ORD_DT|20121231|CP_PAY_DT|N.A.|CP_AMT|1.515550|CP_DVD_STOCK_TYP|2000|CP_TKR|033920 KS|CP_ADJ|1.015156|CP_ADJ_DT|20121227|CP_INDICATOR|N|CP_DIST_AMT_STATUS|F|CP_NOTES|400,000 common stock  issued.|
0
 
DavidSenior Oracle Database AdministratorCommented:
Short of painfully matching each column def against the example, which you are welcome to do, I'd guess the 320 b CP_NOTES is the source.
0
 
slightwv (䄆 Netminder) Commented:
>>Error on table tableA, column LOAD_TS. Field in data file exceeds maximum length


LOAD_TS is a constant so likely the 'good' row doesn't pass the WHERE?

My guess is you are running into an implicit conversion issue.  Oracle is trying to convert your strings into the date and timestmap format.

You should always do explicit conversions.

Try this:
  AS_OF_DT             CONSTANT "to_date('2-FEB-12','DD-MON-YY')",
  LOAD_TS              CONSTANT "to_timestamp('2/7/2012 12:16:14.205071000 PM','MM/DD/YYYY HH:MI:SS.FF AM')"

I don't have the time to set up your exact test case and run it.

If you could simplify it somewhat (fewer columns that still reproduce the issue), I can try to set it up later.
0
 
paquicubaCommented:
CP_INDICATOR         VARCHAR2(4 BYTE) := 'CP_INDICATOR'
0
 
gram77Author Commented:
LOAD_TS seems to be the issue. But then how did the good row got through?

LOAD_TS calls a function that returns a timestamp.
0
 
gram77Author Commented:
Alternatively, it could be a data mismatch
0
 
paquicubaCommented:
Are you sure those two records belong to the same load. If you count the number of pipes "|" you're missing 28 of them
0
 
paquicubaCommented:
The table doesn't even correspond with all the fields. For example, where is field ID_BB_GLOBAL?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
You can correct what all others has said and i believe your table columns/control file columns are not matching. Not sure whether you did a mistake while asking the question to post incorrect table definition or incorrect ctl file.

Kindly provide your correct table definition commands and also the ctl file - so that it is easy to understand and help to resolve your issue.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
i had setup a table in my test db and got the below error

"SQL*Loader-466: Column ID_BB_GLOBAL does not exist in table TABLEA."
0
 
gram77Author Commented:
Modified Table with required feed:
CREATE TABLE tableA
(
  SECURITY_LOCATOR      VARCHAR2(22 BYTE),
  ID_BB_COMPANY         NUMBER(22),
  ID_BB_SECURITY        NUMBER(22),
  RCODE                 NUMBER(22),
  ACTION_ID             VARCHAR2(30 BYTE),
  MNEMONIC              VARCHAR2(30 BYTE),
  FLAG                  VARCHAR2(30 BYTE),
  NAME                  VARCHAR2(80 BYTE),
  SECURITY_TYP          VARCHAR2(28 BYTE),
  SECURITY_ID           VARCHAR2(28 BYTE),
  CRNCY                 VARCHAR2(8 BYTE),
  MARKET_SECTOR_DES     VARCHAR2(8 BYTE),
  ID_BB_UNIQUE          VARCHAR2(30 BYTE),
  ANNOUNCE_DT           DATE,
  EFFECTIVE_DT          DATE,
  AMENDMENT_DT          DATE,
  NFIELDS               NUMBER(22),
  CP_RECORD_DT          DATE,
  CP_PAY_DT             DATE,
  CP_FREQ               VARCHAR2(4 BYTE),
  CP_NET_AMT            NUMBER,
  CP_TAX_AMT            NUMBER,
  CP_GROSS_AMT          NUMBER,
  CP_FRANKED_AMT        NUMBER,
  CP_DVD_CRNCY          VARCHAR2(4 BYTE),
  CP_DVD_TYP            VARCHAR2(4 BYTE),
  CP_BONUS_AMT          NUMBER,
  CP_FOREIGN_AMT        NUMBER,
  CP_PAR_PCT            NUMBER,
  CP_STOCK_OPT          VARCHAR2(4 BYTE),
  CP_REINVEST_RATIO     NUMBER,
  CP_PX                 NUMBER,
  CP_TAX_RT             NUMBER,
  CP_ADJ                NUMBER,
  CP_ADJ_DT             DATE,
  CP_INDICATOR          VARCHAR2(4 BYTE),
  CP_DVD_DRP_DISCOUNT   NUMBER,
  CP_EUSD_TID           NUMBER,
  CP_EUSD_TID_SW        NUMBER,
  CP_NOTES              VARCHAR2(320 BYTE),
  REGION_ID             VARCHAR2(3 BYTE),
  AS_OF_DT              DATE                    DEFAULT SYSDATE               NOT NULL,
  LOAD_TS               TIMESTAMP(6)            DEFAULT SYSTIMESTAMP          NOT NULL,
  ID_BB_GLOBAL          VARCHAR2(400 BYTE),
  ID_BB_GLOBAL_COMPANY  VARCHAR2(400 BYTE),
  ID_BB_SEC_NUM_DES     VARCHAR2(400 BYTE),
  FEED_SOURCE           VARCHAR2(400 BYTE)
)
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
for the 3 records you had given, this is what i get.

Record 1: Rejected - Error on table TABLEA, column LOAD_TS.
Field in data file exceeds maximum length
Record 2: Discarded - failed all WHEN clauses.
Record 3: Discarded - failed all WHEN clauses.

Basically there is some issue with this line - LOAD_TS               "to_timestamp('2/7/2012 12:16:14.205','MM/DD/YYYY HH24:MI:SS.FF')"

i commented this line and it works fine for me. i mean 1 record gets loaded into the table.

my sql loader is on - "SQL*Loader: Release 11.1.0.7.0 "
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
refer to this and i just got curious as to what is your nls_timestamp_format ?

may be this is a bug in the sql loader version which we use or this has something to do with the nls_timestamp_format setting
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
ok..here we go after some research.. this works for me in my control file. so replace the load_ts in your control file with the below and that should work.

LOAD_TS expression "to_timestamp('2/7/2012 12:16:14.205071000 PM','MM/DD/YYYY HH:MI:SS.FF AM')"

Let me know if it still does not work for you.

Thanks,
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
just attaching my working ctl , dat and bad file here for your reference.
aa.dat.txt
aa.ctl.txt
aa.log.txt
aa.bad.txt
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
@gram77,

Have you tested my solution and does it work for you ? if not, please let me know with the error.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 7
  • 7
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now