In your answer about connecting SQL Server 6.5 to Oracle9i you had responded, in part, "Otherwise, you can dump the SQL Server data to comma-delimited files and load them into Oracle with SQL*Loader." Using commas is probably one of the worst choices. Consider the following data stored in a table:
FName Lname Salary Jane Doe 5000 John Doe, Jr 5000 The rows would dump as follows: Jane,Doe,5000 John,Doe, Jr, 5000
The first row would load properly, the second would not.
I prefer to use the | or ~ symbols to delimit.
Then what if your data has the '|' or the '~' symbol? You'd have to use a different symbol and hope that your data doesn't contain that symbol.
To aid in this quest (although it is still not perfect), when you extract data to a flat file using MS Data Transformation Services (DTS), you can make it comma delimited and select to optionally enclose each field in double quotes. So your example would look like the following:
"John","Doe, Jr", 5000
In this case, the number fields don't use the double quotes as they are numbers, not strings. Notice that there are only three fields, even in the second line! The second field of the second line is "Doe, Jr". The comma in that line will be part of the string, not used to delimit the next column.
And if that still does not satisfy the problem, you can always use tab-delimited fields. In either case, Oracle's SQL*Loader (and DTS) support comma delimited files, comma delimited files with optional field characters, and tab delimited files.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.