1 Reply Latest reply: Jan 21, 2014 1:40 AM by odie_63 RSS

    XML parsing failed : Try to update FCRM field

    af432e97-4f6b-4143-88b1-6766ab68dce1

      i created one field in fusion crm ,lead . and i have one table in my oracle EBS database i write one procedure which get data from table and pur into fcrm

      when i call procedure with static value it perfactly alright but ,when try to use xml parsing for all records give following error

       

      ORA-20001: An error was encountered - -31011 -ERRORORA-31011: XML parsing failed

      ORA-19202: Error occurred in XML processing

      LPX-00245: extra data after end of document

      Error at line 1

      ORA-06512: at "SCOTT.XX_LEAD_UPDATE", line 101

      ORA-06512: at line 1

       

      my procedure as follow :

      CREATE OR REPLACE PROCEDURE xx_lead_update

      --(p_leadid IN VARCHAR2,p_xx_kp_lead IN VARCHAR2)

      IS

       

       

          p_leadid   VARCHAR2(50);

          p_xx_kp_lead    VARCHAR2(50);

         

          CURSOR c1 IS

          SELECT FCRM_LEADID,XX_KP_LEAD

          FROM XX_KP_EBSLEAD 

          WHERE ID = 6;

         

        -- Construct xml payload, which is used to invoke the service.

            l_envelope VARCHAR2(32767) := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/marketing/leadMgmt/leads/leadService/types/" xmlns:lead="http://xmlns.oracle.com/oracle/apps/marketing/leadMgmt/leads/leadService/" xmlns:lead1="http://xmlns.oracle.com/apps/marketing/leadMgmt/leads/leadService/" xmlns:not="http://xmlns.oracle.com/apps/crmCommon/notes/noteService" xmlns:not1="http://xmlns.oracle.com/apps/crmCommon/notes/flex/noteDff/">

                                                  <soapenv:Header/>

                                                      <soapenv:Body>

                                                          <typ:updateSalesLead>

                                                              <typ:salesLead>

                                                                      <lead:LeadId>'||p_leadid||'</lead:LeadId>

                                                                      <lead:xx_kp_lead_c>'||p_xx_kp_lead||'</lead:xx_kp_lead_c>

                                                              </typ:salesLead>

                                                          </typ:updateSalesLead>

                                                      </soapenv:Body>

                                          </soapenv:Envelope>';

                                        --PartyId: 300000000912340

       

       

        l_result         VARCHAR2(32767) := null;

        l_http_request   UTL_HTTP.req;

        l_http_response  UTL_HTTP.resp;

       

        v_len            number;

        v_txt            Varchar2(32767);

        soap_respond     CLOB;

        resp             xmltype;

       

        l_str            long;

        v_str            varchar2(100);

      BEGIN

              OPEN c1;

              LOOP

              FETCH c1 INTO p_leadid,p_xx_kp_lead;

              dbms_output.put_line ('p_xx_kp_lead '||p_xx_kp_lead);

              dbms_output.put_line ('p_leadid: '||p_leadid);

       

       

       

        -- Sets the Oracle wallet used for request, required for HTTPS

        UTL_HTTP.set_wallet('file:/apps/oracle/dnlclone/10.2.0/wallet','test1234');

       

       

        -- Creates new HTTP request

        l_http_request := UTL_HTTP.begin_request('https://aes.crm.ap2.oraclecloud.com:443/mklLeads/SalesLeadService?WSDL', 'POST','HTTP/1.1');

       

        -- Configure the authentication details on the request

        UTL_HTTP.SET_AUTHENTICATION(l_http_request, 'AJNAPHADE@DEEPAKNITRITE.COM', 'DNLcrm*1');

       

        -- Configure the request content type to be xml and set the content length

        UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml');

        UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_envelope));

       

        -- Set the SOAP action to be invoked; while the call works without this the value is expected to be set based on standards

        UTL_HTTP.set_header(l_http_request, 'SOAPAction','http://xmlns.oracle.com/apps/marketing/leadMgmt/leads/leadService/');

       

        -- Write the xml payload to the request.

        UTL_HTTP.write_text(l_http_request, l_envelope);

       

        --  Get the response

        l_http_response := UTL_HTTP.get_response(l_http_request);

       

        utl_http.get_header_by_name(l_http_response, 'Content-Length', v_len, 1); -- Obtain the length of the response

          FOR i in 1..CEIL(v_len/32767) -- obtain response in 32K blocks just in case it is greater than 32K

          LOOP

              utl_http.read_text(l_http_response, v_txt, case when i < CEIL(v_len/32767) then 32767 else mod(v_len,32767) end);

              soap_respond := soap_respond || v_txt; -- build up CLOB

          END LOOP;

          UTL_HTTP.END_RESPONSE(l_http_response);

         

          resp:= xmltype.createxml(soap_respond); -- Convert CLOB to XMLTYPE

         

          --Using extract or extractvalue methods, retrieve value from a particular element/tag in the xml

          --v_str := resp.extract('/env:Envelope/env:Body/ns0:updateOpportunityResponse/ns2:result/ns3:EBSOrderNumber_c/text()','xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns0="http://xmlns.oracle.com/apps/sales/opptyMgmt/opportunities/opportunityService/types/" xmlns:ns2="http://xmlns.oracle.com/apps/sales/opptyMgmt/opportunities/opportunityService/types/" xmlns:ns3="http://xmlns.oracle.com/apps/sales/opptyMgmt/opportunities/opportunityService/"').getstringval();

       

       

          l_str := resp.extract('/*').getstringval();

         

          LOOP

          EXIT

          WHEN l_str IS NULL;

                       dbms_output.put_line (SUBSTR (l_str, 1, instr (l_str, chr(10)) - 1));

                       l_str := SUBSTR (l_str, instr (l_str, chr(10)) + 1);

                     

          END LOOP;

         

          --dbms_output.put_line('EBS Order Number updated as: '||v_str);

              EXIT WHEN c1%NOTFOUND;

              END LOOP;

              CLOSE c1;

              COMMIT;

       

        EXCEPTION

        WHEN OTHERS THEN

        --dbms_output.put_line('Exception');

        RAISE_APPLICATION_ERROR(-20001,'An error was encountered - '

                                          ||SQLCODE||' -ERROR'||SQLERRM);

       

      END;