Solved

sqlldr error

Posted on 2013-02-07
19
721 Views
Last Modified: 2013-02-11
Error: Field in data file exceeds maximum length
0
Comment
Question by:gram77
  • 7
  • 7
  • 3
  • +2
19 Comments
 

Author Comment

by:gram77
ID: 38864873
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
 

Author Comment

by:gram77
ID: 38864877
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
 

Author Comment

by:gram77
ID: 38864881
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
 

Author Comment

by:gram77
ID: 38864884
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
 
LVL 23

Expert Comment

by:David
ID: 38864917
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38864919
>>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
 
LVL 23

Expert Comment

by:paquicuba
ID: 38864938
CP_INDICATOR         VARCHAR2(4 BYTE) := 'CP_INDICATOR'
0
 

Author Comment

by:gram77
ID: 38864984
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
 

Author Comment

by:gram77
ID: 38865090
Alternatively, it could be a data mismatch
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:paquicuba
ID: 38865106
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 38865154
The table doesn't even correspond with all the fields. For example, where is field ID_BB_GLOBAL?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38866433
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38866435
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
 

Author Comment

by:gram77
ID: 38867120
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38867250
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38867253
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
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 38867284
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38867333
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38875724
@gram77,

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now