Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2009
    Posts
    3

    Unanswered: sqlldr giving me a hard time!

    Table:

    CREATE TABLE Assignment_I3 (AID char(3), EID char(3), Cnum char(3), SID char(3), Adate date, Hours number(2), PRIMARY KEY (AID), FOREIGN KEY (EID) REFERENCES Employee_3, FOREIGN KEY (SID) REFERENCES Service_3, FOREIGN KEY (Cnum) REFERENCES Cottage_3);

    Assignment_I3 file:
    LOAD DATA
    INFILE *
    REPLACE INTO TABLE Assignment_I3
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    (AID, EID, Cnum, SID, Adate, Hours)
    BEGINDATA
    a01,e08,101,s02,15-MAY-07,3
    a02,e03,101,s03,16-MAY-07,4
    a03,e02,102,s02,17-MAY-07,2
    a04,e05,103,s01,14-MAY-07,3
    a05,e06,105,s05,18-MAY-07,5
    a06,e06,107,s04,15-MAY-07,3
    a07,e03,108,s04,18-MAY-07,4
    a08,e09,109,s01,20-MAY-07,5
    a09,e08,106,s06,15-MAY-07,3
    a10,e10,102,s04,15-MAY-07,2
    a11,e02,110,s04,15-MAY-07,4
    a12,e10,103,s05,15-MAY-07,5
    a13,e05,105,s04,16-MAY-07,4
    a14,e10,107,s04,17-MAY-07,3
    a15,e09,110,s01,18-MAY-07,3


    For some reason, it's telling me that all the numbers in the last column, are not a valid numbers! Anyone know how to fix this?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    CREATE TABLE assignment_i3 ( 
      aid   CHAR(3), 
      eid   CHAR(3), 
      cnum  CHAR(3), 
      sid   CHAR(3), 
      adate DATE, 
      hours NUMBER(2), 
         PRIMARY KEY ( AID ), 
         FOREIGN KEY ( EID ) REFERENCES Employee_3, 
         FOREIGN KEY ( SID ) REFERENCES Service_3, 
         FOREIGN KEY ( Cnum ) REFERENCES Cottage_3);
    Error? What error?
    use CUT & PASTE of whole session so we can see exactly what you see.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2009
    Posts
    3
    The error is related to the last column. According to the log, it says that all the numbers in the last column is an invalid number. It doesn't actually show the error, but when I go to sqlplus, and type in select * from Assignment_I3;, it says no rows selected, but when I go to my files, and go to the log file of my sql loader, it says that the rows weren't added because the last numbers in the column was invalid.

    edit: sorry let me clarify, the numbers under hours in the loader file are not valid for some reason.
    Last edited by Smiley5; 02-18-09 at 15:28.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I'm not sure why this is the case, but I always have to add TRAILING NULLCOLS
    to the controlfile:
    Code:
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (AID, EID, Cnum, SID, Adate, Hours)

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >it says that the rows weren't added because the last numbers in the column was invalid.
    Not a valid/actual Oracle entry/response.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Feb 2009
    Posts
    3
    I tried what shammat told me, still didn't work (I also tried putting quotes to the last column).

    Anacedent, it is in the log saved after the sqlldr initiates, not in the response on the Oracle server. On the Oracle server it says 'logical record count 14', and then 'logical record count 15'.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I can't see any problem & refuse to guess.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    51
    rewrite your data like followings

    a01,e08,101,s02,"15-MAY-07",3
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

  9. #9
    Join Date
    Feb 2009
    Posts
    62
    I know it won't help much, but it worked perfectly for me (once I'd stripped the FK syntax off the end of your Create table statement.

    I made a controlfile called sqlldr.par containing all the etails you posted, and did this:
    Code:
    H:\Temp>sqlldr dev/dev@dev10g control=sqlldr.par
    
    SQL*Loader: Release 10.2.0.4.0 - Production on Thu Feb 19 15:40:22 2009
    
    Copyright (c) 1982, 2007, Oracle.  All rights reserved.
    
    Commit point reached - logical record count 16
    
    H:\Temp>

  10. #10
    Join Date
    Jan 2009
    Posts
    17
    LOAD DATA
    INFILE *
    REPLACE INTO TABLE Assignment_I3
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    (AID, EID, Cnum, SID, Adate, Hours)
    BEGINDATA
    a01,e08,101,s02,"15-MAY-07",3
    a02,e03,101,s03,"16-MAY-07",4
    a03,e02,102,s02,"17-MAY-07",2
    a04,e05,103,s01,"14-MAY-07",3
    a05,e06,105,s05,"18-MAY-07",5
    a06,e06,107,s04,"15-MAY-07",3
    a07,e03,108,s04,"18-MAY-07",4
    a08,e09,109,s01,"20-MAY-07",5
    a09,e08,106,s06,"15-MAY-07",3
    a10,e10,102,s04,"15-MAY-07",2
    a11,e02,110,s04,"15-MAY-07",4


    C:\Documents and Settings\sridhar.i>sqlldr userid=scott@orcl/tiger control='c:\
    2.ctl' log='21.log'

    SQL*Loader: Release 10.2.0.1.0 - Production on Fri Feb 20 19:01:02 2009

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    Commit point reached - logical record count 10
    Commit point reached - logical record count 11

    select * from Assignment_I3

Posting Permissions

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