Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Native Oracle XML DB Web Services in Oracle 11g Release 1

Web services are becoming the preferred way to expose data to client applications. Oracle 11g Database makes the conversion of existing PL/SQL code into web services easier than ever by providing Native XML DB web services. With some simple configuration, this functionality exposes PL/SQL code code as web services.

Note. Native web services also allow queries to be sent to the database via SOAP requests, with the results returned as XML in the SOAP response, but as I always recommend using PL/SQL APIs to access data, I'm not going to focus on this area.

Related articles.

Configure HTTP Access

In order to access a XML DB web service, the HTTP port number of the XML DB built in HTTP server must be configured using the DBMS_XDB package. The GETHTTPPORT function displays the current port number setting. If you've not used the XML DB HTTP server before, it will probably be set to "0" which means HTTP access is disabled. Use the SETHTTPPORT procedure to set the port number to a non-zero value. In most of their examples, Oracle use the value of "8080".

SQL> SELECT dbms_xdb.gethttpport FROM dual;

GETHTTPPORT
-----------
          0

SQL> EXEC dbms_xdb.sethttpport(8080);

PL/SQL procedure successfully completed.

SQL>

Configure the ORAWSV Servlet

Native web services are implemented using the "orawsv" servlet, which must be configured in the xdbconfig.xml file stored within XML DB itself. This can be done using the DBMS_XDB package, as shown below.

CONN / AS SYSDBA

DECLARE
  l_servlet_name VARCHAR2(32) := 'orawsv';
BEGIN
  DBMS_XDB.deleteServletMapping(l_servlet_name);

  DBMS_XDB.deleteServlet(l_servlet_name);

  DBMS_XDB.addServlet(
    name     => l_servlet_name,
    language => 'C',
    dispname => 'Oracle Query Web Service',
    descript => 'Servlet for issuing queries as a Web Service',
    schema   => 'XDB');

  DBMS_XDB.addServletSecRole(
    servname => l_servlet_name,
    rolename => 'XDB_WEBSERVICES',
    rolelink => 'XDB_WEBSERVICES');

  DBMS_XDB.addServletMapping(
    pattern => '/orawsv/*',
    name    => l_servlet_name);
END;
/

The resulting configuration is displayed using the following XQuery statement.

SET LONG 10000
XQUERY declare default element namespace "http://xmlns.oracle.com/xdb/xdbconfig.xsd"; (: :)
       (: This path is split over two lines for documentation purposes only.
          The path should actually be a single long line. :)
       for $doc in fn:doc("/xdbconfig.xml")/xdbconfig/sysconfig/protocolconfig/httpconfig/
        webappconfig/servletconfig/servlet-list/servlet[servlet-name='orawsv']
       return $doc
/

Result Sequence
--------------------------------------------------------------------------------
<servlet xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">
  <servlet-name>orawsv</servlet-name>
  <servlet-language>C</servlet-language>
  <display-name>Oracle Query Web Service</display-name>
  <description>Servlet for issuing queries as a Web Service</description>
  <servlet-schema>XDB</servlet-schema>
  <security-role-ref>
    <description/>
    <role-name>XDB_WEBSERVICES</role-name>
    <role-link>XDB_WEBSERVICES</role-link>
  </security-role-ref>
</servlet>


SQL>

User Privileges

Web service access to schema objects is controlled by three roles, which must be granted to a specific user by SYS.

The following code creates a test user and grants it the relevant privileges to use native web services over HTTP.

CREATE USER test IDENTIFIED BY test QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE, CREATE PROCEDURE TO test;

GRANT XDB_WEBSERVICES TO test;
GRANT XDB_WEBSERVICES_OVER_HTTP TO test;
-- GRANT XDB_WEBSERVICES_WITH_PUBLIC TO test;

Create Test Objects

To show that the native web services are now configured, we need to create a test object. The following code connects to the test user and builds a table and a procedure.

CONN test/test

