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

Home » Articles » Misc » Here

XML-Over-HTTP (REST Web Services) From PL/SQL

This article is an update of an Oracle 9i article (here).

XML-over-HTTP was the precursor to REST and SOAP web services, allowing easy access to XML via HTTP GETs and POSTs. Using Database Access Descriptors (DADs) you can access XML data directly from the database, or via a HTTP server, with relatively little PL/SQL code. In this article I'll demonstrate a simple way to enable XML-over-HTTP.

Related articles.

Test Schema

First we create a test schema.

CONN / AS SYSDBA

CREATE USER test IDENTIFIED BY test DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE, CREATE PROCEDURE TO test;

CREATE TABLE test.emp AS SELECT * FROM scott.emp;
CREATE TABLE test.dept AS SELECT * FROM scott.dept;

Database Access Descriptor Configuration (DAD)

For this article I will be using the Embedded PL/SQL Gateway to provide access to PL/SQL from the browser. The following code creates a DAD called "xml_demo" which points at an Oracle test user.

CONN / AS SYSDBA

-- Create a Database Access Descriptor
BEGIN
  DBMS_EPG.create_dad (
    dad_name => 'xml_demo',
    path     => '/xml_demo/*');
END;
/

BEGIN
  DBMS_EPG.authorize_dad (
    dad_name => 'xml_demo',
    user     => 'TEST');
END;
/

We must also make sure the HTTP port for XML DB is set.

SELECT DBMS_XDB.gethttpport FROM dual;

GETHTTPPORT
-----------
       8080

SQL>

If the value was not set, we could set it with the following procedure call.

EXEC DBMS_XDB.sethttpport(8080);

PL/SQL procedure successfully completed.

SQL>

Volatile Data

For volatile data, we need to make sure the XML is produced each time a request is made for it. This is simple using the PL/SQL Web Toolkit. The following package contains four different procedures that produce the same data as XML. The first two hand-craft the XML in tag-based and attribute-based form. The next two generate the XML directly using built in SQL functions (SQL/XML) in the same two formats.

CONN test/test

CREATE OR REPLACE PACKAGE xml_api AS

PROCEDURE get_emp_1 (p_empno  IN  emp.empno%TYPE DEFAULT NULL);
PROCEDURE get_emp_2 (p_empno  IN  emp.empno%TYPE DEFAULT NULL);
PROCEDURE get_emp_3 (p_empno  IN  emp.empno%TYPE DEFAULT NULL);
PROCEDURE get_emp_4 (p_empno  IN  emp.empno%TYPE DEFAULT NULL);

END xml_api;
/
SHOW ERRORS


CREATE OR REPLACE PACKAGE BODY xml_api AS

PROCEDURE error_page (p_message IN VARCHAR2);

PROCEDURE get_emp_1 (p_empno  IN  emp.empno%TYPE DEFAULT NULL) IS
BEGIN
  OWA_UTIL.mime_header('text/xml');  
  HTP.print('<emp_list>');

  FOR cur_rec IN (SELECT empno, ename, job FROM emp WHERE p_empno IS NULL OR empno = p_empno) LOOP
  HTP.print(
    '<emp>'
    || '<empno>' || cur_rec.empno || '</empno>'
    || '<ename>' || DBMS_XMLGEN.convert(cur_rec.ename) || '</ename>'
    || '<job>' || DBMS_XMLGEN.convert(cur_rec.job) || '</job>'
    || '</emp>');
  END LOOP;
  HTP.print('</emp_list>');
EXCEPTION
  WHEN OTHERS THEN
     error_page(SQLERRM);
END get_emp_1;


PROCEDURE get_emp_2 (p_empno  IN  emp.empno%TYPE DEFAULT NULL) IS
BEGIN
  OWA_UTIL.mime_header('text/xml');  
  HTP.print('<emp_list>');

  FOR cur_rec IN (SELECT empno, ename, job FROM emp WHERE p_empno IS NULL OR empno = p_empno) LOOP
  HTP.print(
    '<emp'
    || ' empno="' || cur_rec.empno || '"'
    || ' ename="' || DBMS_XMLGEN.convert(cur_rec.ename) || '"'
    || ' job="' || DBMS_XMLGEN.convert(cur_rec.job) || '"'
    || ' />');
  END LOOP;
  HTP.print('</emp_list>');
