WEBSERVICE SOAP CALL using PL/SQL and UTL_HTTP – for the new oracle dba or developer





you are a new oracle developer or dba and you would like to know about communicating to a webservice using the utl_http package and pl/sql.This is useful for you to troubleshoot any application functionality using pl/sql and utl_http.

 

Re: Using UTL_HTTP, are you able to send attachments in a web service?
Posted: 10-Feb-2010 21:20   in response to: user12106862
Helpful
  Reply
user12106862 wrote:

I do not understand what you mean by saying if I can construct the required payload in PL/SQL that needs to be delivered?
The UTL_HTTP is simply a transport mechanism. It does not create the HTML or XML payload that you want to send to the web server, for you.

If you for example use your web browser to complete a web form and hit the submit button – your browser submits data to the web server. This can be a PUT or GET http command and it will include the data you’ve entered in the web browser – this is then send to the web server.

UTL_HTTP is only the communication interface part of the web browser. This means that if you want your custom “PL/SQL Web Browser” to submit stuff to the web server, you need to manually create that stuff.

Here’s an example of a basic SOAP call from PL/SQL.

create or replace function getSomeData( someParam varchar2 ) return xmlType is

SOAP_URL        constant varchar2(1000) :=

‘http://some-web-server.mydomain.com/webservices/soaphandler’;

 

— the SOAP envelope format depends on the service being called – the parameters

— and data it expects (here’s a basic example)

SOAP_ENVELOPE  constant varchar2(32767) :=

‘<SOAP-ENV:Envelope

xmlns:SOAP-ENV=”http://schemas.xmlsoap.org/soap/envelope/”

xmlns:xsi=”http://www.w3.org/1999/XMLSchema-instance”

xmlns:xsd=”http://www.w3.org/1999/XMLSchema”>

<SOAP-ENV:Body>

<ns1:SomeStatusEnquiry xmlns:ns1=”urn:fh”>

<PARAM>$MY_PARAM_GOES_HERE</PARAM>

</ns1:SomeStatusEnquiry>

</SOAP-ENV:Body>

</SOAP-ENV:Envelope>’;

 

— our local variables

request         UTL_HTTP.req;

response        UTL_HTTP.resp;

buffer          varchar2(32767);

httpData        clob;

eof             boolean;

soapEnvelope    varchar2(32767) := SOAP_ENVELOPE;

soapAction      varchar2(100) := ‘SomeStatusEnquiry’;

xml             xmlType;

begin

— formatting the soap envelope (we replace placeholders with actual values)

— (when done, the SOAP envelope must be valid and contain our parameters and data

— we want to send to the web service)

soapEnvelope := replace(SOAP_ENVELOPE, ‘$MY_PARAM_GOES_HERE’, someParam );

 

— our “browser” settings

UTL_HTTP.set_response_error_check( true );

UTL_HTTP.set_detailed_excp_support( true );

UTL_HTTP.set_cookie_support( true );

UTL_HTTP.set_transfer_timeout( 30 );

UTL_HTTP.set_follow_redirect( 3 );

UTL_HTTP.set_persistent_conn_support( true );

 

— we perform a HTTP post

request := UTL_HTTP.begin_request( SOAP_URL, ‘POST’, UTL_HTTP.HTTP_VERSION_1_1 );

UTL_HTTP.set_header( request, ‘User-Agent’, ‘Mozilla/4.0 (compatible)’ );

UTL_HTTP.set_header( request, ‘Content-Type’, ‘text/xml; charset=utf-8’ );

UTL_HTTP.set_header( request, ‘Content-Length’, length(soapEnvelope) );

UTL_HTTP.set_header( request, ‘SOAPAction’, soapAction );

UTL_HTTP.write_text( request, soapEnvelope );

 

— we grab the response and process it (saving it as a CLOB)

response := UTL_HTTP.get_response( request );

DBMS_LOB.CreateTemporary( httpData, true );

eof := false;

loop

exit when eof;

begin

UTL_HTTP.read_text( response, buffer, 32767 );

if (buffer is not null) and length(buffer)>0 then

DBMS_LOB.writeAppend( httpData, length(buffer), buffer );

end if;

exception when UTL_HTTP.END_OF_BODY then

eof := true;

end;

 

end loop;

UTL_HTTP.end_response( response );

 

— we now parse the CLOB using Oracle’s XML parser and create a XML DOM

xml := xmlType( httpData );

DBMS_LOB.FreeTemporary( httpData );

 

finally we return the XML DOM

return( xml );

 

exception when OTHERS then

UTL_HTTP.end_response( response );

if httpData is not null then

DBMS_LOB.FreeTemporary( httpData );

end if;

raise;

end;

Now if the SOAP envelope has an attachment, you will need to write the contents of that attachment into the envelope. And that is where the encoding part becomes important – as XML is text and not binary. If you want to add binary data into XML, that binary data needs to be encoded into character text for transmission. The receiver will then decode it back to binary.

The first thing I would do, is to request the XML definition for the SOAP envelope, together with an actual example. This will tell you what the contents of the payload is that you need to submit to the web service.

 

Author: admin