8 Replies Latest reply: Apr 25, 2013 7:07 AM by Hari_639 RSS

    German Characters issue while invoking Web Services via UTL_HTTP

    Hari_639
      Dear Forum Members,

      I'm trying to invoke SAP CRM Web services from Oracle PL/SQL. I have used following code which is working fine.
      -- call web service using Oracle UTIL_HTTP packages
      DECLARE
        http_req utl_http.req;
           http_resp utl_http.resp;
           lv_request VARCHAR2(32767);
           lc_response CLOB;
           lv_buffer VARCHAR2(32000);
           lv_name          VARCHAR2(256);
           lv_hdr_value     VARCHAR2(1024);     
           l_xml XMLType;          
      BEGIN     
           utl_http.set_persistent_conn_support(true);
           utl_http.set_transfer_timeout(600);
           
           http_req:= utl_http.begin_request
                                         ( url => 'http://xyz3ni92.server.xyz.com:8045/sap/bc/srt/xip/sap/crm_bupa_custid_qr/011/customersbycrmid/http_binding'
                                         , method => 'POST'                              
                                         );
           lv_request := '<?xml version="1.0" encoding="UTF-8"?>'
           ||'<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:glob="http://sap.com/xi/CRM/Global2">'
         ||'<soap:Header/>'
         ||'<soap:Body>'
            ||'<glob:CustomerCRMByIDQuery>'
               ||'<MessageHeader>'
                  ||'<ID schemeID="?" schemeAgencyID="?" schemeAgencySchemeAgencyID="?"></ID>'
                  ||'<UUID></UUID>'
                  ||'<ReferenceID schemeID="?" schemeAgencyID="?" schemeAgencySchemeAgencyID="?"></ReferenceID>'
                  ||'<ReferenceUUID></ReferenceUUID>'
               ||'</MessageHeader>'
               ||'<BusinessPartnerSelectionByBusinessPartner>'
                  ||'<UUID schemeID="?" schemeAgencyID="?"></UUID>'
                  ||'<InternalID>2200117598</InternalID>'
               ||'</BusinessPartnerSelectionByBusinessPartner>'
            ||'</glob:CustomerCRMByIDQuery>'
                 ||'</soap:Body>'
           ||'</soap:Envelope>';     
           /*set username and password*/
           utl_http.set_authentication (
                     r => http_req,
                     username => 'WS_USER',
                     password => 'WS_PASSWORD',
                     scheme => 'Basic',
                     for_proxy => false);     
                     
           utl_http.set_header(http_req, 'Content-Type', 'application/soap+xml;charset=UTF-8'); 
           utl_http.set_header(http_req, 'Content-Length', LENGTHB(lv_request));
           utl_http.write_text(http_req, lv_request);
           /*Make HTTP call*/
           http_resp:= utl_http.get_response(http_req);
           
           /*read response text from response*/
           BEGIN
                     LOOP
                               utl_http.read_text(http_resp, lv_buffer);
                               lc_response := lc_response || TO_CLOB(lv_buffer);
                     END LOOP;
           EXCEPTION
                     WHEN OTHERS THEN
                          -- ora-29266 end-of-body reached
                          IF SQLCODE <> -29266 THEN
                                    RAISE;
                          END IF;
           END;
           utl_http.end_response(http_resp);     
           l_xml := XMLType(lc_response);
           /*Log response for testing*/
           DELETE FROM webservice_log;
           INSERT INTO webservice_log (seq_id,xml_response) VALUES (sqe_Webservice_Log.NEXTVAL,l_xml);
      EXCEPTION WHEN OTHERS THEN
                RAISE;
      END;
      However, if there are any German Characters in SAP, then they are being replaced by JUNK data when they come to Oracle.

      If I invoke the same web-service suing tools like SOAP-UI, then German characters are coming fine. I have also traced web-service requests/responses from SAP side, and there response is showing fine. When it comes to Oracle, they are getting corrupted.

      I'm sure it's something to do with character-set, but I'm not able to find-out where and what I should fix/change.

      Thanks for your help in advance.

      DB: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      NLS_DATABASE_PARAMETERS
      PARAMETER                      VALUE                                  
      ------------------------------ ----------------------------------------
      NLS_LANGUAGE                   AMERICAN                                 
      NLS_TERRITORY                  AMERICA                                  
      NLS_CURRENCY                   $                                        
      NLS_ISO_CURRENCY               AMERICA                                  
      NLS_NUMERIC_CHARACTERS         .,                                       
      NLS_CHARACTERSET               AL32UTF8                                 
      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           BYTE                                     
      NLS_NCHAR_CONV_EXCP            FALSE                                    
      NLS_NCHAR_CHARACTERSET         AL16UTF16                                
      NLS_RDBMS_VERSION              11.2.0.1.0   
      Regards,
      Hari

      added further details by: Hari_639 on Apr 24, 2013 6:45 PM
        • 1. Re: German Characters issue while invoking Web Services via UTL_HTTP
          sybrand_b
          but I'm not able to find-out where and what I should fix/change.
          As the only information you provide boils down to 'My car is broke, please fix my car', we are unable to help to, as any relevant information (four digit version, OS, code page of OS, characterset of database, value of NLS_LANG) is missing.

          ----------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: German Characters issue while invoking Web Services via UTL_HTTP
            Hari_639
            Hello,

            I have updated question with NLS_DATABASE_PARAMETERS already :)

            DB is running on Linux Operating System. I will check with our DBA to get it's character set/code page of OS.

            And as you see above DB characterset is AL32UTF8

            I'm running above pl/sql block from SQL Developer, it has encoding as cp1252 (Tools -> Preferences -> Environment)

            Please let me know if you need any further details. Thank you.

            Regards,
            Hari
            • 3. Re: German Characters issue while invoking Web Services via UTL_HTTP
              ranit B
              I will check with our DBA to get it's character set/code page of OS.
              Try this -
              ranit@XE11GR2>> @[%NLS_LANG%]
              SP2-0310: unable to open file "[AMERICAN_AMERICA.WE8MSWIN1252]"
              The value got is the system/OS registry value for 'NLS_LANG'
              • 4. Re: German Characters issue while invoking Web Services via UTL_HTTP
                chris227
                Hari_639 wrote:
                I'm running above pl/sql block from SQL Developer, it has encoding as cp1252 (Tools -> Preferences -> Environment)
                Perhaps it is just a displaying issue. As long as you dont examine the raw bytes you wont know.

                So i would look in SQL Developer for UTF-8 instead of CP1252, because this might not be the correct setting for UTF-8 resp. AL32UTF8 , might be more proper for WE8MSWIN1252.

                To get to the byte to something like
                select
                dump(xml_response, 1010)
                from webservice_log 
                on a suitable substring of xml_response, that contains some critical data.

                Edited by: chris227 on 24.04.2013 07:44
                • 5. Re: German Characters issue while invoking Web Services via UTL_HTTP
                  Hari_639
                  Hello Both,

                  Thank you.

                  I ran following command from SQL Plus window after connecting to DB..
                  SQL> @[%NLS_LANG%]
                  SP2-0310: unable to open file "[AMERICAN_AMERICA.WE8MSWIN1252]"
                  And also I have checked actual data using DUMP function, it looks like data stored is wrong.

                  I have updated one field in SAP such that it only contains German character ä. When I query corresponding data from Oracle I got following output..
                  SELECT xmlresponse.Notes,
                    dump(xmlresponse.Notes,1010) dump_text
                  FROM webservice_log,
                    Xmltable(Xmlnamespaces
                                                ('http://www.w3.org/2003/05/soap-envelope' AS "env",
                                                 'http://sap.com/xi/CRM/Global2' AS "nm",
                                                 'urn:sap.com:proxy:DCT:/1SAI/TAS57DF0B317943DEAE3C49:702' AS "prx" 
                                                 ),
                                                 '/env:Envelope/env:Body/nm:CustomerCRMByIDResponse/BusinessPartner'
                                                 PASSING xml_response
                                                 columns
                                                 NOTES VARCHAR2(4000) PATH 'TextCollection/Text/TextContent/Text' 
                                           ) XMLRESPONSE;
                  
                  /* Output */
                  NOTES -- DUMP_TEXT
                  
                  ä     Typ=1 Len=4 CharacterSet=AL32UTF8: 195,131,194,164
                  But decimal notation for German character ä is different!
                   SELECT DUMP('ä',1010) dump_text from dual;
                  /*Output*/
                  DUMP_TEXT
                  Typ=96 Len=2 CharacterSet=AL32UTF8: 195,164
                  Regards,
                  Hari
                  • 6. Re: German Characters issue while invoking Web Services via UTL_HTTP
                    Hari_639
                    Hello All,

                    I have converted response to WE8ISO8859P1 character-set using CONVERT function and now data is coming fine.
                    ...convert(xmlresponse.Notes,'WE8ISO8859P1','AL32UTF8')...
                    /*for ä */
                    SELECT CONVERT('ä','WE8ISO8859P1','AL32UTF8') con_text from dual;
                    I have tested with all other German Chatacters and everything is coming fine if I convert response to WE8ISO8859P1 character-set.

                    However I'm not able to understand WHY I should convert it to other character set while our DB character-set AL32UTF8 supports German characters. Can some-one explain me this? Thank you very much!

                    Regards,
                    Hari

                    Edited by: Hari_639 on Apr 25, 2013 11:34 AM
                    • 7. Re: German Characters issue while invoking Web Services via UTL_HTTP
                      chris227
                      I am not really experienced with utl_http, but may be you will read on SET_BODY_CHARSET in the docs.
                      ISO-8859-1 is the defaut characterset, until you specify the characterset in the content_type attribut along with media type text.
                      But your media type is not text, so perhaps setting the body charset to UTF-8 might help.

                      regards
                      • 8. Re: German Characters issue while invoking Web Services via UTL_HTTP
                        Hari_639
                        Hello Chris,

                        Thank you very much!

                        Yes, setting the character-set for http request to UTF-8 has solved the issue. That makes this discussion to a logical conclusion.

                        Have a great day :D

                        Regards,
                        Hari