Results 1 to 7 of 7

Thread: sql loader

  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Thumbs down Unanswered: sql loader

    I am trying to load date thru sql loader , it is giving error as

    Record 42: Rejected - Error on table EMP, column EMPNUM.
    ORA-01438: value larger than specified precision allows for this column


    it is giving the same error and failing all records.

    The other thing is if I have 50000 records in my flat file and to load all in single shot do I need to add any option as number of rows are more.

    Thanks,
    sca

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: sql loader

    Originally posted by sca
    I am trying to load date thru sql loader , it is giving error as

    Record 42: Rejected - Error on table EMP, column EMPNUM.
    ORA-01438: value larger than specified precision allows for this column


    it is giving the same error and failing all records.

    The other thing is if I have 50000 records in my flat file and to load all in single shot do I need to add any option as number of rows are more.

    Thanks,
    sca
    My crystal ball is broken.
    You don't tell us what the column definition is.
    You don't give use a sample of the records being loaded.
    So how are we given any meaningful suggestion?
    You're On Your Own (YOYO!)

    P.S.
    The fix is to make a change (or two) so the errors stop ocurring.
    Exactly what changes are left as exercise for you learn.

  3. #3
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120
    This is true, without the column definition it is difficult to pinpoint the problem.
    Best guess from the error message you posted is that you are trying to insert a date value into a column with a Number datatype.
    That won't work!

  4. #4
    Join Date
    Oct 2003
    Posts
    3
    Originally posted by tlael
    This is true, without the column definition it is difficult to pinpoint the problem.
    Best guess from the error message you posted is that you are trying to insert a date value into a column with a Number datatype.
    That won't work!
    I am sorry I misspelled DATA as DATE .

    I am trying to load data type NUMBER

    I mentioned as INTEGER in ctl file for this data type.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    this indicates that your empno column is smaller than the number you are trying to plug in.

    example:
    empno number(3)

    you try to load number "1234"

    error occurs.


    Solution:
    post the table description, and a line from the data file
    you are trying to load, and your control file for sql-loader.

    you probably need to change the precision of the column.

    ie: alter table tablename modify empno number(10)
    or whatever.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Oct 2003
    Posts
    3
    Originally posted by The_Duck
    this indicates that your empno column is smaller than the number you are trying to plug in.

    example:
    empno number(3)

    you try to load number "1234"

    error occurs.


    Solution:
    post the table description, and a line from the data file
    you are trying to load, and your control file for sql-loader.

    you probably need to change the precision of the column.

    ie: alter table tablename modify empno number(10)
    or whatever.


    Thanks, I will modify the column defination and increse the width.

    when I tried to load it is trying to load only first 50 rows of text file. how can I increase to total records of text file

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    you have an 'options' field atthe beginning of your controlfile that
    you can use. Edit the ROWS option to reflect how many you want
    to load. Skip is used to say how many rows at the top of the file to
    skip (like when you have column headings/titles, etc.

    Change the skip, errors (total errors before stopping) and rows to
    reflect your preferences.

    It looks something liek this:

    PHP Code:
    OPTIONS
    SKIP 1,
      
    ERRORS 1000,
      
    ROWS 128,
      
    DIRECT FALSE,
      
    PARALLEL FALSE
    )

    LOAD DATA
    INFILE    
    'bill18.csv'
    BADFILE   'BILLING.bad'
    DISCARDFILE 'BILLING.dis'
    DISCARDMAX 10000000

    REPLACE
    INTO TABLE T_BILLING
    FIELDS TERMINATED BY 
    ',' optionally enclosed by '"' TRAILING NULLCOLS 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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