problem with character set
I have stored procedure in oracle DB 11gR1 with the following signature.
TestProc (
status_ OUT VARCHAR2,
error_description_ OUT VARCHAR2,
order_no_ IN OUT VARCHAR2,
revision_ IN OUT VARCHAR2,
email_id_ IN OUT VARCHAR2,
amount_ IN OUT NUMBER,
currency_ IN OUT VARCHAR2)
;
This is being called by Biztalk server 2009 by passing values using biztalk adapter for oracle.
but fails with the following error
######################################
Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: The value for field "AMOUNT_" is invalid. ---> System.ArgumentException: ORA-22062: chaîne d'entrée [12365.20] non valide
at Oracle.DataAccess.Types.OracleDecimal..ctor(String numStr, String format)
at Microsoft.Adapters.OracleCommon.OracleCommonMetadataUtils.CreateParameterValue(OracleDbType oracleType, Object xmlValue, OracleConnection dbConn, Boolean ignoreLOB, String fieldName)
--- End of inner exception stack trace ---
###################################################
From the error i found that the datatype of amount_ is not matching with the call parameters that biztalk is passing.The amount_ value that is passing is 99.99.
when i connect to sqlplus using command prompt on the biztalk server and query nls_session_parameters the output is as below.
PARAMETER VALUE
NLS_LANGUAGE CANADIAN FRENCH
NLS_TERRITORY CANADA
NLS_CURRENCY $
NLS_ISO_CURRENCY CANADA
NLS_NUMERIC_CHARACTERS ,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT RR-MM-DD
NLS_DATE_LANGUAGE CANADIAN FRENCH
NLS_SORT CANADIAN FRENCH
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT RR-MM-DD HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT RR-MM-DD HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS CHAR
NLS_NCHAR_CONV_EXCP FALSE
but when i connect from the database server and query the nls_session_parameters the output is
PARAMETER VALUE
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
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
the above output is also matching with nls_database_parameters. My guess is that the sqlclient on biztalk is changing the value to coma(,) (nls_numeric_characters at client) and passing and since coma(,) is treated as character the above error is thrown.
Please hlep me to resove the issue.
Thanks & Regards,
sankar
Edited by: sankargvs on Aug 28, 2012 4:40 AM