Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | 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.

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...

Back to the Top.