Solved

how to load specific data with Oracle DB with sqlldr?

Posted on 2015-01-26
13
429 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
  • 5
  • 3
  • 3
  • +2
13 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 40 total points
Comment Utility
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
Comment Utility
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 34

Accepted Solution

by:
johnsone earned 230 total points
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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 34

Expert Comment

by:johnsone
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
>>> your load is removing the " at the beginning and at the end.

yep, my reading was they are not part of the data.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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 73

Assisted Solution

by:sdstuber
sdstuber earned 230 total points
Comment Utility
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
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
Thanks a lot.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now