I'm trying to store encrypted unicode text in an Oracle RAW field. Before encryption I'm converting the string to a raw byte array as AL32UTF8, for the sake of efficiency. (NLS_NCHAR_CHARACTERSET is AL16UTF16).
A LOSSY_CHARACTERSET_CONVERSION (ORA-12713) exception is being thrown from the UTL_I18N package.
I am deliberately setting NCharConversionException = true to have it throw the exception rather than having the lossy conversion 'greek' the output.
What am I doing wrong?
Here is a sample using Dapper for convenience. The NVarchar2 parameter bind without the call to string_to_raw works correctly.
/// <summary>
/// using Dapper;
/// using Oracle.ManagedDataAccess.Client
///
/// </summary>
[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<dynamic>(
"select utl_i18n.string_to_raw(:in_plain, 'AL32UTF8') from dual",
//"select :in_plain from dual",
parms,
commandType: CommandType.Text
);
}
Client side
- Windows 7 Professional x64 SP1
- .Net 4.6 Full
- Oracle.ManagedDataAccess.dll 4.12.1.2400 (the latest).
OracleGlobalization Settings:
{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"
I haven't set an NLS_LANG environment variable on the client because that should be covered by the above. (I haven't checked if it has any effect on the managed provider in any case).
Server
NLS_DATABASE_PARAMETERS
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
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
Client-side error stack:
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.<QueryImpl>d__61`1.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
Core\Connectivity\Dapper\SqlMapper.cs(1506,0): at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType)
UddFixture.cs(330,0): at UddFixture.Dapper_Sql_NLS_StringToRaw_SmokeTest()