Abstract Confusions

Complexity is not a cause of confusion. It is a result of it.

Native Webservice Inside Oracle Database

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.

BEGIN
DBMS_EPG.create_dad (
dad_name => 'xml_demo',
path     => '/xml_demo/*');
END;
/

Now, this being done, we can pin the user ‘TEST’ to the DAD we created.

BEGIN
DBMS_EPG.authorize_dad (
dad_name => 'xml_demo',
user     => 'TEST');
END;
/

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
2  BEGIN
3    OWA_UTIL.mime_header('text/xml');
4    HTP.print('xml version="1.0"?>');
5    HTP.print('<emp_list>');
6
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)
12     LOOP
13     HTP.print(
14      '<emp>'
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;
20    HTP.print('</emp_list>');
21
22  EXCEPTION
23    WHEN OTHERS THEN
24       HTP.print('<ERROR>'||SQLERRM||'</ERROR>');
25  END get_emp;
26  /
Procedure created.

SQL> sho err
No errors.
SQL>

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

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

EmployeeDetailsFullXML

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

SingleEmployeeXML

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.

Reference

Oracle XML DB documentation.
Oracle-Base – XML-over-HTTP.

8 responses to “Native Webservice Inside Oracle Database

  1. Adonis Webservice March 8, 2010 at 6:29 PM

    When you install the software, Oracle has option to create a new database, which can be created with the sample schemas.

  2. Karthikeyan M March 9, 2010 at 7:42 AM

    Nice Post…
    As per the last article the URL “http://test:test@localserver:8080/xml_demo/get_emp” is highly vulnerable for SQL Injection🙂

  3. Pingback: Call Return | AllGraphicsOnline.com

  4. Jumaa January 4, 2013 at 9:30 PM

    Thanks SREE for this helpful example

    I have one question:
    How can I do the same example using (https) instead of http?

    • SREE GURUPARAN January 5, 2013 at 2:09 PM

      You would suggest replace http with https. I have not tried, but guess it should work.

      • Jumaa January 5, 2013 at 3:33 PM

        thanks SREE for your quick respond, I tried replacing http with https and using port (443) instead of (8080) but it didn’t work and no page was displayed

        • SREE GURUPARAN January 6, 2013 at 11:11 AM

          Sorry about that. As I said, I have never tried it myself, hence I never knew for sure. So, you are on your own to set it up and test. You might need to read the XMLDB documentation and try raising this with Oracle forum. All the best!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: