Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

how to load specific data with Oracle DB with sqlldr?

Posted on 2015-01-26
13
Medium Priority
?
473 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 160 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 920 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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 920 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

636 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