Solved

how to load specific data with Oracle DB with sqlldr?

Posted on 2015-01-26
13
442 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 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 34

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

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 34

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
passing parameters to sql script oracle 4 61
ORA-00923: FROM keyword not found where expected 3 80
Use of Exception to end a Loop 3 45
Checking for column width 8 29
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the dā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

829 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