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;
| PARAMETER | VALUE |
| NLS_CALENDAR | GREGORIAN |
| NLS_CHARACTERSET | US7ASCII |
| NLS_COMP | BINARY |
| NLS_CURRENCY | $ |
| NLS_DATE_FORMAT | DD-MON-RR |
| NLS_DATE_LANGUAGE | AMERICAN |
| NLS_DUAL_CURRENCY | $ |
| NLS_ISO_CURRENCY | AMERICA |
| NLS_LANGUAGE | AMERICAN |
| NLS_LENGTH_SEMANTICS | BYTE |
| NLS_NCHAR_CHARACTERSET | AL16UTF16 |
| NLS_NCHAR_CONV_EXCP | FALSE |
| NLS_NUMERIC_CHARACTERS | ., |
| NLS_RDBMS_VERSION | 10.2.0.4.0 |
| NLS_SORT | BINARY |
| NLS_TERRITORY | AMERICA |
| NLS_TIMESTAMP_FORMAT | DD-MON-RR HH.MI.SSXFF AM |
| NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH.MI.SSXFF AM TZR |
| NLS_TIME_FORMAT | HH.MI.SSXFF AM |
| NLS_TIME_TZ_FORMAT | HH.MI.SSXFF AM TZR |
DB2:
select * from nls_database_parameters order by parameter;
| PARAMETER | VALUE |
| NLS_CALENDAR | GREGORIAN |
| NLS_CHARACTERSET | WE8ISO8859P1 |
| NLS_COMP | BINARY |
| NLS_CURRENCY | $ |
| NLS_DATE_FORMAT | DD-MON-RR |
| NLS_DATE_LANGUAGE | AMERICAN |
| NLS_DUAL_CURRENCY | $ |
| NLS_ISO_CURRENCY | AMERICA |
| NLS_LANGUAGE | AMERICAN |
| NLS_LENGTH_SEMANTICS | BYTE |
| NLS_NCHAR_CHARACTERSET | AL16UTF16 |
| NLS_NCHAR_CONV_EXCP | FALSE |
| NLS_NUMERIC_CHARACTERS | ., |
| NLS_RDBMS_VERSION | 10.2.0.3.0 |
| NLS_SORT | BINARY |
| NLS_TERRITORY | AMERICA |
| NLS_TIMESTAMP_FORMAT | DD-MON-RR HH.MI.SSXFF AM |
| NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH.MI.SSXFF AM TZR |
| NLS_TIME_FORMAT | HH.MI.SSXFF AM |
| NLS_TIME_TZ_FORMAT | HH.MI.SSXFF AM TZR |
DBA won't change the NLS_CHARACTERSET on DB2. I already asked.
Any help would be greatly appreciated.
-Josh