3

We have a problem with characters outside of the basic ASCII set appearing as upside down question marks in our Oracle 10g database.

I have the following script to load some test data. The script is saved as Latin-1/ISO-8859-1 on a remote UNIX server from Komodo IDE:

#!/wload/espd/app/perl/bin/perl

use strict; 
use warnings;
use Encode;
use esp_libs_db;
my $dbh = espDbConnectNew();

my $sql = q{ INSERT INTO DBUSER.test VALUES ('qwérty')};

#$sql = encode("iso-8859-1", $sql);

my $rows = $dbh->do($sql)  or Carp::croak "ERROR: PM_DB_0010:[" . $DBI::errstr . "]   Cannot run stmt:\n";;
print $rows;
$dbh->commit();
$dbh->disconnect();



sub espDbConnectNew {
    my ( $database ) = @_;    
    my %connectionStrings = &esp_libs_db::espGetConnectionStrings( $database );

    # Set Environment Variables
    $ENV{ORACLE_SID}=$connectionStrings{"SID"};
    $ENV{ORACLE_HOME}=$connectionStrings{"HOME"};
    my $dbh = DBI->connect("dbi:Oracle:SID=$connectionStrings{'SID'};HOST=$connectionStrings{'HOST'};PORT=$connectionStrings{'PID'}",
    "$connectionStrings{'USER'}","$connectionStrings{'PWD'}",
    {PrintError=>0,
    RaiseError => 0,
    AutoCommit => 0}
) or Carp::croak "ERROR: PM_DB_0003:  Cant connect to db:\n";


    return $dbh;
} #espDbConnect

The database it loads into is an Oracle 10g database with the following parameters:

NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_LANGUAGE    ENGLISH
NLS_TERRITORY   UNITED KINGDOM
NLS_CHARACTERSET    WE8ISO8859P1

The single column on the test table is of type VARCHAR2(255).

Despite a full working day reading about these problems I don't really know what to do to solve/diagnose the exact problem.

I've tried this both with and without using Encode to encode the SQL string before executing it.

Thanks

Nick
  • 2,418
  • 16
  • 20

1 Answers1

1

How are you retrieving the data when you get the upside down question marks? What is the NLS_LANG environment variable on the client where you are retrieving the data?

In SQL*Plus, can you run

SELECT dump( column_name, 1013 ), column_name
  FROM DBUSER.test

and post the results? The DUMP function shows what is actually stored in the database-- that will show whether the problem is in storing the accented character or whether the problem is in retrieving the accented character.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I'm in PLSQL and I'm not sure how to tell what NLS_LANG it's using. AMERICAN_AMERICA.WE8MSWIN1252 is what's in the registry but I don't know whether anything can override that. If I run the dump then I get `Typ=1 Len=6 CharacterSet=WE8ISO8859P1: 113,119,191,114,116,121 qw¿rty`. I see the same problem if I select the data on the UNIX server. This is just a little test script to remove factors like content-type charsets. – Nick Apr 06 '11 at 14:43
  • To clarify I'm not setting NLS_LANG explicitly on the UNIX client when using DBI. – Nick Apr 06 '11 at 14:57
  • I'm not sure what "in PL/SQL" means. Do you mean SQL*Plus? Based on the output of DUMP, it appears that the problem is in storing the character (191 in ISO 8859-1 is the upside down question mark character). Can you set the NLS_LANG on the UNIX client to AMERICAN_AMERICA.WE8ISO8859P1 before running the script? – Justin Cave Apr 06 '11 at 15:05
  • Sorry - missed a fairly crucial word there... I meant 'PLSQL Developer' by allround automations. I've tried adding the line `$ENV{NLS_LANG} = 'AMERICAN_AMERICA.WE8ISO8859P1';` after the ORACLE_HOME is set. I then get an error when trying to connect to the DB. Unfortunately $DBI::errstr just says it had a problem retrieving the text for the error. Am I setting NLS_LANG wrong? – Nick Apr 06 '11 at 15:24
  • I've tested the same thing out on our new system which has much newer versions of DBI, DBD and the oracle client and setting NLS_LANG works and allows me to store the character correctly. Now I just need to look at upgrading our current system. Thank you. – Nick Apr 07 '11 at 12:56