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.
Posting your question on Sunday in Europe and North America means you are getting a small fraction of the normal audience in these forums. I recommend you lock this thread and ask the question tomorrow using the clock on the wall to get it open at the start of the business day in Western Europe. Also consider not asking in the XMLDB forum but rather in the SQL and PL/SQL as I don't see anything in your inquiry that relates to XMLDB.
PS: Version number is critical ... make sure you include it.
ad a) RAC: minimum use afaik 11.2.x (xdb protocol server support for RAC)
ad b) See http://www.liberidu.com/blog/2008/07/08/howto-create-a-native-database-webservice/ (with other words, use Oracle WebServise Manager, part of the SOA Suite, or the Oracle Enterprise Gateway)
ad c) See answer ad b)
ad d) Nope. That said, there shouldn't be with a proper design and decent application code.
We've recently begun using Native Web Services, intending to roll out this approach across the enterprise, but we've encountered 2 significant problems so far, which you may want to consider before proceeding ...
1. NWS returns an exception if the underlying PL/SQL returns a null value - even if the PL/SQL has completed successfully, and null is a legitimate return value (see SR 3-6201969101 - it contains simple instructions to recreate the problem)
(I raised it in this forum but there were no replies - SOAP Fault when returning null from a Native Web Service Stored Procedure
2. The sequence of values returned in the NWS response message is the opposite to the sequence declared in the auto-generated wsdl - i.e. schema validation will fail (see SR 3-6209016991 - it also contains simple instructions to replicate)
We have coded a workaround for problem 1. where we return <EMPTY> in place of null, and check for it in the client, but without proper resolution we're not prepared to use NWS elsewhere
As a workaround for problem 2. we removed schema validation, again not ideal
Both SRs were escalated 9 days ago, but are still outstanding
Incidentally our database is 220.127.116.11, but I've tested on 18.104.22.168 and both problems are still present
We also wrap the NWS services using an OSB Proxy Service, and came across the same security issue that you describe (b) - to provide the credentials required for the NWS, simply create a Service Account (containing the credentials) inside OSB and attach it to the Business Service which invokes the NWS (http://docs.oracle.com/cd/E17904_01/doc.1111/e15867/service_accounts.htm)