Unanswered: Newline in field causing SQL Loader Problem
I am using SQL Loader (both from inside Toad, and using Oralce SQLloader from command line) to import flat files into an Oracle DB.
The record separator is a newline character.
The pipe | is the field separator.
Some of the records have a newline character in the middle of the field.
This is causing problems with importing the data, as the record either is only imported partially, or data is put into incorrect fields.
Is there a way in a SQL loader script to address this issue without changing the structure of the import file (I get it from someone else, and will have difficulty changing the structure).
Also, in the future, we will be using Informatica to import this same data.
Does anyone know if have newline as the record separator and having newline characters in fields will cause similiar problems with Informatica?
Only I should think, if the records are fixed length; otherwise - how would you ever know when a newline character is part of a field or not? I know of no logical way to test through dts. Perhaps you can write a script to count pipes.