Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2007
    Posts
    26

    Data loading error using sqlldr

    Hi all,
    I am getting an error when trying to load a table in Oracle XE using sqlldr.When i invoke the sqlldr, it closes with an error("Sqlldr needs to close") and am unable to pin point the error. My ctl file is as below:

    LOAD DATA
    infile 'D:\oracle-files\data\newmoney.txt'
    INTO TABLE newmoney
    FIELDS TERMINATED BY ','
    (SSN_ID,BRANCH_NUM,JOB_CDE,EMPL_FIRST_NAME,EMPL_LA ST_NAME);

    I typed in the following from the command line
    sqlldr scott/tiger control=D:\oracle-files\data\newmoney.ctl

    What am i not doing correctly?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,817
    Quote Originally Posted by sueamus
    Hi all,
    I am getting an error when trying to load a table
    What is the exact error message?
    Do you have a logfile?
    If not you could try

    sqlldr scott/tiger control=D:\oracle-files\data\newmoney.ctl log=loader.log

    and check the log file afterwards

  3. #3
    Join Date
    Jan 2007
    Posts
    5
    You may use one of those utilities
    http://www.sqlmanager.net/en/products/oracle

  4. #4
    Join Date
    Apr 2007
    Posts
    26
    Shammat, i checked the log file and got the following error "error on table empl, column SERVICE_DT.ORA-00984: column not allowed here."
    In my ctl file, i formatted the columns by giving them start and end positions and for the dates, gave the date format in the data file.I did not paste the whole table definition.Here is the ctl file with the definitions:

    LOAD DATA
    infile 'D:\oracle-files\data\newmoney.txt'
    INTO TABLE newmoney
    TRAILING NULLCOLS
    (SSN_ID position(1:12) ,
    BRANCH_NUM position(13:14) ,
    JOB_CDE position(15:16) ,
    HIRE_REHIRE_DT position(17:26) "YYYY-MM-DD",
    EMPL_FIRST_NAME position(28:29) ,
    LAST_NAME position(30:33) ,
    DEPT_NAME position(34:44) ,
    DEPT_NAME position(45:75) ,
    REVIEW_DT position(76:85) "YYYY-MM-DD",
    NEXT_REVIEW_DT position(87:96) "YYYY-MM-DD",
    SERVICE_DT position(98:107) "YYYY-MM-DD")

    Thanks.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,817
    Your first example indicated that the input file is comma-separated, why are you using absolute positions in the second control file?

    Additionally the error message does not match the given control file. The error messages says "table empl" but the control file says "INTO TABLE newmoney". Are you sure you are using the correct control file and are connecting the correct database?

    I have made the experience that for DATE (or TIMESTAMP) columns it is better to explicitely define them as DATE in the control file, but let SQL*Loader determine the data type for all others.
    This is an example of one of my control files:
    Code:
    LOAD DATA CHARACTERSET UTF8
    TRUNCATE
    INTO TABLE PARTNER_PROFILE
    FIELDS TERMINATED BY '|'
        TRAILING NULLCOLS
    (
        partner_id,
        created_date DATE "DD-MN-YYYY",
        created_login,
        last_upd_date DATE "DD-MN-YYYY",
        name,
        partner_flag,
        site,
        status,
        stage
    )

  6. #6
    Join Date
    Apr 2007
    Posts
    26
    Shammat, do not worry about the confusion above of the control file and the error message.Thank you so much, that just worked fine with the date.

    However, i am still having problems with timestamp,it brings an error " ORA-01830: date format picture ends before converting entire input string" when i replace date with timestamp in the field in question. What do i do in this case?
    The format for the timestamp in the data file is "YYYY-MM-DD-HH24.MM.SS.SSSSSS" ,but my format in the ctl file is YYYY-MM-DD-HH24.MM.SS.SSSSS"

  7. #7
    Join Date
    Nov 2003
    Posts
    2,817
    Quote Originally Posted by sueamus
    The format for the timestamp in the data file is "YYYY-MM-DD-HH24.MM.SS.SSSSSS" ,but my format in the ctl file is YYYY-MM-DD-HH24.MM.SS.SSSSS"
    Are you sure this format is correct? SSSSS is (according to the manual) "Seconds past midnight". But I guess you probably have milliseconds at the end, right? As far as I know Oracle does not support that. So you will need to remove the milliseconds at the end.

    I had to do something similar as well, and ended up with using substr and to_date(), so you could try the following:

    Code:
    (
      ...
      DATECOL "to_date(substr(:DATECOL ,1,21),'YYYY-MM-DD-HH24.MM.SS')",
      ...
    )
    (not sure if I counted the characters correctly in the substr() call)

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,427
    If the version of oracle is 9 or above, and the column type is timestamp, milliseconds are supported. But the format mask would be

    YYYY-MM-DD-HH24.MM.SS.FFFFF"
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Apr 2007
    Posts
    26
    Thanks all.Beilstwh's solution works fine.

Posting Permissions

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