8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

APEX_WEB_SERVICE : Consuming SOAP and REST Web Services

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
Network ACL
SOAP Web Service
REST Web Service
HTTPS (SSL) Web Services
Authentication
Headers and Cookies

APEX Installation

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

CONN / AS SYSDBA
-- ALTER SESSION SET CONTAINER=pdb1;

SET LINESIZE 130

COLUMN username FORMAT A25
COLUMN account_status FORMAT A15
COLUMN default_tablespace FORMAT A20
COLUMN temporary_tablespace FORMAT A20

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_TABLESPACE   TEMPORARY_TABLESPACE
------------------------- --------------- -------------------- -------------------- -------------------- --------------------
APEX_190200               LOCKED          22-DEC-2019                               APEX                 TEMP
APEX_INSTANCE_ADMIN_USER  LOCKED          22-DEC-2019                               USERS                TEMP
APEX_LISTENER             OPEN                                 19-JUN-2020          USERS                TEMP
APEX_PUBLIC_USER          OPEN                                 19-JUN-2020          USERS                TEMP
APEX_REST_PUBLIC_USER     OPEN                                 19-JUN-2020          USERS                TEMP

SQL>

This article was originally written for APEX 4.2, but it applies equally well to later releases. It also works fine in non-CDB and multitenant databases.

If you don't have an APEX installation, you can do one user this article.

Oracle Application Express (APEX) Installation

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_190200 user to access "oracle-base.com". For difference versions of APEX the principal name may change.

CONN / AS SYSDBA
-- ALTER SESSION SET CONTAINER=pdb1;

DECLARE
  --l_principal VARCHAR2(20) := 'APEX_040200';
  --l_principal VARCHAR2(20) := 'APEX_050000';
  --l_principal VARCHAR2(20) := 'APEX_050100';
  --l_principal VARCHAR2(20) := 'APEX_180200';
  --l_principal VARCHAR2(20) := 'APEX_190100';
  l_principal VARCHAR2(20) := 'APEX_190200';
BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'oracle_base_acl.xml', 
    description  => 'An ACL for the oracle-base.com website',
    principal    => l_principal,
    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
-- ALTER SESSION SET CONTAINER=pdb1;

DECLARE
  --l_principal VARCHAR2(20) := 'APEX_040200';
  --l_principal VARCHAR2(20) := 'APEX_050000';
  --l_principal VARCHAR2(20) := 'APEX_050100';
  --l_principal VARCHAR2(20) := 'APEX_180200';
  --l_principal VARCHAR2(20) := 'APEX_190100';
  l_principal VARCHAR2(20) := 'APEX_190200';
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 => l_principal,
                              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: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 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'

Authentication

The APEX_WEB_SERVICE package supports basic authentication. The MAKE_REQUEST and MAKE_REST_REQUEST routines accept credentials, which are used to authenticate to the web service.

p_username => 'my_username',
p_password => 'MyPassword'

Headers and Cookies

Headers can be set and read using a collection as follows.

SET SERVEROUTPUT ON
BEGIN
  APEX_WEB_SERVICE.g_request_headers.delete();
  APEX_WEB_SERVICE.g_request_headers(1).name := 'Content-Type';
  APEX_WEB_SERVICE.g_request_headers(1).value := 'application/json';

  DBMS_OUTPUT.put_line('name: ' || APEX_WEB_SERVICE.g_request_headers(1).name);
  DBMS_OUTPUT.put_line('value: ' || APEX_WEB_SERVICE.g_request_headers(1).value);
END;
/

In the same way cookies can be set and read using a collection as follows.

SET SERVEROUTPUT ON
BEGIN
  APEX_WEB_SERVICE.g_request_cookies.delete();
  APEX_WEB_SERVICE.g_request_cookies(1).name := 'username';
  APEX_WEB_SERVICE.g_request_cookies(1).value := 'me';

  DBMS_OUTPUT.put_line('name: ' || APEX_WEB_SERVICE.g_request_cookies(1).name);
  DBMS_OUTPUT.put_line('value: ' || APEX_WEB_SERVICE.g_request_cookies(1).value);
END;
/

For more information see:

Hope this helps. Regards Tim...

Back to the Top.