Re: Help with junk characters

News <news_connection_at_yahoo.fr> wrote:
> I run 9i on unix aix with NLS_LANG AMERICAN_AMERICA.WE8ISO8859P1. I
> generated data for use with SQL Loader from access database using oracle
> workbench migration. The .dat files looks good on windows but when I
> transfer them to unix host using ftp there are junk characters. This happens
> only with files generated by migration tool so there's no problem with ftp
> tool (I tried with 2 different ones). I can transfer normally any other file
> created using any other editor on windows containing special characters. It
> shows well using cat, in vi editor or when I insert data into tables. This
> is not the case of files generated during migration. I cannot figure out
> what's the problem with these files once transferred. The only difference I
> noticed is eof eor tags and whole data as single line.

To get a good response, please clarify what you did and what your problem is.

• How did you "generate data for use with SQL Loader from access database"?
• Please give an example of the junk characters.
• What is "migration tool"? What is "ftp tool"?

I bet you that your problem is the following, correct me if I'm wrong:

Your Windows data contain some of the dreaded "spoil data transfer to non-Windows systems" characters.

Microsoft software is optimized to create these characters; e.g. if you type ordinary characters with Microsoft Word, it will automatically turn them into Windows-specific characters without telling you.

Typical examples for this are single and double left quotes and hyphens.

Microsoft has invented its own codepages (WIN1252 and similar) to facilitate this incompatibility.

If that is indeed your problem, there are different approaches to a solution:

• Try to convert the Windows characters into something reasonable. This can be difficult or even undesirable, if it is important that your data are not changed.
• Change your NLS_LANG setting to a Windows codepage rather than ISO8859-1. This will make the Oracle client treat the Windows characters correctly. If you are lucky, and the codepage of the database is set appropriately, Oracle will convert the characters.
• If your database codepage is not set appropriately, you are out of luck. You cannot store Windows characters in a WE8ISO8859P1 database, for instance. If you can, recreate the database with a Windows codepage or UNICODE (that latter is preferrable).

For more information, consult the Oracle documentation for NLS issues and codepages.

Yours,
Laurenz Albe Received on Wed Nov 02 2005 - 07:14:47 CST