Missing / Wrong characters returnd via CPAN Perl DBI:Oracle ( missing umlauts)

Question

Enviroment: Oracle DB 12.2, NLS_LANG: AL32UTF8 Linux Client with OracleClient 12.2 installed, also Perl CPAN DBI installed V: 1.636

Windows 10 Client with Oracle Client 12.2

Actually we test a new application which connects vie DBI:Oracle and noticed that the special chatracters ( for example: ä,ö,ü ) are missing / returned incorrectly.

So i have done some tests: on linux client:

root@test:~# locale
LANG=de_DE.UTF-8
LANGUAGE=
LC_CTYPE="de_DE.UTF-8"
LC_NUMERIC="de_DE.UTF-8"
LC_TIME="de_DE.UTF-8"
LC_COLLATE="de_DE.UTF-8"
LC_MONETARY="de_DE.UTF-8"
LC_MESSAGES="de_DE.UTF-8"
LC_PAPER="de_DE.UTF-8"
LC_NAME="de_DE.UTF-8"
LC_ADDRESS="de_DE.UTF-8"
LC_TELEPHONE="de_DE.UTF-8"
LC_MEASUREMENT="de_DE.UTF-8"
LC_IDENTIFICATION="de_DE.UTF-8"
LC_ALL=
root@test:~# export NLS_LANG=GERMAN_GERMANY.AL32UTF8
root@test:~# sqlplus connectstring

SQL*Plus: Release 12.2.0.1.0 Production on Mo Nov 4 14:36:14 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Kennwort eingeben:
Letzte erfolgreiche Anmeldezeit: Mo Nov 04 2019 14:35:53 +01:00

Verbunden mit:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

SQL> create table testchar (
  2  ttext varchar2(10));

Tabelle wurde erstellt.

SQL> insert into testchar values ('Ä');

1 Zeile wurde erstellt.

SQL> insert into testchar values ('Ö');

1 Zeile wurde erstellt.

SQL> insert into testchar values ('Ü');

1 Zeile wurde erstellt.

SQL> insert into testchar values ('ß');

1 Zeile wurde erstellt.

SQL> insert into testchar values ('€');

1 Zeile wurde erstellt.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> select * from testchar;

TTEXT
----------
Ä
Ö
Ü
ß
€

SQL>

on windows client:

C:\Users\t>set NLS_LANG=GERMAN_GERMANY.AL32UTF8

C:\Users\t>chcp 65001
Aktive Codepage: 65001.

C:\Users\t>sqlplus connectstring

SQL*Plus: Release 12.2.0.1.0 Production on Mo Nov 4 14:39:43 2019

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

Kennwort eingeben:
Letzte erfolgreiche Anmeldezeit: Mo Nov 04 2019 14:37:11 +01:00

Verbunden mit:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from testchar;

TTEXT
----------
Ä
Ö
Ü
ß
€

SQL>

now on the same linux client i wrote a little perl script to demonstrate the issue:

root@test:~# cat testumlaut.pl
#!/usr/bin/perl
#use strict;
#use warnings;

use DBI;
$ENV{NLS_LANG} = "GERMAN_GERMANY.AL32UTF8";
my $dbh = DBI->connect( connectstring ) || die($DBI::errstr . "\n");
my $sql = 'select * from testchar';
my $sth = $dbh->prepare($sql);
$sth->execute();
while (my @row = $sth->fetchrow_array) {
        print "Umlaut: $row[0]\n";
}

The output looks like this

root@test :~# perl testumlaut.pl
Umlaut: ▒
Umlaut: ▒
Umlaut: ▒
Umlaut: ▒
Wide character in print at testumlaut.pl line 12.
Umlaut: €

At this point it looks like an error with the Perl DBI Modules. Anyone an idea how to solve this?

thanks for your help.

Answer 1

You are working with utf8 encoding, so the simplest way to fix it may be this one:

binmode(STDOUT, ":utf8");

Before the print sentence. More details in this question How can I output UTF-8 from Perl?