Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Unanswered: Zero's being trimmed at Insert using SQL Loader

    When a record from my flat-file begins with 0 the 0 is being dropped from the insert using SQLLDR. For example, in the flat file:

    2003100612345678968401
    2003100609876543268150

    Starting at position 9 and going to position 17 the data inserted should be 098765432 but the 0 is dropping and I am ending up with 98765432. I need the 0.

    My control file looks like this:

    LOAD DATA
    INFILE *
    INTO TABLE pyrl_hewitt_test
    APPEND
    (
    dte POSITION (1:8) char,
    ssn POSITION (9:17) char,
    id POSITION (18:32) char
    )

    I tried chaging the data type to INTEGER EXTERNAL, but that didn't work either. Any help would be greatly appreciated.

    Thanks.

  2. #2
    Join Date
    Oct 2003
    Location
    INDIA
    Posts
    7

    Re: Zero's being trimmed at Insert using SQL Loader

    Originally posted by joee
    When a record from my flat-file begins with 0 the 0 is being dropped from the insert using SQLLDR. For example, in the flat file:

    2003100612345678968401
    2003100609876543268150

    Starting at position 9 and going to position 17 the data inserted should be 098765432 but the 0 is dropping and I am ending up with 98765432. I need the 0.

    My control file looks like this:

    LOAD DATA
    INFILE *
    INTO TABLE pyrl_hewitt_test
    APPEND
    (
    dte POSITION (1:8) char,
    ssn POSITION (9:17) char,
    id POSITION (18:32) char
    )

    I tried chaging the data type to INTEGER EXTERNAL, but that didn't work either. Any help would be greatly appreciated.

    Thanks.
    I think that problem is not with loader but with the table u r inserting into. if the field is a numeric field the leading zero will be trimmed. try converting the field to varchar. hope it works.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    he is correct.
    if you describe the table pyrl_hewitt_test
    I almost guaruntee that someone made the
    SSN column numeric instead of varchar.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Oct 2003
    Posts
    6
    Actually, the data type was varchar2, I changed it to char and that seemed to work. I was not aware that a varchar2 will trim the 0 off of the data.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    It has always loaded the zeros for me.
    Very strange.

    There must be some explaination, but I cannot think of one.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jan 2009
    Posts
    2
    Hi,

    I have the same issue but the data type is char yet ! I tried with varchar, not better.
    I only have this problem when I import a CSV file, not in using absolute positions.

    Is there someone having an idea ?

    Thanks !
    Last edited by Albataur; 01-08-09 at 09:24.

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down Bs

    Quote Originally Posted by Albataur
    Hi,

    I have the same issue but the data type is char yet ! I tried with varchar, not better.
    I only have this problem when I import a CSV file, not in using absolute positions.

    Is there someone having an idea ?

    Thanks !
    Either your data is bad or your table structure does not match the controlfile or both.
    Oracle does NOT drop/remove any characters when the columns are varchar or char:
    Code:
    SQL> Create TABLE test2
      2  (
      3   dte varchar2(8)
      4  ,ssn varchar2(9)
      5  ,id  varchar2(5)
      6  );
    
    Table created.
    
    SQL> !sqlldr scott/tiger control=l0.ctl
    
    SQL*Loader: Release 9.2.0.5.0 - Production on Thu Jan 8 08:30:15 2009
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    Commit point reached - logical record count 2
    
    SQL> !cat l0.log
    
    SQL*Loader: Release 9.2.0.5.0 - Production on Thu Jan 8 08:30:15 2009
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    Control File:   l0.ctl
    Data File:      l0.ctl
      Bad File:     l0.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 TEST2, loaded from every logical record.
    Insert option in effect for this table: APPEND
    
       Column Name                  Position   Len  Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    DTE                                   1:8     8           CHARACTER            
    SSN                                  9:17     9           CHARACTER            
    ID                                  18:32    15           CHARACTER            
    
    
    Table TEST2:
      2 Rows successfully loaded.
      0 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:                   2560 bytes(64 rows)
    Read   buffer bytes: 1048576
    
    Total logical records skipped:          0
    Total logical records read:             2
    Total logical records rejected:         0
    Total logical records discarded:        0
    
    Run began on Thu Jan 08 08:30:15 2009
    Run ended on Thu Jan 08 08:30:16 2009
    
    Elapsed time was:     00:00:00.16
    CPU time was:         00:00:00.00
    
    SQL> select * from test2;
    
    DTE      SSN       ID
    -------- --------- -----
    20031006 123456789 68401
    20031006 098765432 68150
    
    SQL>
    Last edited by LKBrwn_DBA; 01-08-09 at 09:37.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Jan 2009
    Posts
    2
    Ok, sorry. It was just Ms Excel changed it ...
    (thanks for your help)

Posting Permissions

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