Solved

how to load specific data with Oracle DB with sqlldr?

Posted on 2015-01-26
13
452 Views
Last Modified: 2015-01-27
The table structure is
create T(
A number(5) DEFAULT 0 NOT NULL,
B VARCHAR2(4000),
C number(6) NOT NULL
);

Have the following data to be loaded:
11,""This is a test, test twice,"", 101
,""This is a test,test twice. "That is also a hard test", isn't it?"", 105
....

It looks the delimiter like comma for the control file, but it applies everywhere except the middle part which are double double quotation marks "" "". Inside the double double quotation marks there are possibly the commas.

Can any gurus shed some lights on this situation on how to write the control file?
Thanks in advance.
0
Comment
Question by:jl66
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +2
13 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 40 total points
ID: 40571735
I know as soon as I say this I'll be proven wrong but...

I don't see how you can get what you want.  Not with straight sql loader.  You might be able to come up with some pre-processing logic to clean up the data before running sql loader but I don't think I can come up with that logic.

If you can figure out the pattern to strip out the 'bad' you might be able to use sed and/or awk but the logic involved may be to complex for even that if you want the double quotes to remain in the text after load.

Maybe some fancy replace with sed/awk followed by a un-replace in sql loader.  I'm just not sure how to replace the originals with specific values to know to un-replace them later.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 40572122
Can you provide the sample data output which you are expecting to see in your column B after sqlldr loading for those 2 sample input records which you have given ?
0
 
LVL 35

Accepted Solution

by:
johnsone earned 230 total points
ID: 40572553
Your problem is that you are using " as the optionally enclosed by with a field that contains that character.  You need to change that enclosed by character.  If this is the only character field, then you could probably do something with sed/awk to change the first and last one, but if there is more than one field, it is impossible to determine which one is the correct one to change.

Also, the default value will not kick in on the field.  This is because SQL*Loader will insert a NULL into the field.  NULL does not get replaced by the default.  You can either do it with a trigger, or with the control file.

That being said, I modified the data to have a - character as the optionally enclosed by character in the data for the 2 rows you supplied.  It seemed as the sample rows did not contain that character.  I was able to successfully load the data with this control file.

LOAD DATA
	INFILE *
	DISCARDMAX 9999
	APPEND INTO TABLE t
	fields terminated by "," optionally enclosed by "-"
	trailing NULLCOLS
	(
		A "NVL(:A, 0)",
		B,
		C
	)
BEGINDATA
11,-"This is a test, test twice,"-, 101
,-"This is a test,test twice. "That is also a hard test", isn't it?"-, 105

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 40572587
This worked for me with your data left unchanged.  (i.e. no sed, no awk, no need to alter your delimiters)

I created control file x.ctl

LOAD DATA
INFILE '/tmp/x'
INTO TABLE t
APPEND
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY X'2222'
(
  A "NVL(:A, 0)",
  B,
  C
)

Open in new window



I simply put your data into file /tmp/x.dat

and  

sqlldr myuser@mydb control=x.ctl

works just fine

and this is the data that ends up in the table

A     B                                                                   C      
11    This is a test, test twice,                                         101    
0     This is a test,test twice. "That is also a hard test", isn't it?    105

Open in new window

0
 
LVL 35

Expert Comment

by:johnsone
ID: 40572623
sdstuber,

your load is removing the " at the beginning and at the end.  I believe those are part of the data and need to be preserved.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40572638
>>> your load is removing the " at the beginning and at the end.

yep, my reading was they are not part of the data.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40572720
Yeah, that really could go either way.  Depends on asker.  Expected results was asked for but not seen yet.  Either way, one of the 2 methods should work.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 230 total points
ID: 40572757
and actually the use of hex representation wasn't needed, just habit I got into from another project I worked on

this works just as well as what I posted above

LOAD DATA
INFILE '/tmp/x'
INTO TABLE t
APPEND
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '""'
(
  A "NVL(:A, 0)",
  B,
  C
)

Open in new window

0
 

Author Comment

by:jl66
ID: 40573694
Thanks all tips from the top experts in this field. It worked in most of situations. One question remains when the length of column B is > 500 characters long. It stopped working. Those lines were thrown out as bad lines. Is there any way to remedy this? Thanks in advance.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40573702
for 500 characters that shouldn't be a problem.

please open a new question and post exactly what you tried including some sample data that caused the problem
0
 

Author Comment

by:jl66
ID: 40573730
Actually the length column B is around 370 characters long, esp some blanks in the column. It is no good. Are there any parameters we can specify in control file?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40573737
from what you have posted, 370 characters should be fine.

this looks like a new issue and different question than what was originally asked here, please create a new question and post exactly what you tried along with the sample data that caused the problem.
0
 

Author Closing Comment

by:jl66
ID: 40573751
Thanks a lot.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question