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.
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;
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>
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
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.
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 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.
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 normal view: https://oracle-base.com/articles/misc/xml-over-http