November 16th, 2012, 02:01 PM
SQL Loader: Load single column
I am trying to load a single column into a Table. Below is the ctl file:
INTO TABLE "ECOMM"."CC_ECOMM"
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:
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
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
November 16th, 2012, 07:05 PM
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:
infile "/home/DEV/files/CC_ECOMM.TXT" "str '\r\n'"
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
November 19th, 2012, 12:16 PM
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
November 20th, 2012, 04:03 PM
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:
> cat CC_ECOMM.TXT
> 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
November 21st, 2012, 11:16 PM
Please try this
*** assuming your data file name is emails.txt
into table emails
fields terminated by ' '
will work for sure