APEX_WEB_SERVICE : Consuming SOAP and REST Web Services

21 de marzo de 2015 - 07:59 - Oracle

Even if you don't want to use Oracle Application Express (APEX), as a PL/SQL developer it is a good idea for you to get it installed on your database because it gives you access to a number of useful APIs. One example of that is the APEX_WEB_SERVICE package for interacting with SOAP and REST web services. This article gives some simple examples of using this package.

APEX Installation

The first thing you need to do is make sure APEX is installed on your machine.

CONN / AS SYSDBA

SELECT username,
       account_status,
       TO_CHAR(lock_date, 'DD-MON-YYYY') AS lock_date,
       TO_CHAR(expiry_date, 'DD-MON-YYYY') AS expiry_date,
       default_tablespace,
       temporary_tablespace
FROM   dba_users
WHERE  username LIKE UPPER('%APEX%')
ORDER BY username;

USERNAME             ACCOUNT_STATUS   LOCK_DATE   EXPIRY_DATE DEFAULT_TABLESP TEMPORARY_TABLE
-------------------- ---------------- ----------- ----------- --------------- ---------------
APEX_030200          EXPIRED & LOCKED 03-NOV-2011 03-NOV-2011 SYSAUX          TEMP
APEX_PUBLIC_USER     EXPIRED & LOCKED 03-NOV-2011 03-NOV-2011 USERS           TEMP

3 rows selected.

SQL>

In this case we have the version that shipped with 11gR2, which is quite old. Since we need to alter the major version (3.2 to 4.2), we need to do a full installation, which will do the upgrade for us. If we already had an older version of 4.2, we might want to patch to the latest version before starting. You can see how to install and patch using these articles.

Network ACL

If you are using Oracle Database 11g or higher, you will need an ACL to allow access to external network services. Here is an 11g example of creating an ACL to allow the APEX_040200user to access "oracle-base.com".

CONN / AS SYSDBA
BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'oracle_base_acl.xml', 
    description  => 'An ACL for the oracle-base.com website',
    principal    => 'APEX_040200',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'oracle_base_acl.xml',
    host        => 'oracle-base.com', 
    lower_port  => 80,
    upper_port  => 80); 

  COMMIT;
END;
/

Things changed a little with Oracle Database 12c. Here is an example for 12c using the new approach.

CONN / AS SYSDBA
BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'oracle-base.com', 
    lower_port => 80,
    upper_port => 80,
    ace        => xs$ace_type(privilege_list => xs$name_list('http'),
                              principal_name => 'APEX_040200',
                              principal_type => xs_acl.ptype_db)); 
END;
/

SOAP

The URL of the WDSL file describing the SOAP web 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.

The 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, which returns the response as an XMLTYPE. If you prefer, you can use this XML directly, or use the PARSE_XML function to return specific values from the XML using XPATH expressions.

CREATE OR REPLACE FUNCTION add_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:tns="http://www.ignyte.com/whatsshowing"
  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 output below shows the function in action.

SELECT add_numbers(1, 5) FROM dual;

ADD_NUMBERS(1,5)
----------------
               6

SQL>

SELECT add_numbers(10, 15) FROM dual;

ADD_NUMBERS(10,15)
------------------
                25

SQL>

If we turn on the trace output, we can see the document returned by the web service.

SET SERVEROUTPUT ON
SELECT add_numbers(1, 5) FROM dual;

ADD_NUMBERS(1,5)
----------------
               6

1 row selected.

l_xml=<?xml version="1.0" encoding="ISO-8859-1"?><SOAP-ENV:Envelope
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"><SOAP-ENV:Body><ns1:ws_addResponse
xmlns:ns1="http://oracle-base.com/webservices/"><return
xsi:type="xsd:string">6</return></ns1:ws_addResponse></SOAP-ENV:Body></SOAP-ENV:Envelope>

l_result=6
SQL>

REST

The URL of the REST web service used in this example is shown here (http://oracle-base.com/webservices/add-numbers.php). The web service accepts two number parameters and returns the sum of those values.

As the name suggests, the MAKE_REST_REQUEST function allows you to process REST web service requests. The following example creates a function to add two numbers together using a web service. It sends the parameters to the REST URL, which returns the response as a CLOB. If you prefer, you can use the contents of the CLOB directly, or use the PARSE_XML function to return specific values from the XML using XPATH expressions.

CREATE OR REPLACE FUNCTION add_numbers (p_int_1  IN  NUMBER,
                                        p_int_2  IN  NUMBER)
  RETURN NUMBER
AS
  l_clob    CLOB;
  l_result  VARCHAR2(32767);
BEGIN

  -- Get the XML response from the web service.
  l_clob := APEX_WEB_SERVICE.make_rest_request(
    p_url         => 'http://oracle-base.com/webservices/add-numbers.php',
    p_http_method => 'GET',
    p_parm_name   => APEX_UTIL.string_to_table('p_int_1:p_int_2'),
    p_parm_value  => APEX_UTIL.string_to_table(p_int_1 || ':' || p_int_2)
  );

  -- Display the whole SOAP document returned.
  DBMS_OUTPUT.put_line('l_clob=' || l_clob);

  -- Pull out the specific value of interest.
  l_result := APEX_WEB_SERVICE.parse_xml(
    p_xml   => XMLTYPE(l_clob),
    p_xpath => '//answer/number/text()'
  );

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

  RETURN TO_NUMBER(l_result);
END;
/

The output below shows the function in action.

SELECT add_numbers(1, 5) FROM dual;

ADD_NUMBERS(1,5)
----------------
               6

SQL>

SELECT add_numbers(10, 15) FROM dual;

ADD_NUMBERS(10,15)
------------------
                25

SQL>

If we turn on the trace output, we can see the document returned by the web service.

SET SERVEROUTPUT ON
SELECT add_numbers(1, 5) FROM dual;

ADD_NUMBERS(1,5)
----------------
               6

1 row selected.

l_clob=<?xml version="1.0" encoding="UTF-8"?><answer><number>6</number></answer>
l_result=6
SQL>

HTTPS (SSL) Web Services

If you want to access web services using HTTPS (SSL), then you will need to create an Oracle wallet to hold the trusted certificates. You can see how that is done here.

The MAKE_REQUEST and MAKE_REST_REQUEST routines accept P_WALLET_PATH and P_WALLET_PWD parameters, allowing you to specify the wallet location and password.

p_wallet_path => 'file:/home/oracle/wallets',
p_wallet_pwd  => 'MyPassword1'

For more information see:

http://oracle-base.com/articles/misc/apex_web_service-consuming-soap-and-rest-web-services.php