Results 1 to 4 of 4

Thread: SQL loader

  1. #1
    Join Date
    May 2013
    Posts
    33

    Unanswered: SQL loader

    I formatted the datas using tab....and 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
    And this is my control file....

    Code:
    load data
    infile 'F:\sat.csv'
    into table orders
    fields terminated by x'09'
    trailing nullcols
    (ord_number,ord_amount,advanced_amount,ord_date,cust_code,agent_code,ord_description)
    when i m loading datas i got only 9 records loaded into the table and the remaining were not loaded..
    see my process
    Code:
    Microsoft Windows XP [Version 5.1.2600]
    (C) Copyright 1985-2001 Microsoft Corp.
    
    C:\Documents and Settings\Administrator>sqlplus one/two
    
    SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 19 23:04:23 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 23:09:16 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;
    
    ORD_NUMBER ORD_AMOUNT ADVANCED_AMOUNT ORD_DATE  CUST_CODE  AGENT_CODE ORD_DESCRI
    ---------- ---------- --------------- --------- ---------- ---------- ----------
        200100       1000             600 08-JAN-08 C00015     A003       SOD
        200118        500             100 20-JUL-08 C00023     A006       SOD
        200117        800             200 20-OCT-08 C00014     A001       SOD
        200123        500             100 16-SEP-08 C00022     A002       SOD
        200120        500             100 20-JUL-08 C00009     A002       SOD
        200116        500             100 13-JUL-08 C00010     A009       SOD
        200124        500             100 20-JUN-08 C00017     A007       SOD
        200126        500             100 24-JUN-08 C00022     A002       SOD
        200131        900             150 26-AUG-08 C00012     A012       SOD
    
    9 rows selected.
    log file is:

    Code:
    SQL*Loader: Release 10.2.0.1.0 - Production on Mon Aug 19 23:09:16 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
    TRAILING NULLCOLS option in effect
    
       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 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 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 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 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:
      9 Rows successfully loaded.
      27 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:        27
    Total logical records discarded:        0
    
    Run began on Mon Aug 19 23:09:16 2013
    Run ended on Mon Aug 19 23:09:16 2013
    
    Elapsed time was:     00:00:00.44
    CPU time was:         00:00:00.03
    Having doubts while loading datas using tab,whitespace,tab and whitespace

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Did the 9 rows that were loaded load correctly? Here is your datafile, with the tabs replaced by '<tab>'
    Code:
    200100<tab>1000.00<tab>600.00<tab>08-JAN-08<tab>C00015<tab>A003<tab>SOD
    200110<tab>3000.00<tab>500.00 <tab>15-APR-08 <tab>C00019 <tab>A010 <tab>SOD
    200107 <tab>4500.00 900.00 <tab>30-AUG-08 <tab>C00007 <tab>A010 <tab>SOD
    200112 <tab>2000.00 400.00 <tab>30-MAY-08 <tab>C00016 <tab>A007 <tab>SOD
    200113 <tab>4000.00 600.00 <tab>10-JUN-08<tab>C00022 <tab>A002 <tab>SOD
    200102 <tab>2000.00 300.00 <tab>25-MAY-08<tab>C00012 <tab>A012 <tab>SOD
    200114 <tab>3500.00 1500.00 15-AUG-08 <tab>C00002 <tab>A008 <tab>SOD
    200122 <tab>2500.00 400.00 <tab>16-SEP-08 <tab>C00003 <tab>A004 <tab>SOD
    200118 <tab>500.00<tab>100.00 <tab>20-JUL-08 <tab>C00023 <tab>A006 <tab>SOD
    200119 <tab>4000.00 700.00 <tab>16-SEP-08 <tab>C00007 <tab>A010 <tab>SOD
    200121 <tab>1500.00 600.00 <tab>23-SEP-08 <tab>C00008 <tab>A004 <tab>SOD
    200130 <tab>2500.00 400.00 <tab>30-JUL-08 <tab>C00025 <tab>A011 <tab>SOD
    200134 <tab>4200.00 1800.00 25-SEP-08 <tab>C00004 <tab>A005 <tab>SOD
    200115 <tab>2000.00 1200.00 08-FEB-08 <tab>C00013 <tab>A013 <tab>SOD
    200108 <tab>4000.00 600.00 <tab>15-FEB-08 <tab>C00008 <tab>A004 <tab>SOD
    200103 <tab>1500.00 700.00 <tab>15-MAY-08 <tab>C00021 <tab>A005 <tab>SOD
    200105 <tab>2500.00 500.00 <tab>18-JUL-08 <tab>C00025 <tab>A011 <tab>SOD
    200109 <tab>3500.00 800.00 <tab>30-JUL-08 <tab>C00011 <tab>A010 <tab>SOD
    200101 <tab>3000.00 1000.00 15-JUL-08 <tab>C00001 <tab>A008 <tab>SOD
    200111 <tab>1000.00 300.00 <tab>10-JUL-08 <tab>C00020 <tab>A008 <tab>SOD
    200104 <tab>1500.00 500.00 <tab>15-MAR-08 <tab>C00006 <tab>A004 <tab>SOD
    200106 <tab>2500.00 700.00 <tab>20-APR-08 <tab>C00005 <tab>A002 <tab>SOD
    200125 <tab>2000.00 600.00 <tab>16-OCT-08 <tab>C00018 <tab>A005 <tab>SOD
    200117 <tab>800.00 <tab>200.00 <tab>20-OCT-08 <tab>C00014 <tab>A001 <tab>SOD
    200123 <tab>500.00 <tab>100.00 <tab>16-SEP-08 <tab>C00022 <tab>A002 <tab>SOD
    200120 <tab>500.00 <tab>100.00 <tab>20-JUL-08 <tab>C00009 <tab>A002 <tab>SOD
    200116 <tab>500.00 <tab>100.00 <tab>13-JUL-08 <tab>C00010 <tab>A009 <tab>SOD
    200124 <tab>500.00 <tab>100.00 <tab>20-JUN-08 <tab>C00017 <tab>A007 <tab>SOD
    200126 <tab>500.00 <tab>100.00 <tab>24-JUN-08 <tab>C00022 <tab>A002 <tab>SOD
    200129 <tab>2500.00 500.00 <tab>20-JUL-08 <tab>C00024 <tab>A006 <tab>SOD
    200127 <tab>2500.00 400.00 <tab>20-JUL-08 <tab>C00015 <tab>A003 <tab>SOD
    200128 <tab>3500.00 1500.00 20-JUL-08 <tab>C00009 <tab>A002 <tab>SOD
    200135 <tab>2000.00 800.00 <tab>16-SEP-08 <tab>C00007 <tab>A010 <tab>SOD
    200131 <tab>900.00 <tab>150.00 <tab>26-AUG-08 <tab>C00012 <tab>A012 <tab>SOD
    200133 <tab>1200.00 400.00 <tab>29-JUN-08 <tab>C00009 <tab>A002 <tab>SOD
    200132 <tab>4000.00 2000.00 15-AUG-08 <tab>C00013 <tab>A013 <tab>SOD
    Notice anything a bit off?

  3. #3
    Join Date
    May 2013
    Posts
    33
    sorry i cant understand what you are saying....In many of the columns u didnt mention tab i.e <tab>, cant understand wat it mean?

    It would be good, if i get a correct control file for the above dat file.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I did not mention a tab, because those tabs are missing in your data file. Since the tab is your delimiter, you don't need a new control file, you need to fix your data file.

Posting Permissions

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