SQL*Loader and Long data types

Hi,

We are having problems in SQL*Loader loading data into a long datatype- in particular, when we wish to load carriage return/linefeeds and perform substitutions

The data for a single record is split over a number of records; where the record is continued, there is a "*" in the first character.

When the control file is:

LOAD INFILE data.dat APPEND CONTINUEIF THIS (1-1) = '*'   INTO TABLE DEV.DATA FIELDS TERMINATED BY '~' TRAILING NULLCOLS
( TEXT_LINE CHAR(8000) )
the load works OK, but we cannot have linefeeds embedded in the data, as it interprets them as end of record markers.

On VARCHAR fields, we can add a line to change '/n' characters into CR/LF:
( TEXT_LINE CHAR (8000)

        "REPLACE(:TEXT_LINE,'/n',CHR(13)||CHR(10))"

This works fine on VARCHARs, and on LONGs- except where the data exceeds about 2000 characters- we get a message: ORA-01461: can bind a LONG value only for insert into a LONG column

Can anybody tell us how to either load the data without error, or how to insert a CR/LF without using a replace statement (either before the load, or after, perhaps using an SQL statement or a PL/SQL program)?

Thank you

Geoff Received on Mon Sep 28 1998 - 17:54:09 CDT