HOWTO: Create a Native Database Web Service

I noticed that when I wanted to do some tests with the 11g Native Database Web Service (again), NDWS for short, that I had to gather the information from all over the place on my site. Which was not so very handy. So here a short intro how to set it up, configure and deploy it, also based on the OTN XMLDB Forum example from Mark Drake.

Overview

In short you will have to do the following steps:

  1. Install Oracle XMLDB
  2. Enable the Protocol Server for HTTP access
  3. Enable the orawsv entry points in xdbconfig.xml
  4. Create an example to test the NDWS service
  5. Test the NDWS service by calling the WSDL entry point
  6. Troubleshooting

Sounds very complex, doesn’t it? But be assured, it isn’t at all. As pointed out, most of it is described in posts on this site.


XMLDB install

The NDWS functionality works only on 11gR1 and onwards, so be aware.

--
-- set ORACLE_SID
--
-- remove XMLDB via
-- @?/rdbms/admin/catnoqm.sql
--
 
conn / AS sysdba
 
-- alter session set NLS_LENGTH_SEMANTICS ='BYTE';
 
-- create dedicated tablespace
 
CREATE TABLESPACE "XDB" DATAFILE
'/oracle/oradata/$ORACLE_SID/xmldb01.dbf' SIZE 250M
AUTOEXTEND ON NEXT 10485760 MAXSIZE 2048M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL;
 
spool xdb_install.lst
 
-- create functionality and repository
 
@?/rdbms/admin/catqm.SQL oracle XDB TEMP
 
-- recompile all invalid objects
 
@?/rdbms/admin/utlrp
 
spool off

If you need to destroy, deinstall your XMLDB environment you can use nocatqm.sql, residing in the same directory.

Enable the Protocol Server

You can enable the Protocol Server via the following way. The set dispatchers statement is default already done / shared server is default enabled via dbca installations. My believe is that this is a serious security, performance problem and if you don’t use it for Shared Server (aka Multi Threaded Server) purposes or like demonstrated here for XMLDB functionality, then you should disable it.

--
-- reset statement
-- ALTER SYSTEM RESET dispatchers scope=spfile sid='*';
--
 
ALTER system SET dispatchers='(PROTOCOL=TCP)(SERVICE=$ORACLE_SIDXDB)'
comment='Dispatcher enabled for XMLDB Protocol Server' scope = BOTH;
 
-- in case we go heavy
 
ALTER system SET shared_servers=5 scope=BOTH;
 
-- instantiate all changes against the listener
 
ALTER system register;
 
host lsnrctl STATUS
 
-- set HTTP, FTP, WebDav ports
 
SELECT dbms_xdb.getftpport() FROM dual;
SELECT dbms_xdb.gethttpport() FROM dual;
 
EXEC dbms_xdb.setHttpPort(8080);
EXEC dbms_xdb.setFtpPort(2100);
 
SELECT dbms_xdb.getftpport() FROM dual;
SELECT dbms_xdb.gethttpport() FROM dual;
 
-- Double check
 
host lsnrctl STATUS
 
SET head off
 
-- Valid?
 
SELECT * FROM dba_registry WHERE comp_id='XDB';
 
SET head ON
 
-- final test via browser:
 
http://localhost:8080/
ftp://localhost:2100/
.

ORAWSV Entry Service

By default, the Native Database Web Service is not enabled and you will have to create some extra entries in the xdbconfig.xml configuration file that handles the Protocol Server behavior. As mentioned in the documentation of the Oracle XMLDB Developers Guide, to enable the NDWS service you will have to do the following:

DECLARE
  SERVLET_NAME VARCHAR2(32) := 'orawsv';
BEGIN
  DBMS_XDB.deleteServletMapping(SERVLET_NAME);
  DBMS_XDB.deleteServlet(SERVLET_NAME);
  DBMS_XDB.addServlet(NAME => SERVLET_NAME,
                              LANGUAGE => 'C',
                              DISPNAME => 'Oracle Query Web Service',
                              DESCRIPT => 'Servlet for issuing queries as a Web Service',
                              SCHEMA => 'XDB');
  DBMS_XDB.addServletSecRole(SERVNAME => SERVLET_NAME,
                             ROLENAME => 'XDB_WEBSERVICES',
                             ROLELINK => 'XDB_WEBSERVICES');
  DBMS_XDB.addServletMapping(PATTERN => '/orawsv/*',
                             NAME => SERVLET_NAME);
END;
/

This will update the xdbconfig.xml file. You can check the new entries via your browser (HTTP).

To use the WSDL service grant the role XDB_WEBSERVICES to the schema that needs it. This role enables use of Web services over HTTPS; it is required to be able to use Web services.

The service can be used via http://host:port/orawsv and http://host:port/orawsv?wsdl
To quickly register this against the listener use the statement:

SQL> ALTER system register;
 
System altered.

