• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

how to load specific data with Oracle DB with sqlldr?

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
jl66
Asked:
jl66
  • 5
  • 3
  • 3
  • +2
3 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
sdstuberCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
sdstuberCommented:
>>> your load is removing the " at the beginning and at the end.

yep, my reading was they are not part of the data.
0
 
johnsoneSenior Oracle DBACommented:
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
 
sdstuberCommented:
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
 
jl66Author Commented:
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
 
sdstuberCommented:
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
 
jl66Author Commented:
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
 
sdstuberCommented:
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
 
jl66Author Commented:
Thanks a lot.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 5
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now