sqlldr delimiters in columns

[supenguin]

I've run into an issue today with sqlldr. Our control file current has:

  fields terminated by "|" optionally enclosed by '"'

This has worked great up until this weekend when we got a record with commas in one of the fields. This has thrown off the processing of every record after the ones with the comma in it.

The .csv file is generated by a Perl script, so we do have control over the format of the data, but not the data itself -it is pulled from a database that we have no control over. We tried delimiting the commas in the data with a backslash, but that didn't help. Every double backslashes didn't do the trick.

Our solution for now is to use the | character (pipe character). But if we ever get those in our data, it will be the same issue all over again.

Is there any way to delimit the field separator so sqlld won't mess things up or maybe some way to tell sqlldr to ignore the field separator character when it is inside a quoted string?

[supenguin]

I'm going to be "that guy" and reply to myself with the answer. The column that was causing issues is a VARCHAR2(2000) in our database. The data that was causing errors had 257 characters in that column. Some research turned up that the default length of a field for sqlldr is 255 characters, so if you want to do anything longer, you have to specify that in your control file.

The results look like this:

column_name char(2000),

[PAVB]

Is this forum good or what?
Look at this...
- You got a solution within the hour
- You got the solution from yourself
- Even if the solution doesn't match your description of the issue you are a happy camper.
Isn't that great?