8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
XML-Over-HTTP
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.
Database Access Descriptor Configuration (DAD)
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.
- Access the database HTTP server main page via a browser (http://yourServer:7777/). The correct port number is listed in the $ORACLE_HOME/Apache/Apache/setupinfo.txt file
- Click on the "Mod_plsql Configuration Menu" link.
- Click on the "Gateway Database Access Descriptor Settings" link.
- Click on the "Add Default (blank configuration)" link.
- Enter SCOTT as the Database Access Descriptor Name. This will be used in the requesting URL.
- Enter the username (SCOTT), password (TIGER) and connect string (W2K1) for the desired database connection.
- Select the "Basic" authentication mode.
- Click the OK button at the top right of the screen.
The list of DADs will now contain the SCOTT entry, which can be edited if necessary by clicking the edit icon next to it.
Create PL/SQL Code
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.
Test It
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...