Solved

sqlldr error

Posted on 2013-02-06
8
576 Views
Last Modified: 2013-02-24
I am getting following error in sqlldr: ORA-00947: not enough values
0
Comment
Question by:gram77
  • 5
  • 2
8 Comments
 

Author Comment

by:gram77
ID: 38861505
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_PRV_MTY_DT         DATE,
  CP_NXT_MTY_DT         DATE,
  CP_EXTEN_ISS_REASON   NUMBER,
  CP_STILL_EXTEND       VARCHAR2(4 BYTE),
  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)
);



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


ALTER TABLE tableA ADD (
  CONSTRAINT PK_BLG_CAX_EXTEN_ISS
  PRIMARY KEY
  (ID_BB_UNIQUE, ACTION_ID, AS_OF_DT)
  USING INDEX PK_tableA);
0
 

Author Comment

by:gram77
ID: 38861513
OPTIONS (DIRECT=FALSE, PARALLEL=FALSE, BINDSIZE=50000, ROWS=200, READSIZE=65536)
Load DATA
INTO TABLE  tableA
      Append
      WHEN  "MNEMONIC" = 'EXTEN_ISS'  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_PRV_MTY_DT                                                  "DECODE(TRIM(:CP_PRV_MTY_DT),'N.A.',NULL,'N/A',NULL,NULL,NULL,TO_DATE(:CP_PRV_MTY_DT ,'YYYYMMDD')))",      
  FIELD6                                                                         FILLER      ,
  CP_NXT_MTY_DT                                                  "DECODE(TRIM(:CP_NXT_MTY_DT),'N.A.',NULL,'N/A',NULL,NULL,NULL,TO_DATE(:CP_NXT_MTY_DT ,'YYYYMMDD')))",
  FIELD8                                                                        FILLER      ,
  CP_EXTEN_ISS_REASON                                "DECODE(TRIM(:CP_EXTEN_ISS_REASON),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_EXTEN_ISS_REASON))",
  FIELD10                                                                   FILLER      ,
  CP_STILL_EXTEND                               "DECODE(TRIM(:CP_STILL_EXTEND    ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_STILL_EXTEND    ))",
  FIELD14                                                                   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 'NMR',
  AS_OF_DT                                                       "racs_util.get_cob_date('BB', 'CorpPfdCax', 'NMR')",  
  LOAD_TS                                                        "GET_LOADER_RUN_DATE"                                  
  )
0
 

Author Comment

by:gram77
ID: 38861518
Data:
Row1:
EJ1210057 Corp|14154276|21104481|0|73688025|EXTEN_ISS|N|Bank of Nova Scotia/Houston|CUSIP|06417EL68|USD|Corp|COEJ1210057|20130202|20130202|20130204|BBG002VNLSD1|BBG001H7WXD1|BNS F 11/29/13 YCD9|N.A.|5|CP_PRV_MTY_DT|20130702|CP_NX        T_MTY_DT|20131129|CP_EXTEN_ISS_REASON|2|CP_STILL_EXTEND|Y|CP_NOTES|N.A.|

Row2:
EJ3536624 Corp|305680|25210287|0|73687955|EXTEN_ISS|N|Royal Bank of Canada/New York NY|CUSIP|78009NJE7|USD|Corp|COEJ3536624|20130204|20130204|20130204|BBG003C7KTS8|BBG001FMDSZ8|RY F 03/04/14 YCD|N.A.|5|CP_PRV_MTY_DT|20140204|CP_N        XT_MTY_DT|20140304|CP_EXTEN_ISS_REASON|2|CP_STILL_EXTEND|Y|CP_NOTES|N.A.|
0
 

Author Comment

by:gram77
ID: 38861525
Data:
AS_OF_DT                                                       null  
LOAD_TS                                                        null
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 14

Expert Comment

by:ajexpert
ID: 38861748
It means number of columns are more than the set of values.  There should be 1=1 correspondence between columns and values

I suggest put the number of columns on one side of XLS
and on other side put the expected values and correct the sql loader file

HTH
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 38862730
I have found that there were 2 mistakes in your control file... Could you please test with the below control file as this works fine for me at my end.

OPTIONS (DIRECT=FALSE, PARALLEL=FALSE, BINDSIZE=50000, ROWS=200, READSIZE=65536)
Load DATA
INTO TABLE  tableA
      Append
      WHEN  "MNEMONIC" = 'EXTEN_ISS'  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_PRV_MTY_DT                                                  "DECODE(TRIM(:CP_PRV_MTY_DT),'N.A.',NULL,'N/A',NULL,NULL,NULL,TO_DATE(:CP_PRV_MTY_DT ,'YYYYMMDD'))",      
  FIELD6                                                                         FILLER      ,
  CP_NXT_MTY_DT                                                  "DECODE(TRIM(:CP_NXT_MTY_DT),'N.A.',NULL,'N/A',NULL,NULL,NULL,TO_DATE(:CP_NXT_MTY_DT ,'YYYYMMDD'))",
  FIELD8                                                                        FILLER      ,
  CP_EXTEN_ISS_REASON                                "DECODE(TRIM(:CP_EXTEN_ISS_REASON),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_EXTEN_ISS_REASON))",
  FIELD10                                                                   FILLER      ,
  CP_STILL_EXTEND                               "DECODE(TRIM(:CP_STILL_EXTEND    ),'N.A.',NULL,'N/A',NULL,NULL,NULL,TRIM(:CP_STILL_EXTEND    ))",
  FIELD14                                                                   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 'NMR',
  AS_OF_DT                                                       "racs_util.get_cob_date('BB', 'CorpPfdCax', 'NMR')",  
  LOAD_TS                                                        "GET_LOADER_RUN_DATE"                                  
  )

Let me know if this is still not working for you.

Thanks,
0
 

Author Comment

by:gram77
ID: 38862892
Thanks Nav, you made my day.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38862904
Good and thanks
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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

705 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

13 Experts available now in Live!

Get 1:1 Help Now