1 Reply Latest reply: Aug 28, 2012 7:01 PM by Sergiusz Wolicki-Oracle RSS

    problem with character set

    sankargvs
      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
        • 1. Re: problem with character set
          Sergiusz Wolicki-Oracle
          This is caused by the setting of NLS_LANG in the Registry. Most probably, it is set to CANADIAN FRENCH_CANADA.WE8MSWIN1252. You can either try to change the setting to AMERICAN_AMERICA.WE8MSWIN1252, which will reset all behavior to standard US, including date formatting and error message language, or you can try adding the parameter NLS_NUMERIC_CHARACTERS with the value ., (i.e. dot comma), which will only change the parsing of numbers. I know nothing about Biztalk server so I cannot tell what NLS configuration is the recommended one. You should ask Microsoft.


          -- Sergiusz