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:
- 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
- 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
7 comments
1 ping
Skip to comment form ↓
Zdenek Hrib
21 July, 2008 at 17:14 (UTC 1) Link to this comment
I am desperately trying to switch the default error page of this orawsv servlet to my own error page. I got
in xdbconfig.xml and it is not working at all. Any suggestions ?
Marco Gralike
21 July, 2008 at 20:47 (UTC 1) Link to this comment
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…
Chris
11 August, 2008 at 20:16 (UTC 1) Link to this comment
Is REST support for Web services — can I call XSLT on the fly?
Marco Gralike
18 August, 2008 at 18:36 (UTC 1) Link to this comment
Also see Machelo post here (I saw that some of the answers were already given on the OTN Forum)
Is Oracle 11g REST ready?
Marco Gralike
14 July, 2009 at 21:53 (UTC 1) Link to this comment
A REST from the database solution, based on the DBMS_EPG gateway, is demonstrated in full detail here:
http://ora-00001.blogspot.com/2009/07/creating-rest-web-service-with-plsql.html
Gaurang
19 September, 2012 at 15:10 (UTC 1) Link to this comment
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
Marco Gralike
19 September, 2012 at 15:16 (UTC 1) Link to this comment
Let me guess, you asked this already on the OTN XMLDB Forum. Could you please follow up there and not here so that there is a central place for this info…?
https://forums.oracle.com/forums/thread.jspa?threadID=2440417
Part of the Puzzle: Oracle XMLDB NFS Functionality « AMIS Technology blog
27 February, 2010 at 22:13 (UTC 1) Link to this comment
[...] of “servlets” (naming probably derived from the WebDAV definitions) like HTTP, FTP, Native Database Web Services (NDWS), PL/SQL Gateways like local APEX support, security, etc., [...]