There is a newer version of this article here.
XML-over-HTTP was the precursor to web services allowing easy access to XML via HTTP GETs and POSTs. If you like the idea of web services but don't know which of the emerging standards to follow why not go back to basics. Using Database Access Descriptors (DADs) you can access XML data directly from the database, or via 9iAS, with relatively little PL/SQL code. In this article I'll demonstrate a simple way to enable XML-over-HTTP.
First we must set up a Database Access Descriptor (DAD) to allow the webserver to connect to the database when a specific URL is requested.
The list of DADs will now contain the SCOTT entry, which can be edited if necessary by clicking the edit icon next to it.
Next we create the code to produce two different XML documents.
CREATE OR REPLACE TYPE emp_type AS OBJECT ( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) ); / CREATE OR REPLACE PACKAGE emp_api AS PROCEDURE get_emp (p_empno IN emp.empno%TYPE); PROCEDURE get_emp_by_dept (p_deptno IN emp.deptno%TYPE); END; / show errors CREATE OR REPLACE PACKAGE BODY emp_api AS PROCEDURE get_emp (p_empno IN emp.empno%TYPE) IS v_emp emp%ROWTYPE; BEGIN SELECT * INTO v_emp FROM emp WHERE empno = p_empno; OWA_UTIL.Mime_Header('text/xml'); HTP.Print( '<?xml version="1.0"?>' || '<EMP>' || ' <EMPNO>'||v_emp.empno||'</EMPNO>' || ' <ENAME>'||v_emp.ename||'</ENAME>' || ' <JOB>'||v_emp.job||'</JOB>' || ' <MGR>'||v_emp.mgr||'</MGR>' || ' <HIREDATE>'||v_emp.hiredate||'</HIREDATE>' || ' <SAL>'||v_emp.sal||'</SAL>' || ' <COMM>'||v_emp.comm||'</COMM>' || ' <DEPTNO>'||v_emp.deptno||'</DEPTNO>' || '</EMP>'); EXCEPTION WHEN OTHERS THEN OWA_UTIL.Mime_Header('text/xml'); HTP.Print( '<?xml version="1.0"?>' || '<ROWSET>' || ' <ERROR>'||SQLERRM||'</ERROR>' || '</ROWSET>'); END; PROCEDURE get_emp_by_dept (p_deptno IN emp.deptno%TYPE) IS v_clob CLOB; BEGIN SELECT SYS_XMLAGG( SYS_XMLGEN( emp_type(empno, ename, job, mgr, hiredate, sal, comm, deptno), XMLFormat.createFormat('EMP') ), XMLFormat.createFormat('EMP_LIST') ).getClobVal() INTO v_clob FROM emp WHERE deptno = p_deptno ORDER BY empno; OWA_UTIL.Mime_Header('text/xml'); HTP.Print(v_clob); END; END; / show errors
The emp_api.get_emp
procedure retrieves the EMP
record specified by the p_empno
parameter. Then
it manually builds the XML document and sends it to the requester using the HTP.Print
procedure. This method works
equally well for Oracle8i and Oracle9i.
The emp_api.get_emp_by_dept
procedure retrieves the EMP
records for the department specified by the
p_deptno
parameter. In this example the SYS_XMLAGG
, SYS_XMLGEN
and XMLFormat
(xmlGenFormatType
in Release 1) functions available in Oracle9i are used to build the XML document within the query.
Once again the HTP.Print
procedure is used to send the finished document.
With the DAD configured and the PL/SQL code in place the XML document can be retrieved by entering the correct URL into a browser.
http://yourServer:7777/pls/SCOTT/emp_api.get_emp?p_empno=7369 http://yourServer:7777/pls/SCOTT/emp_api.get_emp_by_dept?p_deptno=30
For security reasons you may wish to access the data over SSL.
https://yourServer:443/pls/SCOTT/emp_api.get_emp?p_empno=7369 https://yourServer:443/pls/SCOTT/emp_api.get_emp_by_dept?p_deptno=30
The SSL port is listed in the $ORACLE_HOME/Apache/Apache/setupinfo.txt file.
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/9i/xml-over-http-9i