CREATE TABLE test_tab (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

INSERT INTO test_tab (id, description) VALUES (1, 'ONE');
INSERT INTO test_tab (id, description) VALUES (2, 'TWO');
INSERT INTO test_tab (id, description) VALUES (3, 'THREE');
COMMIT;

CREATE OR REPLACE PROCEDURE get_description (
  p_id          IN  test_tab.id%TYPE,
  p_description OUT test_tab.description%TYPE) AS
BEGIN
  SELECT description
  INTO   p_description
  FROM   test_tab
  WHERE  id = p_id;
END;
/

These objects are automatically exposed as web services.

Access the Web Service

The orawsv servlet automatically generates a WSDL file, whose URL is determined using the following format.

# Format.
http://host:port/orawsv/DBSCHEMA/FN_OR_PROC?wsdl
http://host:port/orawsv/DBSCHEMA/PACKAGE/FN_OR_PROC?wsdl

# Example.
http://ol5-112.localdomain:8080/orawsv/TEST/GET_DESCRIPTION?wsdl

Notice the package and procedure/function names are in upper case.

The web services require authentication. If you use this URL in a browser you will be prompted for a username and password. In this case you would enter the schema credentials (test/test). Alternatively, you can include the authentication details in the URL.

# Format with password.
http://username:password@host:port/orawsv/DBSCHEMA/FN_OR_PROC?wsdl
http://username:password@host:port/orawsv/DBSCHEMA/PACKAGE/FN_OR_PROC?wsdl

# Example with password.
http://test:test@ol5-112.localdomain:8080/orawsv/TEST/GET_DESCRIPTION?wsdl

An example of the generated WSDL file can be seen here.

Configure Anonymous Access

Anonymous access to native web services is not possible. I questioned this on Metalink and I received the following statement from the "Global Technical Lead XML Database".

"Anonymous access to web services is not possible. Direct PL/SQL access without authentication would be a massive security hole."

Some parts of the documentation suggest setting the "<allow-repository-anonymous-access>" element to "true" in the xdbconfig.xml file and unlocking the "ANONYMOUS" account will achieve anonymous access. Other sections of the documentation (here) state the "<allow-repository-anonymous-access>" element has no affect on servlets, which certainly seems to be the case with the orawsv servlet.

Should you want to convince yourself anonymous access doesn't work, the following code creates the "<allow-repository-anonymous-access>" element if it is missing, or updates it if it is already present in the xdbconfig.xml file.

CONN / AS SYSDBA

SET SERVEROUTPUT ON
DECLARE
  l_configxml XMLTYPE;
  l_value     VARCHAR2(5) := 'true'; -- (true/false)
BEGIN
  l_configxml := DBMS_XDB.cfg_get();

  IF l_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN
    -- Add missing element.
    SELECT insertChildXML
           (
             l_configxml,
       	     '/xdbconfig/sysconfig/protocolconfig/httpconfig',
       	     'allow-repository-anonymous-access',
       	     XMLType('<allow-repository-anonymous-access xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">' ||
       	              l_value ||
       	             '</allow-repository-anonymous-access>'),
       	     'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
       	   )
    INTO   l_configxml
    FROM   dual;

    DBMS_OUTPUT.put_line('Element inserted.');
  ELSE
    -- Update existing element.
    SELECT updateXML
           (
             DBMS_XDB.cfg_get(),
             '/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access/text()',
             l_value,
             'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
           )
    INTO   l_configxml
    FROM   dual;

    DBMS_OUTPUT.put_line('Element updated.');
  END IF;

  DBMS_XDB.cfg_update(l_configxml);
  DBMS_XDB.cfg_refresh;
END;
/

Once the the "<allow-repository-anonymous-access>" element is set to "true", anonymous access to the XML DB repository, but not web services, is enabled by unlocking the anonymous database account.

CONN / AS SYSDBA

ALTER USER anonymous ACCOUNT UNLOCK;

Removing anonymous access to the XML DB repository, not web services, can be accomplished by locking the anonymous database account, or setting the "<allow-repository-anonymous-access>" element back to "false".

If you need to remove the "<allow-repository-anonymous-access>" element entirely, it can be accomplished using the following code.

CONN / AS SYSDBA

SET SERVEROUTPUT ON
DECLARE
  l_configxml XMLTYPE;
BEGIN
  l_configxml := DBMS_XDB.cfg_get();

  IF l_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') != 0 THEN
    SELECT deleteXML
           (
              l_configxml,
              '/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access',
              'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
           )
    INTO   l_configxml
    FROM   dual;

    DBMS_XDB.cfg_update(l_configxml);
    DBMS_XDB.cfg_refresh;

    DBMS_OUTPUT.put_line('Element deleted.');
  END IF;
END;
/

For more information see:

Hope this helps. Regards Tim...

Back to the Top.