Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    9

    Unanswered: Allow space in data

    Hey there,

    i suppose i have a very simple common but a problem in sqlldr.

    I want to allow blank space in one of my columns.

    say if i want to load the following
    'amol '
    ' amol '
    ' amol '
    ' amol '

    it gets loaded but i want toi retain the trailing blank characters. what i find in table is the trailing space is truncated and i get data length as
    4
    5
    6
    7

    e.g 7 includes three prefixed blank characters and the 4 alphabets.

    i want to see a regular length of 10 in each row.

    any pointers?? any option in the control file of sqlldr??


    Thanks,
    Amol C

  2. #2
    Join Date
    Jun 2004
    Posts
    9

    uuufff

    now what on earth is a 'blank space' ??? poor term, ain't it?

    hey just dropped in to convey that the length of the data is 10.

    'amol '

    4 characters + 6 "blank spaces"

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Slightly modify the control file ... add the column length where needed.

    Let's say, for example, that your control file looks like this:
    Code:
    LOAD DATA
    INFILE some_file.txt
    INTO TABLE your_table
    (column_name POSITION(001:010) CHAR)
    When loading, you'll lose trailing blanks. To prevent this to happen, the last row in example control file should look like this:
    Code:
    (column_name POSITION(001:010) CHAR(10))
    P.S. I guess it is about Oracle SQL*Loader utility ... if so, I'd suggest you to post questions regarding Oracle in the Oracle forum.
    Last edited by Littlefoot; 02-25-05 at 07:39.

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

    Wink

    User the PRESERVE BLANKS option:
    Code:
    LOAD DATA
    INFILE some_file.txt
    INTO TABLE your_table
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
    PRESERVE BLANKS
    (COL1, COL2, ...etc...
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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