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.
In short you will have to do the following steps:
- Install Oracle XMLDB
- Enable the Protocol Server for HTTP access
- Enable the orawsv entry points in xdbconfig.xml
- Create an example to test the NDWS service
- Test the NDWS service by calling the WSDL entry point
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.
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
- 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…
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
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.