Re: !! SQL*Loader versus Import/Export !!

Dennis Harvey (harvey_at_metropolis.com) wrote:
: I am trying to load data from a non-Oracle database where some of the
: data destined for varchar2(255) fields has embedded <CR><LF>s
: (carriage return, linefeeds) in them. I WANT to retain the <CR><LF>
: in the data. Please take in on faith that there is a good reason
: why I want to retain these in the data.

Ok. (Geez, those pesky users are SO demanding sometimes...)

The default (possibly depending on platform, I'm DOS/Netware Oracle7) is "stream" for the data loader, this makes the data loader think that a <CR><LF> is the end of a (possibly) variable length record. If you can dump the data from the non-Oracle database in FIXED length text records, then you should be able to define the load by field position. I have never done this with embedded <CR><LF>, so take my advice for what it is worth.

1 possible ugly/stupid/brute force method:

	Before the Oracle data load, convert the <CR><LF> codes to 
	another "fake" code, then do a simple data load into Oracle.
	Then in Sqlplus, use an update with DECODE/substr concatenation
	functions or TRANSLATE looking for the "fake" codes???

	This may be painfully slow for large tables.

Maybe a data loader guru knows an easier/more generic way to do it right.

:
: An example of the type of data I want to load is similar to this
: SQL statement:
:
: insert into mytable (mytext)
: values
: ('line one' || CHR(13) || '
: line two' || CHR(13) || '
: line three');
:
: I have been looking for a way to do this using
: SQL*Loader. Does anyone have any ideas for an easy way to do this?
: Would IMPORT work?

NO NO NO. IMPort is an Oracle utility that will only process special data files produced by the Oracle EXPort utility. It is used for backups, database defragmentation/reorganization, and migrating data between Oracle versions and/or hardware-OS platforms.

:
: Thanks in advance,
:
: Dennis Harvey
: harvey_at_metropolis.com
: Metropolis Software Inc.

BTW IMHO, the Oracle loader documentation is pretty weak in good examples, if you can call Oracle tech support and beg for any "internal" draft bulletins or training materials on the load utility, you might get some interesting stuff.

Sorry I couldn't help more,

EP Received on Tue Jan 10 1995 - 03:30:59 CET