EXCEPTION
  WHEN OTHERS THEN
     error_page(SQLERRM);
END get_emp_2;


PROCEDURE get_emp_3 (p_empno  IN  emp.empno%TYPE DEFAULT NULL) IS
  l_clob  CLOB;
BEGIN
  SELECT XMLELEMENT("emp_list",
           XMLAGG(
             XMLELEMENT("emp",
               XMLFOREST(empno AS "empno",
                         ename AS "ename",
                         job AS "job"
               )
             )
           )
         ).getClobVal()
  INTO   l_clob
  FROM   emp
  WHERE  p_empno IS NULL OR empno = p_empno;

  OWA_UTIL.mime_header('text/xml');  
  HTP.print(l_clob);
EXCEPTION
  WHEN OTHERS THEN
     error_page(SQLERRM);
END get_emp_3;


PROCEDURE get_emp_4 (p_empno  IN  emp.empno%TYPE DEFAULT NULL) IS
  l_clob  CLOB;
BEGIN
  SELECT XMLELEMENT("emp_list",
           XMLAGG(
             XMLELEMENT("emp",
               XMLATTRIBUTES(empno AS "empno",
                             ename AS "ename",
                             job AS "job"
               )
             )
           )        
         ).getClobVal()
  INTO   l_clob
  FROM   emp
  WHERE  p_empno IS NULL OR empno = p_empno;

  OWA_UTIL.mime_header('text/xml');  
  HTP.print(l_clob);
EXCEPTION
  WHEN OTHERS THEN
     error_page(SQLERRM);
END get_emp_4;


PROCEDURE error_page (p_message IN VARCHAR2) AS
BEGIN
  OWA_UTIL.mime_header('text/xml');
  HTP.print(
       '<rowset>'
    || '  <error>' || DBMS_XMLGEN.convert(p_message) || '</error>'
    || '</rowset>');
END error_page;

END xml_api;
/
SHOW ERRORS

The xml_api.get_emp_n procedures retrieve the EMP record specified by the p_empno parameter, or all records if the parameter is NULL.

With the DAD configured and the PL/SQL code in place the XML documents can be retrieved by entering the correct URL into a browser.

http://test:test@myserver:8080/xml_demo/xml_api.get_emp_1
http://test:test@myserver:8080/xml_demo/xml_api.get_emp_1?p_empno=7369
http://test:test@myserver:8080/xml_demo/xml_api.get_emp_2
http://test:test@myserver:8080/xml_demo/xml_api.get_emp_2?p_empno=7369
http://test:test@myserver:8080/xml_demo/xml_api.get_emp_3
http://test:test@myserver:8080/xml_demo/xml_api.get_emp_3?p_empno=7369
http://test:test@myserver:8080/xml_demo/xml_api.get_emp_4
http://test:test@myserver:8080/xml_demo/xml_api.get_emp_4?p_empno=7369

Static Data

In the previous section we saw how volatile data could be presented as XML. We could use this method for static and semi-static data, but we would be needlessly regenerating XML that had not changed since the last request. It would be far better to generate the XML once, then keep presenting the original document until such as time as the data changes and the document is regenerated. Fortunately XML DB allows us to do this by creating resources in its internal file system. These resources can then be accessed via HTTP, FTP and WebDAV.

We have already turned on HTTP access to the XML DB repository using the DBMS_XDB.sethttpport procedure. If we want FTP access we must open a port for it as follows.

CONN / AS SYSDBA

EXEC DBMS_XDB.setftpport(9021);

PL/SQL procedure successfully completed.

SQL>

Next we create a folder in the XML DB file system to hold our file. We expect the deletion of the folder to fail if it is the first time you've run the script, so don't worry out it.

