java - Access Web service from Oracle stored procedure

Question

Is there anybody who has successfully accessed a Web service from an Oracle stored procedure? If so, was it a Java stored procedure? A PL/SQL stored procedure?

Is there any reason why I should not be trying to access a WS from a stored proc?

Here are a couple refs that I found so far

• Database Web Services
• Calling external Web Service from a Java Stored Procedure

..Just to clarify, this is for SOAP calls

Answer 1

First off, what sort of web service are you calling? I am assuming either SOAP or REST.

For REST web services, UTL_HTTP is often more than sufficient, combined with a bit of XPath in a simple PL/SQL stored procedure.

For SOAP web services, it depends on how sophisticated you need (or want) to be. You can certainly use XQuery to create an XML document that meets the spec for the web service, use UTL_HTTP to post the document and get the response, and then use some XPath to parse the response all in PL/SQL. This is a relatively manual and relatively brute-force solution, but if you are talking about a handful of web services, it involves a minimum of infrastructure and the calls can get knocked together pretty quickly.

If you expect the calls to evolve over time or you expect there to be a number of procedures calling a number of web services, it probably makes sense to invest time in something like UTL_DBWS (this isn't something, though, that you generally get working in a couple hours).