How to handle line breaks in data for importing with SQL Loader

Question

Hello,

I am a front-end developer writing a C# application to export data from an oracle stored procedure into a text file for later importing into a different oracle database. The data will be loaded with SQL Loader (sqlldr) and I am trying to understand that tool's abilities so that I can output the file in a format that will be easy to process with SQL Loader.

I am unsure of how to handle line breaks in the column data. Some of the columns are descriptions, and may contain line breaks. I cannot find an option in SQL Loader to handle the possibility of a non-constant number of line breaks in the data.

It does appear that I could use a sentential value to terminate or start a record in the text file, then use CONTINUEIF. Then that presents me with the issue of handling the sentential value within the data.

I am hesitant to just strip out the breaks because, while not critical for this specific application, I do not want to lessen the fidelity of the data.

Any ideas on how I can approach this?

Thanks!

Answer 1

Isn't it easier to create a database link between those different databases? When you use a database link you no longer need to write a C# app. You just select the data from the other database with a select-statement.

Answer 2

I would use the SQL*Loader Stream Record Format (see the documentation) with a hex byte string delimiter that will not be present in the data. It shouldn't be too hard to find something that will not falsely signal a row termination.

Answer 3

I'd look at EXPDP and IMPDP ahead of a text file. In 10g, you can read/write to external tables using datapump. See here. Your export becomes as simple as

SQL> CREATE TABLE EMP_50
2 ORGANIZATION EXTERNAL
3 ( TYPE oracle_datapump
4 DEFAULT DIRECTORY dmp_dir
5 LOCATION (‘emp_50.dmp'))
6 )
7 AS SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 50
8 ;

You don't have to worry about any exotic characters, date or number conversion/formatting, even raw binary data.