我试图将加密的unicode文本存储在Oracle RAW字段中。
在加密之前,为了提高效率,我将字符串转换为PLERTHOLDER_FOR_CODE_1的原始字节数组。
(NLS_NCHAR_CHARACTERSET是AL16UTF16)。
正在从UTL_I18N包引发LOSSY_CHARACTERSET_CONVERSION(ORA-12713)异常。
我故意设置NCharConversionException = true让它抛出异常,而不是有损转换“希腊”输出。
我究竟做错了什么?
这里是一个使用Dapper的例子,以方便使用。
没有对string_to_raw的调用,NVarchar2参数会正常工作。
///
/// using Dapper;
/// using Oracle.ManagedDataAccess.Client
///
///
[Test, Explicit]
public void Dapper_Sql_NLS_StringToRaw_ReturnValue_RefCursor_SmokeTest()
{
var parms = new OracleDynamicParameters();
var cn = ApiThreadContext.Current.Connection.DbConnection as OracleConnection;
OracleGlobalization og = cn.GetSessionInfo();
og.NCharConversionException = true; //throws LOSSY_CHARACTERSET_CONVERSION
cn.SetSessionInfo(og);
var expected = "Шон";
parms.Add(":in_plain", expected, OracleDbType.NVarchar2, ParameterDirection.Input);
parms.Add(":rc", dbType: OracleDbType.RefCursor, direction: ParameterDirection.ReturnValue);
//'Шон'
var result = cn.Query(
"select utl_i18n.string_to_raw(:in_plain, 'AL32UTF8') from dual",
//"select :in_plain from dual",
parms,
commandType: CommandType.Text
);
}
客户端 h2>
Windows 7 Professional x64 SP1
.Net 4.6完整版
Oracle.ManagedDataAccess.dll 4.12.1.2400(最新)。
OracleGlobalization设置: h3>
{Oracle.ManagedDataAccess.Client.OracleGlobalization}
Calendar: "GREGORIAN"
Comparison: "BINARY"
Currency: "£"
DateFormat: "DD-MON-YYYY HH24:MI:SS"
DateLanguage: "ENGLISH"
DualCurrency: "€"
ISOCurrency: "UNITED KINGDOM"
Language: "ENGLISH"
LengthSemantics: "BYTE"
NCharConversionException: true
NumericCharacters: ".,"
Sort: "BINARY"
Territory: "UNITED KINGDOM"
TimeStampFormat: "DD-MON-RR HH24.MI.SSXFF"
TimeStampTZFormat: "DD-MON-RR HH24.MI.SSXFF TZR"
TimeZone: "Europe/London"我没有在客户端上设置AL32UTF80环境变量,因为这应该由上述内容覆盖。 (在任何情况下,我没有检查它是否对托管提供商有任何影响)。
服务器 H2>
NLS_DATABASE_PARAMETERS H3>
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_LANGUAGE ENGLISH
NLS_TERRITORY UNITED KINGDOM
NLS_CURRENCY £
NLS_ISO_CURRENCY UNITED KINGDOM
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE ENGLISH
NLS_SORT BINARY
NLS_TIME_FORMAT HH24.MI.SSXFF
NLS_TIMESTAMP_FORMAT DD-MON-RR HH24.MI.SSXFF
NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH24.MI.SSXFF TZR
NLS_DUAL_CURRENCY €
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_RDBMS_VERSION 10.2.0.5.0
V $ VERSION H3>
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
客户端错误堆栈: h3>
Test 'UddFixture.Dapper_Sql_NLS_StringToRaw_SmokeTest' failed:
Oracle.ManagedDataAccess.Client.OracleException : ORA-12713: Character data loss in NCHAR/CHAR conversion
ORA-06512: at "SYS.UTL_I18N", line 321
ORA-06512: at line 1
at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, Boolean isDescribeOnly, Boolean isFromEF)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
Core\Connectivity\Dapper\SqlMapper.cs(1631,0): at Dapper.SqlMapper.d__61`1.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToListTSource
Core\Connectivity\Dapper\SqlMapper.cs(1506,0): at Dapper.SqlMapper.QueryT
UddFixture.cs(330,0): at UddFixture.Dapper_Sql_NLS_StringToRaw_SmokeTest()