SET SERVEROUTPUT ON
DECLARE
  l_return BOOLEAN;
  
  ex_delete_failure EXCEPTION;
  PRAGMA EXCEPTION_INIT(ex_delete_failure, -31001);
BEGIN
  -- Delete the folder if it already exists.
  BEGIN
    DBMS_XDB.deleteresource('/public/mydocs', DBMS_XDB.DELETE_RECURSIVE);
  EXCEPTION
    WHEN ex_delete_failure THEN
      DBMS_OUTPUT.put_line(SQLERRM);
  END;

  -- Create the folder.
  l_return := DBMS_XDB.createfolder('/public/mydocs');
  COMMIT;
END;
/

Next we write a procedure to create the XML data as an XML DB resource in the folder.

CONN test/test

CREATE OR REPLACE PROCEDURE semi_static AS
  l_return BOOLEAN;
  l_clob   CLOB;
  
  ex_delete_failure EXCEPTION;
  PRAGMA EXCEPTION_INIT(ex_delete_failure, -31001);
BEGIN
  -- Generate some XML and place it in a CLOB.
  SELECT XMLELEMENT("emp_list",
           XMLAGG(
             XMLELEMENT("emp",
               XMLATTRIBUTES(empno AS "empno",
                             ename AS "ename",
                             job AS "job"
               )
             )
           )        
         ).getClobVal()
  INTO   l_clob
  FROM   emp;
   
  -- Delete the resource it if is already present.
  BEGIN
    DBMS_XDB.deleteresource('/public/mydocs/semi_static.xml', DBMS_XDB.DELETE_FORCE);
  EXCEPTION
    WHEN ex_delete_failure THEN
      DBMS_OUTPUT.put_line('Delete of file failed. Probably first time call.');
      DBMS_OUTPUT.put_line(SQLERRM);
  END;
  
  -- Create the resource using the data in the CLOB.
  l_return := DBMS_XDB.createresource('/public/mydocs/semi_static.xml', l_clob);
  
  DBMS_XDB.setACL('/public/mydocs/semi_static.xml',
                  '/sys/acls/all_owner_acl.xml');
  COMMIT;
END semi_static;
/
SHOW ERRORS

Every time the semi_static procedure is called it will replace the XML document with a fresh copy of the data. This could be called from a scheduled job, or a trigger. The first call will produce an error message associated with the failure to delete the file. This message can be ignored.

SET SERVEROUTPUT ON
EXEC semi_static;

The file is now present in the XML DB repository, so we can access it using HTTP, FTP and WebDAV.

http://test:test@myserver:8080/public/mydocs/semi_static.xml
ftp://test:test@myserver:9021/public/mydocs/semi_static.xml

For WebDAV, map a drive using the HTTP URL and you will see the file in the mapped directory.

REST-Style URLs

You will have noticed that XML over HTTP looks very similar to REST web services, with the exception of the URL. We can mimic REST style URLs using the PATH-ALIAS and PATH-ALIAS-PROCEDURE DAD attributes. The PATH-ALIAS attribute identifies an expression to look out for in a URL. The PATH-ALIAS-PROCEDURE attribute indicates what procedure should be called when the previous expression is found. Using our existing DAD, we will associate the rest_api.handle_request procedure with the expression "rest-ws".

CONN / AS SYSDBA

BEGIN
  DBMS_EPG.set_dad_attribute(
    dad_name   => 'xml_demo',
    attr_name  => 'path-alias',
    attr_value => 'rest-ws');
    
  DBMS_EPG.set_dad_attribute(
    dad_name   => 'xml_demo',
    attr_name  => 'path-alias-procedure',
    attr_value => 'rest_api.handle_request');
END;
/

Next we code the rest_api to intercept the requests and do something with them. The handle_request procedure uses some APEX functionality to split the URL, but this could easily be done using string_api.split_test instead.

CONN test/test