Two extra roles are available:

  • XDB_WEBSERVICES_OVER_HTTP – Enable use of Web services over HTTP (not
    just HTTPS).
  • XDB_WEBSERVICES_WITH_PUBLIC – Enable access, using Web services, to
    database objects that are accessible to PUBLIC.

Be aware that access can be restricted via the DBMS_XDB.SETLISTENERLOCALACCESS method, so when in trouble, keep this in mind aswel…

WSDL integration with OWSM (Jinyu Wang (r), Oracle - Oracle Open World 2006)
Click picture to enlarge

As the picture shows, NDWS can be combined with the Oracle Web Service Manager which is part of the SOA Suite. This enables extra control, reporting and securing this NDWS functionality.

Create an Example

Mark Drake demonstrated a great (and simple) example on the XMLDB OTN Forum to demonstrate the power of this NDWS functionality and that can be used to check if everything works as expected.

CONNECT sys/ AS sysdba
--
def USERNAME=SCOTT
--
def PASSWORD=tiger
--
BEGIN
  dbms_network_acl_admin.create_acl('localhost.xml', 'ACL for 127.0.0.1', '&USERNAME', TRUE, 'connect');
  dbms_network_acl_admin.assign_acl('localhost.xml', '127.0.0.1');
END;
/
COMMIT
/
GRANT XDB_WEBSERVICES TO &USERNAME
/
GRANT XDB_WEBSERVICES_OVER_HTTP TO &USERNAME
/
GRANT XDB_WEBSERVICES_WITH_PUBLIC TO &USERNAME
/
CONNECT &USERNAME/&PASSWORD
--
var url varchar2(700)
--
SET long 10000
--
BEGIN
  :url :=   'http://&USERNAME:&PASSWORD@127.0.0.1:' || dbms_xdb.getHttpPort() || '/orawsv?wsdl';
END;
/
print url
--
SET long 10000 pages 0
--
SELECT  httpuritype( :url ).getXML() FROM dual
/
CREATE OR REPLACE PROCEDURE SQUARE ( parm IN OUT NUMBER)
AS
BEGIN
  parm := parm * 2;
END;
/
BEGIN
  :url :=   'http://&USERNAME:&PASSWORD@127.0.0.1:' || dbms_xdb.getHttpPort() || '/orawsv/SCOTT/SQUARE?wsdl';
END;
/
print url
--
SET long 10000 pages 0
--
SELECT  httpuritype( :url ).getXML() FROM dual
/

If you check it out via XMLSpy or JDeveloper than you can present the WSDL service as shown in the following picture.

XMLSpy presentation of one of the examples given on OTN by Mark Drake

Click picture to enlarge

Troubleshooting

There is a chance that you run into a ORA-24247 error. This is because Oracle 11g Security has tightened. If you experiencing this then have a look at the DBMS_NETWORK_ACL_ADMIN package. Have a look in the Oracle manuals or a detailed follow up in this blog post: “Oracle 11g Security – XMLDB is here to stay…

That’s basically it. Create your own, probably more interesting, procedure which you now can publish via Oracle Native Database Web Services.

HTH.

Marco

Marco Gralike Written by:

9 Comments

  1. Zdenek Hrib
    July 21

    I am desperately trying to switch the default error page of this orawsv servlet to my own error page. I got

     401 /some/local/path/to/file.ext                                                                                                
    

    in xdbconfig.xml and it is not working at all. Any suggestions ?

  2. July 21

    Something got wrong pasting your code. I think I partially could reconstruct it. Maybe an idea to post it on the OTN XMLDB Forum?

    HTML error code: 401 means “Unauthorized”

    See also: http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html

    My guess would be that the ACL’s on that resource are to restrictive for the user that is allowed to access the wsdl service…

  3. Chris
    August 11

    Is REST support for Web services — can I call XSLT on the fly?

    • Gaurang
      September 19

      Marco –

      can you give me in depth answer of below questions? Out architect are want

      Iā€™m proposing use of Database Native web service in our company. Our architects are asking following questions so appreciate your help

      a. How do you load balance across the DB WS (is there a way to use the F5 to detect downtime and balance the load across the DB servers)? We will be using RAC configuration with 3 nodes.
      b. Our architect have security concern as user id/password information are in the web service URL, which may prove to be an issue. Is there a way to mitigate this risk?
      c. Instrumentation in this approach (db native services) is questionable due to development/auto gen of the service logic from the DB itself. We may investigate wrapping the services with OSB to give us an additional instrumentation, policy enforcement, endpoint mediation and security proxy. Thoughts??
      d. Architect are curious regarding any potential performance issues this may have on the DB server.

      Thanks

  4. vipin hasija
    August 19

    Hi

    This is we tried with SOAP but how the same can be done by Restful services?
    and also I am unable to see xml of stored procedure “SQUARE” in url,till servlet its fine then why error in calling with proc.

    Incorrect Input Doc/URL </OracleErrors

    Thanks
    vipin

Comments are closed.