SQL & PL/SQL

SOAP is xml, so why not use Oracle's XML capabilities?

Hi Peter,

you can extract your xmlfile using regexp_substr like this:

with soap(response) as (
select 
'--MIMEBoundary_004939d69735beed70e3e5c4563e98dbfce2aad561364e11
Content-Type: application/xop+xml; charset=UTF-8; type="text/xml"
Content-Transfer-Encoding: binary
Content-ID: <0.104939d69735beed70e3e5c4563e98dbfce2aad561364e11@apache.org>
<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header>
<ITCAMTOOLKITSyncSoapHeader xmlns="http://www.ibm.com/xmlns/prod/tivoli/itcam">1</ITCAMTOOLKITSyncSoapHeader>
</soapenv:Header>
<soapenv:Body>
</soapenv:Body>
</soapenv:Envelope>
--MIMEBoundary_004939d69735beed70e3e5c4563e98dbfce2aad561364e11
Content-Type: application/octet-stream
Content-Transfer-Encoding: binary
Content-ID: <e34939d69735beed70e3e5c4563e98dbfce2aad561364e11@apache.org>
' from dual)
select xmltype(regexp_substr(response,'<\?xml.+</soapenv:Envelope>',1,1,'n')) 
from soap

Output:

<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header>
<ITCAMTOOLKITSyncSoapHeader xmlns="http://www.ibm.com/xmlns/prod/tivoli/itcam">1</ITCAMTOOLKITSyncSoapHeader>
</soapenv:Header>
<soapenv:Body>
</soapenv:Body>
</soapenv:Envelope>

Stefan Jager wrote:
SOAP is xml, so why not use Oracle's XML capabilities?

SOAP is XML, but "multipart MIME" is not.

You have to:

1. split the SOAP Result by the MIME Boundary (eg DBMS_LOB.substr()/instr())
2. parse out the data
3. convert it to XMLType()
4. Then, you can use XMLTable()

Split via DBMS_LOB should look something like this untested code

start_pos := dbms_lob.instr( result_CLOB, mime_boundary,1,1) + length( mime_boundary );
stop_pos  := dbms_lob.instr( result_CLOB, mime_boundary,1,2);
amount    := stop_pos - start_pos + 1
xml_plus_header_varchar2 := dbms_lob.substr( result_clob, amount, start_pos );

My $0.02

MK

True, but from what I remember from SOAP is that it always has to be fully valid xml, wrapped in a SOAP envelope. But then I haven't used it in ages, so that may have changed, I wouldn't know.

I've always found it cumbersome to work with...

Not everybody follows the rules the same way you would.

Will try how to handle it with PLSQL but for now, have done java stored procedure like this...

have two methods:

To return the number of attachment from SOAP response:

p.s: MultiPart and BodyPart Class is a part of javax.mail package...

public static int retNumAtt(BLOB vsebina) throws Exception
    {
        int stevilo = 0;
        InputStream stream = null;
        
        try
        {
            stream = vsebina.getBinaryStream();
            byte[] buffer = new byte[(int)vsebina.length()];
            stream.read(buffer);
            MimeMultipart mp = new MimeMultipart(new ByteArrayDataSource(buffer, "text/xml"));
            stevilo = mp.getCount();
        }
        finally
        {
            if (stream != null)
            {
                stream.close();
            }
        }
         
        return stevilo;
    }

To get a specific attachment from SOAP response into BLOB.

public static BLOB vrniPrilogo(BLOB vsebina, int index) throws Exception {
        int stevilo = 0;
        if (vsebina == null) {
            return null;
        }
        InputStream stream = null;
        InputStream att = null;
        java.io.OutputStream outStr = null;
        BLOB retBlob = null;
        try {
            stream = vsebina.getBinaryStream();
            byte[] buffer = new byte[(int)vsebina.length()];
            stream.read(buffer);
            MimeMultipart mp = new MimeMultipart(new ByteArrayDataSource(buffer, "text/xml"));
            stevilo = mp.getCount();
            BodyPart bp = mp.getBodyPart(index);
            att = bp.getInputStream();
            byte[] buffer_att = new byte[att.available()];
            att.read(buffer_att);
            OracleDriver ora = new OracleDriver();
            Connection conn = ora.defaultConnection();
            retBlob = BLOB.createTemporary(conn, true, oracle.sql.BLOB.DURATION_SESSION);
            outStr = retBlob.setBinaryStream(0);
            outStr.write(buffer_att);
            outStr.flush();
            outStr.close();
        } finally {
            if (stream != null) {
                stream.close();
            }
            if (att != null) {
                att.close();
            }
            if (outStr != null) {
                outStr.close();
            }
        }
        return retBlob;
    }

plsql / java wrapper...

CREATE OR REPLACE FUNCTION f_stevilo_att(p_soap_envelope in BLOB) RETURN number
AS LANGUAGE JAVA NAME 'ZpizService.retNumAtt(oracle.sql.BLOB) return java.lang.Long';
/
CREATE OR REPLACE FUNCTION f_vrniPrilogo(p_soap_envelope in BLOB, p_index in number) RETURN blob
AS LANGUAGE JAVA NAME 'ZpizService.vrniPrilogo(oracle.sql.BLOB, int) return oracle.sql.BLOB';
/

demo program..

declare
    l_blob blob;
    l_ret number;
    l_priloga blob;
begin
    
    --here I have a soap envelope stored (just for demo)
    select vsebina into l_blob from ....
    
    -- get number off attachment from soap response
    l_ret := f_stevilo_att(l_blob);
   
    
    --return attachment from soap envelope for the 1 element
    l_priloga := f_vrniPrilogo(l_blob,1);
    
    --print length on dbms_output
    put_xl_line('BLOB length blob-a: ' || dbms_lob.getlength(l_priloga));
end;