Re: SQL Loader

"andrew" <a_g_pears_at_hotmail.com> wrote in message news:fed27dee.0302210300.3fcd564f_at_posting.google.com...
> I am attempting to load ASCII data files (created from SQL server using
bulk
> copy) into an oracle (v8.0) database. The problem is with long
datatypes -
> some contain carriage returns so the loader assumes that the record is
> finished when it is not. The data file is tab delimited, and the long
> datatypes are of variable length. I am using a DOS system so the "VAR"
> option does not appear to help - and anyway, I need to use specify
trailing
> nulcolls. It is not possible to enclose the offending datatypes in quotes
> and I need to preserve the carriage returns in the datatypes. I have no
idea
> what characters will in the future be found in the datatypes so inserting
a
> dummy character to mark a carriage return and then later using SQL to
> re-insert the carriage return is risky. Do you have any suggestions of
how
> to overcome this problem?
>
> Thanks!

Is the TAB character present also at the end of what you call a "long datatype" ?
For what I see, you must find a way to add some special character(s) to mark the end of each record in the source file and specify the same character(s) as record terminator using the "str" OSD specifier. If you're not going to specifiy how long is each record, then you must rely on some form of record terminator, there is no way out. Or you could try padding the short records up to the required fixed length, using the "fix" OSD specifier.
Or, for instance, you could specify CHR(0) in this way "str X'00'". Probably CHR(0) is not going to occur so often in your data, but if you don't like it, you can use any other character sequence. If you are sure that a tab/newline combination marks the end of each record, you can specify that as "str X'080D0A'" (assuming CHR(8) is ASCII TAB, I can't remember).
In the end you must take a decision, you can't expect the system to work magically.

Bye,
Flavio Received on Fri Feb 21 2003 - 09:55:05 CST