Re: Escape sequences in SQL*Loader

From: J.D. Laub <jjdl_at_iasi.com>
Date: 1997/12/04
Message-ID: <3486e6f2.0@mirage.iasi.com>#1/1

In article <662uu5$bri$1_at_hermes.is.co.za>, "Billy Verreynne" <vslabs_at_onwe.co.za> wrote:
>Morten Myrvold wrote in message <660itn$reb$1_at_elle.eunet.no>...
>>I'm using SQL*Loader to load our database tables from text files. Some of
>>these columns contain text with CR's embedded. My question is: how do I
>>specify a CR (carriage return) in my SQL*Loader data file??? >
><snip>
>
>The only way (other than replacing CR's before loading) is to make use of
>the FIX option in SQL*Loader

There's also the VAR option which exactly fits Billy's needs. I had to bug support last February to find out about it since (from what I can tell) it's only documented in the problem repository (attached). I've found that TRAILING NULLCOLS does not work when sqlldr is in VAR mode. Also, to work around the problem in the "Notes:" section, I end up putting an extra column separator b4 the first char of the first field, then in the .ctl file I specify that my first column starts in position(2).

                              Oracle Corporate Support
                                 Problem Repository

Prob# 1011372.6 HOW TO LOAD CARRIAGE RETURNS IN SQLLOADER?
Soln# 2059405.6 HOW TO LOAD CARRIAGE RETURN THROUGH SQLLOADER?
Prob# 1011372.6 HOW TO LOAD CARRIAGE RETURNS IN SQLLOADER?

Problem ID          : 1011372.6
Affected Platforms  : Generic: not platform specific
Affected Products   : Oracle7 Server
Affected Components : RDBMS Generic
Affected Oracle Vsn : Generic

Summary:
HOW TO LOAD CARRIAGE RETURNS IN SQLLOADER? +=+

Problem Description:

Trying to load data that includes carriage returns, such as large text files.

Search Words: SQL*Loader loader return end line end-of-line \n newline var

+==+

Diagnostics and References:

{7299.6,Y,100} USING SQL*LOADER

ref: {5238.6} BUG-237583

2. Soln# 2059405.6 HOW TO LOAD CARRIAGE RETURN THROUGH SQLLOADER?

Solution ID         : 2059405.6
For Problem         : 1011372.6
Affected Platforms  : Generic: not platform specific
Affected Products   : Oracle7 Server
Affected Components : RDBMS Generic
Affected Oracle Vsn : V07.XX

Summary:
HOW TO LOAD CARRIAGE RETURN THROUGH SQLLOADER? +=+

WARNINGS:

This is only available for UNIX platforms. If you are loading all numeric data, you may have trouble (see Note below).

Solution Description:

Overriding the newline character as the terminator of a variable length record is not possible (at present) on all platforms. However on Unix platforms you can use the "VAR" file processing option to load logical records of up to a maximum length of 99999 characters. (Oracle7 Server for Unix Administrator's reference guide -- look under SQLloader file processing options)

Control file Syntax:

load data
infile '/full_path/filename.dat' "VAR" insert ..
.....

In the datafile count the number of characters that make up your logical record (including the NEWLINE character) and precede this record by the count specified as a 5 digit length field.

For example:

00124This is an example to illustrate combining several physical records\n separated by newline characters into one logical record. 00023This is a small record.

NOTE: In the above example, the '\n' is the return character, so would normally be invisible.

Note:

If the data being loaded has numbers immidiately after the five length-defining numbers, you may have no rows loaded at all. The only workaround for this problem is to place a non-numeric character immediately after the first five numbers. This is bug 237583 (see the 'References' section of this entry).

+==+

References:

J.D. Laub (Laubster) |"I think you're very, very, very, very, very, jjdl_at_iasi.com |very, very, very, very, ..." - Flying Lizards to reply, remove the first letter of my email address (make it jdl) Received on Thu Dec 04 1997 - 00:00:00 CST

Re: Escape sequences in SQL*Loader - Var [01/01]