📋Steps in this guide1/7

APEX_WEB_SERVICE : Consuming SOAP and REST Web Services

Use the APEX_WEB_SERVICE package to simplify consuming SOAP and REST web services from PL/SQL.

oracle miscconfigurationintermediate

by OracleDba

14 views

APEX Installation

The first thing you need to do is make sure APEX is installed on your machine. 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

Code/Command (click line numbers to comment):

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>

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 user to access "oracle-base.com". For difference versions of APEX the principal name may change. Things changed a little with Oracle Database 12c. Here is an example for 12c using the new approach.

Code/Command (click line numbers to comment):

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;
/

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 package contains a procedure and function called 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 , which returns the response as an . If you prefer, you can use this XML directly, or use the function to return specific values from the XML using XPATH expressions. The output below shows the function in action. If we turn on the trace output, we can see the document returned by the web service.

Code/Command (click line numbers to comment):

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;
/

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>

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 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 . If you prefer, you can use the contents of the CLOB directly, or use the function to return specific values from the XML using XPATH expressions. The output below shows the function in action. If we turn on the trace output, we can see the document returned by the web service.

Code/Command (click line numbers to comment):

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;
/

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>

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 and routines accept and parameters, allowing you to specify the wallet location and password.

Code/Command (click line numbers to comment):

1
2

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

Authentication

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

Code/Command (click line numbers to comment):

1
2

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

Headers and Cookies

Headers can be set and read using a collection as follows. In the same way cookies can be set and read using a collection as follows. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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;
/

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;
/

Comments (0)

Please to add comments

No comments yet. Be the first to comment!