CREATE OR REPLACE PACKAGE rest_api AS?

  PROCEDURE get_emps (p_empno  IN  emp.empno%TYPE DEFAULT NULL);
  PROCEDURE get_depts (p_deptno  IN  dept.deptno%TYPE DEFAULT NULL);
  
  PROCEDURE handle_request(p_path IN VARCHAR2);
  
END rest_api;
/
SHOW ERRORS


CREATE OR REPLACE PACKAGE BODY rest_api AS

PROCEDURE error_page (p_message IN VARCHAR2);

PROCEDURE get_emps (p_empno IN emp.empno%TYPE DEFAULT NULL) IS
  l_clob  CLOB;
BEGIN
  SELECT XMLELEMENT("emp_list",
           XMLAGG(
             XMLELEMENT("emp",
               XMLATTRIBUTES(empno AS "empno",
                             ename AS "ename",
                             job AS "job"
               )
             )
           )        
         ).getClobVal()
  INTO   l_clob
  FROM   emp
  WHERE  p_empno IS NULL OR empno = p_empno;

  OWA_UTIL.mime_header('text/xml');  
  HTP.print(l_clob);
EXCEPTION
  WHEN OTHERS THEN
    error_page(SQLERRM);
END get_emps;


PROCEDURE get_depts (p_deptno IN dept.deptno%TYPE DEFAULT NULL) IS
  l_clob  CLOB;
BEGIN
  SELECT XMLELEMENT("dept_list",
           XMLAGG(
             XMLELEMENT("dept",
               XMLATTRIBUTES(deptno AS "deptno",
                             dname AS "dname",
                             loc AS "loc"
               )
             )
           )        
         ).getClobVal()
  INTO   l_clob
  FROM   dept
  WHERE  p_deptno IS NULL OR deptno = p_deptno;

  OWA_UTIL.mime_header('text/xml');  
  HTP.print(l_clob);
EXCEPTION
  WHEN OTHERS THEN
    error_page(SQLERRM);
END get_depts;


PROCEDURE handle_request(p_path IN VARCHAR2) IS
  l_path_arr  apex_application_global.vc_arr2;
  l_subject   VARCHAR2(32767);
  l_id        VARCHAR2(32767) := NULL;
BEGIN
  l_path_arr := apex_util.string_to_table(p_path || '/', '/');
  l_subject  := l_path_arr(1);
  l_id       := l_path_arr(2);
  
  CASE LOWER(l_subject)
  WHEN 'emp' THEN
    get_emps(p_empno => TO_NUMBER(l_id));
  WHEN 'dept' THEN
    get_depts(p_deptno => TO_NUMBER(l_id));
  ELSE
    HTP.Print('Page not found.');
  END CASE;
END handle_request;


PROCEDURE error_page (p_message IN VARCHAR2) AS
BEGIN
  OWA_UTIL.mime_header('text/xml');
  HTP.print(
       '<rowset>'
    || '  <error>' || DBMS_XMLGEN.convert(p_message) || '</error>'
    || '</rowset>');
END error_page;

END rest_api;
/
SHOW ERRORS

These procedures can be accessed using the original style URLs.

http://test:test@myserver:8080/xml_demo/rest_api.get_emps
http://test:test@myserver:8080/xml_demo/rest_api.get_emps?p_empno=7900
http://test:test@myserver:8080/xml_demo/rest_api.get_depts
http://test:test@myserver:8080/xml_demo/rest_api.get_depts?p_deptno=10

But they can also now be accessed using a REST-Style URL.

http://test:test@myserver:8080/xml_demo/rest-ws/emp
http://test:test@myserver:8080/xml_demo/rest-ws/emp/7900
http://test:test@myserver:8080/xml_demo/rest-ws/dept
http://test:test@myserver:8080/xml_demo/rest-ws/dept/10

Performance Considerations

Performance problems associated with this method are almost always caused by badly performing SQL. If you are generating XML using complex and slow SQL, the web service is never going to respond quickly. Possible solutions to this are as follows.

JSON

Many of the techniques discussed in this article apply equally well to JSON web services. If you want to know about how to generate JSON from within the database, check out the article here.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.