I use perl to create reports and when I connect to a database with the character set of WE8ISO8859P1 I get a space between each character in a pure text output and a square between each character when output to excel.

Example:

Allowance unlimited MMS v1 - Roaming for VXC

Prints as below when output to a txt file and opened on a windows machine. Displays fine if opened on unix.

A l l o w a n c e u n l i m i t e d M M S v 1 - R o a m i n g f o r V X C

And prints with a little square where each space is above in excel. (won't let me copy and paste anywhere)
I tried all the following below (not all at once just one at a time) and nothing has worked so far.

$ENV{'NLS_CHARACTERSET'} = 'US7ASCII';

$ENV{'NLS_CHARACTERSET'} = 'WE8ISO8859P1';

$ENV{'NLS_LANG'}="AMERICAN_AMERICA.US7ASCII";

$ENV{'NLS_LANG'}="AMERICAN_AMERICA.WE8ISO8859P1"; (when i use the above got ORA-12705: Cannot access NLS data files or invalid environment specified when doing DBI->connect)

system ("NLS_CHARACTERSET=WE8ISO8859P1");
system ("export NLS_CHARACTERSET");

system ("NLS_CHARACTERSET=US7ASCII");
system ("export NLS_CHARACTERSET");

When I use same code to connect to a database with characterset of US7ASCII I do not get this problem.

DB1 (no problem):

select * from nls_database_parameters order by parameter;

PARAMETERVALUE
NLS_CALENDARGREGORIAN
NLS_CHARACTERSETUS7ASCII
NLS_COMPBINARY
NLS_CURRENCY$
NLS_DATE_FORMATDD-MON-RR
NLS_DATE_LANGUAGEAMERICAN
NLS_DUAL_CURRENCY$
NLS_ISO_CURRENCYAMERICA
NLS_LANGUAGEAMERICAN
NLS_LENGTH_SEMANTICSBYTE
NLS_NCHAR_CHARACTERSETAL16UTF16
NLS_NCHAR_CONV_EXCPFALSE
NLS_NUMERIC_CHARACTERS.,
NLS_RDBMS_VERSION10.2.0.4.0
NLS_SORTBINARY
NLS_TERRITORYAMERICA
NLS_TIMESTAMP_FORMATDD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMATHH.MI.SSXFF AM
NLS_TIME_TZ_FORMATHH.MI.SSXFF AM TZR



DB2:

select * from nls_database_parameters order by parameter;

PARAMETERVALUE
NLS_CALENDARGREGORIAN
NLS_CHARACTERSETWE8ISO8859P1
NLS_COMPBINARY
NLS_CURRENCY$
NLS_DATE_FORMATDD-MON-RR
NLS_DATE_LANGUAGEAMERICAN
NLS_DUAL_CURRENCY$
NLS_ISO_CURRENCYAMERICA
NLS_LANGUAGEAMERICAN
NLS_LENGTH_SEMANTICSBYTE
NLS_NCHAR_CHARACTERSETAL16UTF16
NLS_NCHAR_CONV_EXCPFALSE
NLS_NUMERIC_CHARACTERS.,
NLS_RDBMS_VERSION10.2.0.3.0
NLS_SORTBINARY
NLS_TERRITORYAMERICA
NLS_TIMESTAMP_FORMATDD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMATHH.MI.SSXFF AM
NLS_TIME_TZ_FORMATHH.MI.SSXFF AM TZR

DBA won't change the NLS_CHARACTERSET on DB2. I already asked.

Any help would be greatly appreciated.

-Josh

In reply to DBI NLS_CHARACTERSET issue by Joshd

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.