call SOAP request in oracle database

 

SOAP Request with by using XML

handling SOAP request in oracle database is easy and simple just make sure you have install and configure oracle apex with the database even apex is not in use.

The response will pass as XML and the return response as XML as well.

The URL of the WDSL file describing the SOAP service used in this example is shown here (http://oracle-base.com/webservices/server.php?wsdl). The web service accepts two number parameters and returns the sum of those values.

APEX_WEB_SERVICE package contains a procedure and function called MAKE_REQUEST that allow you to process SOAP web service requests. The following example creates a function to add two numbers together using a web service. It builds the appropriate SOAP document, sends it to the web service using MAKE_REQUEST, the return response as an XMLTYPE. you can use this XML directly, or use the PARSE_XML function to get specific values from the XML using XPATH.

This is support in any version of oracle database where oracle apex install and configure.

create below function in PLSQL

CREATE OR REPLACE FUNCTION add_the_numbers (p_int_1  IN  NUMBER,

                                        p_int_2  IN  NUMBER)

  RETURN NUMBER

AS

  l_envelope  CLOB;

  l_xml       XMLTYPE;

  l_result    VARCHAR2(32767);

BEGIN

  -- Build a SOAP document appropriate for the web service.

  l_envelope := '<?xml version="1.0" encoding="UTF-8"?>

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"

  xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <soap:Body>

    <ws_add xmlns="http://oracle-base.com/webservices/" soap:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">

      <int1 xsi:type="xsd:integer">' || p_int_1 || '</int1>

      <int2 xsi:type="xsd:integer">' || p_int_2 || '</int2>

    </ws_add>

  </soap:Body>

</soap:Envelope>';

  -- Get the XML response from the web service.

  l_xml := APEX_WEB_SERVICE.make_request(

    p_url      => 'http://oracle-base.com/webservices/server.php',

    p_action   => 'http://oracle-base.com/webservices/server.php/ws_add',

    p_envelope => l_envelope

  );

  -- Display the whole SOAP document returned.

  DBMS_OUTPUT.put_line('l_xml=' || l_xml.getClobVal());

  -- Pull out the specific value of interest.

  l_result := APEX_WEB_SERVICE.parse_xml(

    p_xml   => l_xml,

    p_xpath => '//return/text()',

    p_ns    => 'xmlns:ns1="http://oracle-base.com/webservices/"'

  );

  DBMS_OUTPUT.put_line('l_result=' || l_result);

  RETURN TO_NUMBER(l_result);

END;

/

the function can be call in sql statement as well.

summary

1. install and configure oracle APEX
2. create function that call the web service
3. return the result as whole or specific value