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
Related articles.
- Web Services and the Oracle Database
- Consuming Web Services in Oracle (SOAP)
- UTL_DBWS - Consuming Web Services in Oracle 10g Onward (SOAP)
- APEX Articles
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.
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:
- APEX_WEB_SERVICE
- Web Services and the Oracle Database
- Consuming Web Services in Oracle (SOAP)
- UTL_DBWS - Consuming Web Services in Oracle 10g Onward (SOAP)
- Fine-Grained Access to Network Services in Oracle Database 11g Release 1
- Fine-Grained Access to Network Services Enhancements in Oracle Database 12c Release 1
- Oracle Application Express (APEX) Installation
- APEX Articles
Hope this helps. Regards Tim...