plsql - How to extract an attachment which is a MTOM XOP from SOAP response using PL/SQL

Question

I am trying to extract an attachment which is a MTOM XOP from a SOAP response using PL/SQL(UTL_HTTP plugin). I can see the raw response but it only shows cid and content but I am not able to retreive the attachment. When I use SOAP UI I am able to get the attachment. Here is the code

create or replace procedure soap_proc_extract_attach as   
  v_soap_request_text  clob;
  v_request            UTL_HTTP.REQ;
  v_response          UTL_HTTP.RESP;
  v_buffer            varchar2(32767);
  v_response_xml      clob;
  l_soap_env          xmltype;
  l_soap_body          xmltype;
  p_napaka    varchar2(1000);
begin

  v_soap_request_text := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
                                                           xmlns:v2="$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$" 
                                                           xmlns:v1="$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$/v1" 
                                                           xmlns:v11="$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$/v1" 
                                                           xmlns:v12="$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$/v1">
        <soapenv:Header>
                <v2:SecurityInfo>
                        <v1:Token>udsiddydi543i5yiuy543iu5y43iu</v1:Token>
                </v2:SecurityInfo>
        </soapenv:Header>
        <soapenv:Body>
                <v2:generateDocumentumMessage_IN>
                       
                    <v12:TextContent><![CDATA[<?xml version="1.0" encoding="utf-8"?><client><lastname>XX</lastname><firstname>uyuuu</firstname><time>13:00 PM</time><date>2012-03-16</date><project>ghghgh</project></client>]]></v12:TextContent>
                                       
                </v2:generateDocumentumMessage_IN>
        </soapenv:Body>
</soapenv:Envelope>';
  v_request := utl_http.begin_request(url => 'http://example.com', method => 'POST');
  utl_http.set_header(V_REQUEST, 'User-Agent', 'Apache-HttpClient/4.5.5 (Java/12.0.1)');

  utl_http.set_header (R => v_request, name => 'Content-Length', value => DBMS_LOB.GETLENGTH(v_soap_request_text));
  utl_http.set_header (R => v_request, name => 'SOAPAction', value => '""');
  utl_http.set_header (R => v_request, name => 'Content-Type', value => 'text/xml;charset=UTF-8');

  utl_http.write_text (R => v_request, data => v_soap_request_text);
  utl_http.set_body_charset(v_request, 'UTF8');
  dbms_output.put_line('making requiest');

  v_response := utl_http.get_response(v_request);

  dbms_output.put_line('getting response');

  if (v_response.status_code <> 200) then
  p_napaka := ' response status code: ' ||  v_response.status_code;
  return;
  end if;

  DBMS_LOB.createtemporary (lob_loc => v_response_xml, cache => TRUE);
  begin
    loop
                  dbms_output.put_line('loop starts');
                      insert into mytable1 (column3) values (v_response_xml);
  UTL_HTTP.read_raw(v_response, v_buffer, 32767);


     -- utl_http.read_text(v_response, v_buffer, 32767);
              dbms_output.put_line('appended');

      DBMS_LOB.writeappend (v_response_xml, LENGTH (v_buffer),v_buffer);
    end loop;
                  dbms_output.put_line('loop ended');

    insert into mytable1 (column3) values (v_response_xml);
dbms_output.put_line(substr(v_response_xml,5000,200));
dbms_output.put_line(length(v_response_xml));

    commit;
        dbms_output.put_line('inserted');

  exception when utl_http.end_of_body then
                dbms_output.put_line('exception 1');
    insert into mytable1 (column3) values (v_response_xml);
    insert into mytable1 (letter) values (clob_to_blob2(v_response_xml));
        insert into mytable1 (letter) values (F_DECODE_BASE64(v_response_xml));
    utl_http.end_response(v_response);
  end;


exception
  when others then
              dbms_output.put_line('exception');

    dbms_output.put_line( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    utl_http.end_response(v_response);
    p_napaka := sqlerrm;
end;

---

output

HTTP/1.1 200 OK Content-Type: multipart/related; type="application/xop+xml"; boundary="uuid:t6t6t6tgg-7677-49b1-81d9-ac1c1b5d2d19"; start="root.message@cxf.apache.org"; start-info="text/xml" Transfer-Encoding: chunked Server: Jetty(7.5.3.v20111011)

--uuid:d45689e1-6a07-49b1-81d9-ac1c1b5d2d19 Content-Type: application/xop+xml; charset=UTF-8; type="text/xml"; Content-Transfer-Encoding: binary Content-ID: root.message@cxf.apache.org

<soap:Envelope >ghggh<xop:Include xmlns:xop="http://www.w3.org/2004/08/xop/include" href="cid:ygff5f-hhg66-4238-a859-87ygf-36@cxf.apache.org"/></ns5:generateDocMessage_OUT></soap:Body></soap:Envelope> --uuid:d45689e1-6a07-49b1-8788-hghhg7767 Content-Type: application/octet-stream Content-Transfer-Encoding: binary Content-ID: e7c6e7df-b9d5-4238-a859-5e2f200458a1-36@cxf.apache.org

question from:https://stackoverflow.com/questions/65876048/how-to-extract-an-attachment-which-is-a-mtom-xop-from-soap-response-using-pl-sql

Answer 1

Waitting for answers