#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    16
    Rep Power
    0

    SQL Loader: Load single column


    Hi,

    I am trying to load a single column into a Table. Below is the ctl file:

    OPTIONS (ERRORS=10000)
    LOAD DATA
    TRUNCATE
    INTO TABLE "ECOMM"."CC_ECOMM"
    TRAILING NULLCOLS
    (EMAIL_ID)

    Calling loader:
    sqlldr silent=ALL userid=username/password@DB control=/home/DEV/control/CC_ECOMM.CTL log=/home/DEV/log/Unsubs.log bad=/home/DEV/log/Unsubs.bad discard=/home/DEV/log/Unsubs.dis data=/home/DEV/files/CC_ECOMM.TXT

    Though the count in the Table after load is the same as the input file, they are all NULL.

    I tried adding FIELDS TERMINATED BY X'A' for new line and also FIELDS TERMINATED BY X'D' for carriage return. Both times bad file was created and the records that were loaded were again NULL.

    The input file has a list of emails:
    iatraveler2008@aol.com
    iaz65@aol.com
    2blue2brown@comcast.net
    2c3mwilson@embarqmail.com
    abigailolschan@comcast.net
    imisskoco@aol.com

    I tried FIELDS TERMINATED BY X'10' and FIELDS TERMINATED BY X'13' too for new line and carriage return respectively. This time there were no bad file created, but the Table has Null values.
    ******************************************
    Log File contents:

    Number to load: ALL
    Number to skip: 0
    Errors allowed: 10000
    Bind array: 64 rows, maximum of 256000 bytes
    Continuation: none specified
    Path used: Conventional
    Silent options: FEEDBACK, ERRORS and DISCARDS

    Table "ECOMM"."CC_ECOMM", loaded from every logical record.
    Insert option in effect for this table: TRUNCATE
    TRAILING NULLCOLS option in effect

    Column Name Position Len Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    EMAIL_ID FIRST * O(X13) CHARACTER


    Table ""ECOMM"."CC_ECOMM"":
    914361 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: 16512 bytes(64 rows)
    Read buffer bytes: 1048576

    Total logical records skipped: 0
    Total logical records read: 914361
    Total logical records rejected: 0
    Total logical records discarded: 0
    ******************************************

    Please help!
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    If the input file is from windows(i.e. each record ends with a carriage return and line feed) then the below example should work for you:
    Code:
    --Control file:
    Load
     infile       "/home/DEV/files/CC_ECOMM.TXT" "str '\r\n'"
     badfile      "/home/DEV/log/Unsubs.bad"
     discardfile  "/home/DEV/log/Unsubs.dis"
     replace
     into table ecomm.cc_ecomm
     ( email_id )
    
    
    sqlldr silent=ALL userid=username/password@DB control=/home/DEV/control/CC_ECOMM.CTL log=/home/DEV/log/Unsubs.log
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    16
    Rep Power
    0
    I am getting the following error when i specify "str '\r\n'" with the infile path:

    SQL*Loader-510: Physical record in data file (/home/DEV/files/CC_ECOMM.TXT) is longer than the maximum(1048576)
    SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

    Data File: /home/DEV/files/CC_ECOMM.TXT
    File processing option string: "str '^M
    '"
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    Does your input file(CC_ECOMM.TXT) have a carriage return and line feed(i.e. \r\n) at the end of each record?
    See example from the example data you supplied below:


    Code:
    > cat CC_ECOMM.TXT
    iatraveler2008@aol.com
    iaz65@aol.com
    2blue2brown@comcast.net
    2c3mwilson@embarqmail.com
    abigailolschan@comcast.net
    imisskoco@aol.com
    
    
    > od -c CC_ECOMM.TXT
    0000000   i   a   t   r   a   v   e   l   e   r   2   0   0   8   @   a
    0000020   o   l   .   c   o   m  \r  \n   i   a   z   6   5   @   a   o
    0000040   l   .   c   o   m  \r  \n   2   b   l   u   e   2   b   r   o
    0000060   w   n   @   c   o   m   c   a   s   t   .   n   e   t  \r  \n
    0000100   2   c   3   m   w   i   l   s   o   n   @   e   m   b   a   r
    0000120   q   m   a   i   l   .   c   o   m  \r  \n   a   b   i   g   a
    0000140   i   l   o   l   s   c   h   a   n   @   c   o   m   c   a   s
    0000160   t   .   n   e   t  \r  \n   i   m   i   s   s   k   o   c   o
    0000200   @   a   o   l   .   c   o   m  \r  \n
    0000212
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    1
    Rep Power
    0
    Please try this

    *** assuming your data file name is emails.txt

    load data
    infile emails.txt
    truncate
    into table emails
    fields terminated by ' '
    trailing nullcols
    (email_id)


    will work for sure

IMN logo majestic logo threadwatch logo seochat tools logo