Prev: oracle data pump with virtual column
Next: Triggers
From: The Magnet on 6 Aug 2010 09:37

We are exporting data from MySQL to Oracle. According to the
developers the data from MySQL contains new lines and tabs (\n, \r,
\t).

Is there a way to retain that in the message? The data is being
imported into a CLOB column. Are there CHR for that? I think there
is more than just a replace, so, I'm looking for ideas as I continue
to search.

Thanks!
From: Mark D Powell on 6 Aug 2010 12:04
On Aug 6, 9:37 am, The Magnet <a...(a)unsu.com> wrote:
> We are exporting data from MySQL to Oracle.  According to the
> developers the data from MySQL contains new lines and tabs (\n, \r,
> \t).
>
> Is there a way to retain that in the message?  The data is being
> imported into a CLOB column.  Are there CHR for that?  I think there
> is more than just a replace, so, I'm looking for ideas as I continue
> to search.
>
> Thanks!

If you are going to load the data in question into a CLOB column then
the sqlldr utility has the ability to load LOB's and each LOB can be a
file so the tabs, CR, and NL can be loaded.

You can also load the data via programs that insert the data in
question.

You can shove tab and NL into varchar2 columns for that matter. As
long as the data submitted to Oracle has tab and NL in it Oracle will
store it. If you transfer the data across platforms be sure you do
not translate it such as the ASCII FTP I do between Windows and UNIX
automatically converts Windows text file CR NL combinations in just NL
on windows.

HTH -- Mark D Powell --
From: The Magnet on 6 Aug 2010 13:43
On Aug 6, 11:04 am, Mark D Powell <Mark.Powe...(a)hp.com> wrote:
> On Aug 6, 9:37 am, The Magnet <a...(a)unsu.com> wrote:
>
> > We are exporting data from MySQL to Oracle.  According to the
> > developers the data from MySQL contains new lines and tabs (\n, \r,
> > \t).
>
> > Is there a way to retain that in the message?  The data is being
> > imported into a CLOB column.  Are there CHR for that?  I think there
> > is more than just a replace, so, I'm looking for ideas as I continue
> > to search.
>
> > Thanks!
>
> If you are going to load the data in question into a CLOB column then
> the sqlldr utility has the ability to load LOB's and each LOB can be a
> file so the tabs, CR, and NL can be loaded.
>
> You can also load the data via programs that insert the data in
> question.
>
> You can shove tab and NL into varchar2 columns for that matter.  As
> long as the data submitted to Oracle has tab and NL in it Oracle will
> store it.  If you transfer the data across platforms be sure you do
> not translate it such as the ASCII FTP I do between Windows and UNIX
> automatically converts Windows text file CR NL combinations in just NL
> on windows.
>
> HTH -- Mark D Powell --


Well, maybe not the best way, but I was thinking of replacing the \t
with CHR(9) and the \n with CHR(10). I'll just parse the line. It
comes from MySQL with the line feed & tab stuff, so I'll just do a
parse / replace.

It is a one time thing, so I think that will do.


 | 
Pages: 1
Prev: oracle data pump with virtual column
Next: Triggers