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!

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

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
'"

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

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