Perl、DBD::Oracle 和 Oracle 10g 的字符集问题

发布于 2024-10-31 02:07:08 字数 1521 浏览 2 评论 0原文

我们遇到了一个问题，基本 ASCII 集之外的字符在 Oracle 10g 数据库中显示为颠倒的问号。

我有以下脚本来加载一些测试数据。该脚本在 Komodo IDE 的远程 UNIX 服务器上保存为 Latin-1/ISO-8859-1：

#!/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

它加载到的数据库是 Oracle 10g 数据库，具有以下参数：

NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_LANGUAGE    ENGLISH
NLS_TERRITORY   UNITED KINGDOM
NLS_CHARACTERSET    WE8ISO8859P1

测试表上的单个列的类型为 VARCHAR2( 255）。

尽管花了整整一天的时间阅读这些问题，但我真的不知道该怎么做才能解决/诊断确切的问题。

我在执行 SQL 字符串之前尝试过使用和不使用 Encode 对 SQL 字符串进行编码。

谢谢

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

耀眼的星火 2024-11-07 02:07:08

当你得到倒置的问号时，你如何检索数据？您检索数据的客户端上的 NLS_LANG 环境变量是什么？

在 SQL*Plus 中，您可以运行

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

并发布结果吗？ DUMP 函数显示数据库中实际存储的内容，这将显示问题是在存储重音字符还是在检索重音字符时出现问题。

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.

回复收藏 0 原文

~没有更多了~