Lot of times, developers configure and build web service using Java, ASP or some other technology.
In most of the times, the web services called from Java program (or some thing of same sort) connects to database, pulls out chunk of data, processes them and returns them in nice XML structure. The program which called the web service gets the XML, and processes it and presents it to the viewer.
In Oracle database release 10g, Oracle supports native web services. i.e., you can setup and configure web services right inside database. So, how to do that? Let’s start with creating a user and a DAD for out testing.
CREATE USER test
IDENTIFIED BY test
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users
Now few grants
GRANT CONNECT, CREATE TABLE, CREATE PROCEDURE TO test
CREATE TABLE test.emp AS SELECT * FROM hr.employee
CREATE TABLE test.dept AS SELECT * FROM hr.department
Now create a DAD using DBMS_EPG. This is a very useful package which can be used for many interesting works.
dad_name => 'xml_demo',
path => '/xml_demo/*');
Now, this being done, we can pin the user ‘TEST’ to the DAD we created.
dad_name => 'xml_demo',
user => 'TEST');
I have already set my port to 8080. In 8080, the XMLDB will be listening for any incoming request. All we have to do now is to create a procedure which does the business logic and returns a XML. Here we go…
SQL> CREATE OR REPLACE PROCEDURE get_emp (p_empno IN emp.employee_id%TYPE DEFAULT NULL) IS
4 HTP.print('xml version="1.0"?>');
7 FOR cur_rec IN (SELECT employee_id empno
8 , first_name ename
9 , job_id job
10 FROM emp
11 WHERE p_empno IS NULL OR employee_id= p_empno)
15 || '<empno>' || cur_rec.empno || '</empno>'
16 || '<ename>' || DBMS_XMLGEN.convert(cur_rec.ename) || '</ename>'
17 || '<job>' || DBMS_XMLGEN.convert(cur_rec.job) || '</job>'
18 || '</emp>');
19 END LOOP;
23 WHEN OTHERS THEN
25 END get_emp;
SQL> sho err
So, we are all set. Let’s go ahead and make a call to this. Open a browser and type http://localhost:8080/xml_demo/get_emp then press enter. You will get a popup.
XML DB Login popup
Enter the username and password (or you can try this method: http://test:test@localserver:8080/xml_demo/get_emp, but the drawback with this method is you are exposing the password). This call return with the values in EMPLOYEE details in XML (look at the code, WHERE p_empno IS NULL OR employee_id= p_empno).
All employees listed as XML
You want to pass a specific EMPLOYEE_ID and see the details for him? Try passing the parameter as http://localhost:8080/xml_demo/get_emp?p_empno=100
Single employee details in XML
Now, we get details for EMPLOYEE_ID 100 alone.
So, pretty much there ends the story. Now we got a native web service call from database.
Oracle XML DB documentation.
Oracle-Base – XML-over-HTTP.