Results 1 to 3 of 3

Thread: SQL Loader

  1. #1
    Join Date
    May 2013
    Posts
    33

    Unanswered: SQL Loader

    This is my dat.file
    Code:
    200100 	1000.00 600.00 	08-JAN-08 	C00015 	A003 	SOD
    200110 	3000.00 500.00 	15-APR-08 	C00019 	A010 	SOD
    200107 	4500.00 900.00 	30-AUG-08 	C00007 	A010 	SOD
    200112 	2000.00 400.00 	30-MAY-08 	C00016 	A007 	SOD
    200113 	4000.00 600.00 	10-JUN-08 	C00022 	A002 	SOD
    200102 	2000.00 300.00 	25-MAY-08 	C00012 	A012 	SOD
    200114 	3500.00 1500.00 15-AUG-08 	C00002 	A008 	SOD
    200122 	2500.00 400.00 	16-SEP-08 	C00003 	A004 	SOD
    200118 	500.00 	100.00 	20-JUL-08 	C00023 	A006 	SOD
    200119 	4000.00 700.00 	16-SEP-08 	C00007 	A010 	SOD
    200121 	1500.00 600.00 	23-SEP-08 	C00008 	A004 	SOD
    200130 	2500.00 400.00 	30-JUL-08 	C00025 	A011 	SOD
    200134 	4200.00 1800.00 25-SEP-08 	C00004 	A005 	SOD
    200115 	2000.00 1200.00 08-FEB-08 	C00013 	A013 	SOD
    200108 	4000.00 600.00 	15-FEB-08 	C00008 	A004 	SOD
    200103 	1500.00 700.00 	15-MAY-08 	C00021 	A005 	SOD
    200105 	2500.00 500.00 	18-JUL-08 	C00025 	A011 	SOD
    200109 	3500.00 800.00 	30-JUL-08 	C00011 	A010 	SOD
    200101 	3000.00 1000.00 15-JUL-08 	C00001 	A008 	SOD
    200111 	1000.00 300.00 	10-JUL-08 	C00020 	A008 	SOD
    200104 	1500.00 500.00 	15-MAR-08 	C00006 	A004 	SOD
    200106 	2500.00 700.00 	20-APR-08 	C00005 	A002 	SOD
    200125 	2000.00 600.00 	16-OCT-08 	C00018 	A005 	SOD
    200117 	800.00 	200.00 	20-OCT-08 	C00014 	A001 	SOD
    200123 	500.00 	100.00 	16-SEP-08 	C00022 	A002 	SOD
    200120 	500.00 	100.00 	20-JUL-08 	C00009 	A002 	SOD
    200116 	500.00 	100.00 	13-JUL-08 	C00010 	A009 	SOD
    200124 	500.00 	100.00 	20-JUN-08 	C00017 	A007 	SOD
    200126 	500.00 	100.00 	24-JUN-08 	C00022 	A002 	SOD
    200129 	2500.00 500.00 	20-JUL-08 	C00024 	A006 	SOD
    200127 	2500.00 400.00 	20-JUL-08 	C00015 	A003 	SOD
    200128 	3500.00 1500.00 20-JUL-08 	C00009 	A002 	SOD
    200135 	2000.00 800.00 	16-SEP-08 	C00007 	A010 	SOD
    200131 	900.00 	150.00 	26-AUG-08 	C00012 	A012 	SOD
    200133 	1200.00 400.00 	29-JUN-08 	C00009 	A002 	SOD
    200132 	4000.00 2000.00 15-AUG-08 	C00013 	A013 	SOD
    This is my control file
    Code:
    load data 
    infile 'F:\sat.csv'
    into table orders
    fields terminated by ' '
    (ord_number,ord_amount,advanced_amount,ord_date,cust_code,agent_code,ord_description)
    When i am loading data i m getting error like this

    Code:
    C:\Documents and Settings\Administrator>sqlplus one/two
    
    SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 19 15:31:03 2013
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> desc orders
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ORD_NUMBER                                         NUMBER
     ORD_AMOUNT                                         NUMBER(8,2)
     ADVANCED_AMOUNT                                    NUMBER(8,2)
     ORD_DATE                                           DATE
     CUST_CODE                                          VARCHAR2(10)
     AGENT_CODE                                         VARCHAR2(10)
     ORD_DESCRIPTION                                    VARCHAR2(10)
    
    SQL> host
    Microsoft Windows XP [Version 5.1.2600]
    (C) Copyright 1985-2001 Microsoft Corp.
    
    C:\Documents and Settings\Administrator>notepad me.ctl
    
    C:\Documents and Settings\Administrator>sqlldr one/two me.ctl
    
    SQL*Loader: Release 10.2.0.1.0 - Production on Mon Aug 19 15:32:00 2013
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Commit point reached - logical record count 35
    Commit point reached - logical record count 36
    
    C:\Documents and Settings\Administrator>exit
    
    SQL> select * from orders;
    
    no rows selected
    This is my log file

    Code:
    SQL*Loader: Release 10.2.0.1.0 - Production on Mon Aug 19 15:32:00 2013
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Control File:   me.ctl
    Data File:      F:\sat.csv
      Bad File:     sat.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 ORDERS, loaded from every logical record.
    Insert option in effect for this table: INSERT
    
       Column Name                  Position   Len  Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    ORD_NUMBER                          FIRST     *  WHT      CHARACTER            
    ORD_AMOUNT                           NEXT     *  WHT      CHARACTER            
    ADVANCED_AMOUNT                      NEXT     *  WHT      CHARACTER            
    ORD_DATE                             NEXT     *  WHT      CHARACTER            
    CUST_CODE                            NEXT     *  WHT      CHARACTER            
    AGENT_CODE                           NEXT     *  WHT      CHARACTER            
    ORD_DESCRIPTION                      NEXT     *  WHT      CHARACTER            
    
    Record 1: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 2: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 3: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 4: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 5: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 6: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 7: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 8: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 9: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 10: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 11: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 12: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 13: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 14: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 15: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 16: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 17: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 18: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 19: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 20: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 21: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 22: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 23: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 24: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 25: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 26: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 27: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 28: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 29: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 30: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 31: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 32: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 33: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 34: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 35: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    Record 36: Rejected - Error on table ORDERS, column ORD_AMOUNT.
    ORA-01722: invalid number
    
    
    Table ORDERS:
      0 Rows successfully loaded.
      36 Rows not loaded due to data errors.
      0 Rows not loaded because all WHEN clauses were failed.
      0 Rows not loaded because all fields were null.
    
    
    Space allocated for bind array:                 115584 bytes(64 rows)
    Read   buffer bytes: 1048576
    
    Total logical records skipped:          0
    Total logical records read:            36
    Total logical records rejected:        36
    Total logical records discarded:        0
    
    Run began on Mon Aug 19 15:32:00 2013
    Run ended on Mon Aug 19 15:32:01 2013
    
    Elapsed time was:     00:00:00.31
    CPU time was:         00:00:00.11
    I dont know why i m getting datas not loaded... can anyone point my mistake.

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    My guess is that you've specified that the fields are separated by a space but you have 2 spaces between the first two fields.
    Why are you using a space as the delimiter? Why not use something that's a bit easier to see & debug, such as a comma, or a semicolon, or something/anything that can actually be better seen by the naked eye?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    He has tabs between date and customer_code. Could one of the spaces before order_amount be a tab?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •