Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2013
    Posts
    5

    Unanswered: Bug in sqlldr using trim in ctl

    Hi Guys,
    When I am trying to upload data using ctl file having trim in one of the fields, the length of that column in database is not being considered.

    The control file is
    OPTIONS (SKIP=1, ERRORS=99999999, DIRECT=TRUE )
    LOAD DATA
    CHARACTERSET WE8ISO8859P15
    APPEND
    INTO TABLE s_test_trim
    WHEN RECORD_TYPE='D'
    FIELDS TERMINATED BY '|'
    TRAILING NULLCOLS
    (
    static_1 CONSTANT "<DATE>"
    ,static_2 CONSTANT "<REGION>"
    ,static_flag EXPRESSION "CASE WHEN :status = 'A' THEN 'V' ELSE 'I' END"
    ,field_1 CHAR "TRIM(:field_1)"
    )

    The ddl for table is
    CREATE TABLE s_test_trim
    (
    static_1 DATE NOT NULL
    ,static_2 VARCHAR2(10) NOT NULL
    ,static_flag VARCHAR2(1) DEFAULT 'V' NOT NULL
    ,field_1 VARCHAR2(10)
    )
    COMPRESS
    ;

    Now, when I am trying to load data of field length > 10 for field_1, it is allowing me to do so.
    I cannot find any reference on any of the web sites.
    Please guide me.

    I am using 11g
    Last edited by ankitgg2006; 09-17-13 at 05:16.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ankitgg2006 View Post
    Code:
    CREATE TABLE s_test_trim
    (
    ...
      ,field_1      VARCHAR2(10)
    )
    Now, when I am trying to load data of field length > 10 for field_1, it is allowing me to do so.
    So, you're saying that, despite being declared as VARCHAR2(10), the loaded values appear to be longer than 10 characters?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Bug in sqlldr using trim in ctl
    Then you need to file bug report with Oracle Support
    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.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Just an idea (which is most probably wrong): saying that the column's datatype is VARCHAR2(10), what does "10" represent: bytes or characters? You know, multibyte characters and stuff ...

  5. #5
    Join Date
    Sep 2013
    Posts
    5
    I tried till 15 characters and they are all getting loaded

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The records getting loaded and truncated at a length of 10 or loaded with a length of 15?

  7. #7
    Join Date
    Sep 2013
    Posts
    5
    The ddl is defined for column length of 10. I am trying to load data with length of 15 and the complete data is getting loaded into the DB column.

  8. #8
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Quote Originally Posted by ankitgg2006 View Post
    The ddl is defined for column length of 10. I am trying to load data with length of 15 and the complete data is getting loaded into the DB column.
    You're managing to fit 15 chars into a 10 char column???
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  9. #9
    Join Date
    Sep 2013
    Posts
    5
    Yes,
    that's the issue

  10. #10
    Join Date
    Sep 2013
    Posts
    5
    Hi,

    I have realized the source of problem here.

    It is the parameter DIRECT=TRUE in ctl file

    When I am giving the parameter, it is ignoring the length defined for the columns in the table and is inserting the data.

    But, is it the desired functionality?

Tags for this Thread

Posting Permissions

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