DBD::Oracle 和 utf8
我在 Solaris 上使用最新的 DBD::Oracle on perl v5.8.4 将 UTF8 字符串插入到 Oracle 10 数据库时遇到一些问题。
这是我的数据库设置
> --------SELECT * from NLS_DATABASE_PARAMETERS-------------------------------
> NLS_NCHAR_CHARACTERSET AL16UTF16
> NLS_LANGUAGE AMERICAN
> NLS_TERRITORY AMERICA NLS_CURRENCY $
> NLS_ISO_CURRENCY AMERICA
> NLS_NUMERIC_CHARACTERS .,
> NLS_CHARACTERSET UTF8
> NLS_CALENDAR GREGORIAN
> NLS_DATE_FORMAT DD-MON-RR
> NLS_DATE_LANGUAGE AMERICAN
> NLS_SORT BINARY
> NLS_TIME_FORMAT HH.MI.SSXFF AM
> NLS_TIMESTAMP_FORMAT DD-MON-RR
> HH.MI.SSXFF AM
> NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
> NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR
> HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $
> NLS_COMP BINARY
> NLS_LENGTH_SEMANTICS CHAR
> NLS_NCHAR_CONV_EXCP FALSE
> NLS_RDBMS_VERSION 10.2.0.4.0
> --------------------------------------------------------------------------
这是我的 perl $dbh->ora_nls_parameters()
$VAR1 = {
'NLS_LANGUAGE' => 'AMERICAN',
'NLS_TIME_TZ_FORMAT' => 'HH.MI.SSXFF AM TZR',
'NLS_SORT' => 'BINARY',
'NLS_NUMERIC_CHARACTERS' => '.,',
'NLS_TIME_FORMAT' => 'HH.MI.SSXFF AM',
'NLS_ISO_CURRENCY' => 'AMERICA',
'NLS_COMP' => 'BINARY',
'NLS_CALENDAR' => 'GREGORIAN',
'NLS_DATE_FORMAT' => 'DD-MON-RR',
'NLS_DATE_LANGUAGE' => 'AMERICAN',
'NLS_TIMESTAMP_FORMAT' => 'DD-MON-RR HH.MI.SSXFF AM',
'NLS_TERRITORY' => 'AMERICA',
'NLS_LENGTH_SEMANTICS' => 'CHAR',
'NLS_NCHAR_CHARACTERSET' => 'AL16UTF16',
'NLS_DUAL_CURRENCY' => '$',
'NLS_TIMESTAMP_TZ_FORMAT' => 'DD-MON-RR HH.MI.SSXFF AM TZR',
'NLS_NCHAR_CONV_EXCP' => 'FALSE',
'NLS_CHARACTERSET' => 'UTF8',
'NLS_CURRENCY' => '$'
};
在我的脚本中，我有：
use utf-8;
$ENV{NLS_LANG}='AMERICAN_AMERICA.UTF8';
..
$sth->bind_param(5, $myclobfield, {ora_type => ORA_CLOB, ora_csform => SQLCS_NCHAR});
..
字符串打印出 1
print Encode::is_utf8($myclobfield);
但像 òàè 这样的字符没有正确插入到数据库中。 （我使用兼容 utf8 的客户端进行了测试，可以正确插入和读取它们）
任何人都可以建议最好的方法吗？ 谢谢
I have some troubles inserting an UTF8 string into an oracle 10 database on Solaris, using the latest DBD::Oracle on perl v5.8.4.
This are my DB settings
> --------SELECT * from NLS_DATABASE_PARAMETERS-------------------------------
> NLS_NCHAR_CHARACTERSET AL16UTF16
> NLS_LANGUAGE AMERICAN
> NLS_TERRITORY AMERICA NLS_CURRENCY $
> NLS_ISO_CURRENCY AMERICA
> NLS_NUMERIC_CHARACTERS .,
> NLS_CHARACTERSET UTF8
> NLS_CALENDAR GREGORIAN
> NLS_DATE_FORMAT DD-MON-RR
> NLS_DATE_LANGUAGE AMERICAN
> NLS_SORT BINARY
> NLS_TIME_FORMAT HH.MI.SSXFF AM
> NLS_TIMESTAMP_FORMAT DD-MON-RR
> HH.MI.SSXFF AM
> NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
> NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR
> HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $
> NLS_COMP BINARY
> NLS_LENGTH_SEMANTICS CHAR
> NLS_NCHAR_CONV_EXCP FALSE
> NLS_RDBMS_VERSION 10.2.0.4.0
> --------------------------------------------------------------------------
This are my perl $dbh->ora_nls_parameters()
$VAR1 = {
'NLS_LANGUAGE' => 'AMERICAN',
'NLS_TIME_TZ_FORMAT' => 'HH.MI.SSXFF AM TZR',
'NLS_SORT' => 'BINARY',
'NLS_NUMERIC_CHARACTERS' => '.,',
'NLS_TIME_FORMAT' => 'HH.MI.SSXFF AM',
'NLS_ISO_CURRENCY' => 'AMERICA',
'NLS_COMP' => 'BINARY',
'NLS_CALENDAR' => 'GREGORIAN',
'NLS_DATE_FORMAT' => 'DD-MON-RR',
'NLS_DATE_LANGUAGE' => 'AMERICAN',
'NLS_TIMESTAMP_FORMAT' => 'DD-MON-RR HH.MI.SSXFF AM',
'NLS_TERRITORY' => 'AMERICA',
'NLS_LENGTH_SEMANTICS' => 'CHAR',
'NLS_NCHAR_CHARACTERSET' => 'AL16UTF16',
'NLS_DUAL_CURRENCY' => '
In my script I have:
use utf-8;
$ENV{NLS_LANG}='AMERICAN_AMERICA.UTF8';
..
$sth->bind_param(5, $myclobfield, {ora_type => ORA_CLOB, ora_csform => SQLCS_NCHAR});
..
The string prints out 1 on
print Encode::is_utf8($myclobfield);
But characters like òàè are not correctly inserted into the DB.
(I tested with a utf8 compliant client that can correctly insert and read them)
Can anyone suggest the best way to do it?
Thanks
,
'NLS_TIMESTAMP_TZ_FORMAT' => 'DD-MON-RR HH.MI.SSXFF AM TZR',
'NLS_NCHAR_CONV_EXCP' => 'FALSE',
'NLS_CHARACTERSET' => 'UTF8',
'NLS_CURRENCY' => '
In my script I have:
The string prints out 1 on
But characters like òàè are not correctly inserted into the DB.
(I tested with a utf8 compliant client that can correctly insert and read them)
Can anyone suggest the best way to do it?
Thanks
};
In my script I have:
The string prints out 1 on
But characters like òàè are not correctly inserted into the DB.
(I tested with a utf8 compliant client that can correctly insert and read them)
Can anyone suggest the best way to do it?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
好吧，经过几个小时的锤炼，并篡改了所有我能做的 DBA，我解决了它：
我错过了这个：
也要小心
如果你不确定它是 UTF8
干杯，
G。
Ok after couple of hours of hammering, and tampering all the DBAs I could, I Solved it:
I was missing this:
also be careful to
if you're not sure it's UTF8
Cheers
G.
Unicode 文字需要以“n”为前缀，如下所示：
select n'Language - Språk - Język' from Dual；
另外，请检查以下内容：将国家字符插入 Oracle NCHAR 或 NVARCHAR 列不起作用
Unicode literals needs to be prefixed with an 'n', like this:
select n'Language - Språk - Język' from dual;
Also, check this: Inserting national characters into an oracle NCHAR or NVARCHAR column does not work