Wednesday, November 07, 2007

Oracle: Expose your data as XML over HTTP

Once you have enabled the HTTP protocol in the Oracle listener configuration, you can access data stored in tables as XML data through HTTP.

  1. Download and install SQL Developer.
  2. Connect as sys to your database, under Other Users, right click on HR, choose Edit User.

  3. Set a password for HR, uncheck Password expired and Account is Locked.
Now you can go to http://localhost:8889/oradb/HR/COUNTRIES to retrieve all the rows in countries table as an XML document. When asked for authentication, enter the HR login you set earlier.

You can select precisely the data you want using XPath. For instance, this will return the row for Argentina in the countries table: http://localhost:8889/oradb/HR/COUNTRIES/ROW[COUNTRY_ID='AR'].

The oradb part of the URL points to the DBUriServlet which serves what Oracle calls DBUris over HTTP. See the Oracle documentation for a full description the DBUris syntax.