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…

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.

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