title: Character set problems with Perl, DBD::Oracle and Oracle 10g
body: <p>We have a problem with characters outside of the basic ASCII set appearing as upside down question marks in our Oracle 10g database.</p>
  
  <p>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:</p>
  
  <pre><code>#!/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-&gt;do($sql)  or Carp::croak "ERROR: PM_DB_0010:[" . $DBI::errstr . "]   Cannot run stmt:\n";;
  print $rows;
  $dbh-&gt;commit();
  $dbh-&gt;disconnect();
  
  
  
  sub espDbConnectNew {
      my ( $database ) = @_;    
      my %connectionStrings = &amp;esp_libs_db::espGetConnectionStrings( $database );
  
      # Set Environment Variables
      $ENV{ORACLE_SID}=$connectionStrings{"SID"};
      $ENV{ORACLE_HOME}=$connectionStrings{"HOME"};
      my $dbh = DBI-&gt;connect("dbi:Oracle:SID=$connectionStrings{'SID'};HOST=$connectionStrings{'HOST'};PORT=$connectionStrings{'PID'}",
      "$connectionStrings{'USER'}","$connectionStrings{'PWD'}",
      {PrintError=&gt;0,
      RaiseError =&gt; 0,
      AutoCommit =&gt; 0}
  ) or Carp::croak "ERROR: PM_DB_0003:  Cant connect to db:\n";
  
  
      return $dbh;
  } #espDbConnect
  </code></pre>
  
  <p>The database it loads into is an Oracle 10g database with the following parameters:</p>
  
  <pre><code>NLS_NCHAR_CHARACTERSET  AL16UTF16
  NLS_LANGUAGE    ENGLISH
  NLS_TERRITORY   UNITED KINGDOM
  NLS_CHARACTERSET    WE8ISO8859P1
  </code></pre>
  
  <p>The single column on the test table is of type VARCHAR2(255).</p>
  
  <p>Despite a full working day reading about these problems I don't really know what to do to solve/diagnose the exact problem.</p>
  
  <p>I've tried this both with and without using Encode to encode the SQL string before executing it.</p>
  
  <p>Thanks</p>