Socialmedia

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.

• Test Schema
• Database Access Descriptor Configuration
• Volatile Data
• Static Data
• REST-Style URLs

Test SchemaFirst we create a test schema.CONN / AS SYSDBACREATE 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 DescriptorBEGIN 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———– 8080SQL>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 DataFor 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 in the same two formats.CONN test/testCREATE OR REPLACE PACKAGE xml_api ASPROCEDURE 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 ERRORSCREATE OR REPLACE PACKAGE BODY xml_api ASPROCEDURE error_page (p_message IN VARCHAR2);PROCEDURE get_emp_1 (p_empno IN emp.empno%TYPE DEFAULT NULL) ISBEGIN OWA_UTIL.mime_header(‘text/xml’); HTP.print(‘<?xml version=”1.0″?>’); 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) ISBEGIN OWA_UTIL.mime_header(‘text/xml’); HTP.print(‘<?xml version=”1.0″?>’); 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 XMLROOT( 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 XMLROOT( 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) ASBEGIN OWA_UTIL.mime_header(‘text/xml’); HTP.print( ‘<?xml version=”1.0″?>’ || ‘<rowset>’ || ‘ <error>’ || DBMS_XMLGEN.convert(p_message) || ‘</error>’ || ‘</rowset>’);END error_page;END xml_api;/SHOW ERRORSThe 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:

test:test@myserver:8080/xml_demo/xml_api.get_emp_1test:test@myserver:8080/xml_demo/xml_api.get_emp_1&p_empno=7369test:test@myserver:8080/xml_demo/xml_api.get_emp_2test:test@myserver:8080/xml_demo/xml_api.get_emp_2&p_empno=7369test:test@myserver:8080/xml_demo/xml_api.get_emp_3test:test@myserver:8080/xml_demo/xml_api.get_emp_3&p_empno=7369test:test@myserver:8080/xml_demo/xml_api.get_emp_4test:test@myserver:8080/xml_demo/xml_api.get_emp_4&p_empno=7369Static DataIn 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 SYSDBAEXEC 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 ONDECLARE 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/testCREATE 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 XMLROOT( 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 ERRORSEvery 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, so 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 ONEXEC semi_static;The file is now present in the XML DB repository, so we can access it using HTTP, FTP and WebDAV.test:test@myserver:8080/public/mydocs/semi_static.xmlftp://test:test@myserver:9021/public/mydocs/semi_static.xmlFor WebDAV, map a drive using the HTTP URL and you will see the file in the mapped directory.Rest-Style URLsYou 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 SYSDBABEGIN 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/testCREATE 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 ERRORSCREATE OR REPLACE PACKAGE BODY rest_api ASPROCEDURE error_page (p_message IN VARCHAR2);PROCEDURE get_emps (p_empno IN emp.empno%TYPE DEFAULT NULL) IS l_clob CLOB;BEGIN SELECT XMLROOT( 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 XMLROOT( 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) ASBEGIN OWA_UTIL.mime_header(‘text/xml’); HTP.print( ‘<?xml version=”1.0″?>’ || ‘<rowset>’ || ‘ <error>’ || DBMS_XMLGEN.convert(p_message) || ‘</error>’ || ‘</rowset>’);END error_page;END rest_api;/SHOW ERRORSThese procedures can be accessed using the original style URLs.test:test@myserver:8080/xml_demo/rest_api.get_empstest:test@myserver:8080/xml_demo/rest_api.get_emps&p_empno=7900test:test@myserver:8080/xml_demo/rest_api.get_deptstest:test@myserver:8080/xml_demo/rest_api.get_depts&p_deptno=10But they can also now be accessed using a REST-Style URL.test:test@myserver:8080/xml_demo/rest-ws/emptest:test@myserver:8080/xml_demo/rest-ws/emp/7900test:test@myserver:8080/xml_demo/rest-ws/depttest:test@myserver:8080/xml_demo/rest-ws/dept/10For more information see:

• DBMS_EPG
• DBMS_XDB
• DBMS_XMLGEN
• Oracle XML DB Developer’s Guide 11g Release 2 (11.2)

Hope this helps. Regards Tim…

Back to the Top.