Consuming web services from Oracle PL/SQL

Question

Our application is interfacing with a lot of web services these days. We have our own package that someone wrote a few years back using UTL_HTTP and it generally works, but needs some hard-coding of the SOAP envelope to work with certain systems. I would like to make it more generic, but lack experience to know how many scenarios I would have to deal with. The variations are in what namespaces need to be declared and the format of the elements. We have to handle both simple calls with a few parameters and those that pass a large amount of data in an encoded string.

I know that 10g has UTL_DBWS, but there are not a huge number of use-cases on-line. Is it stable and flexible enough for general use? Documentation

Answer 1

Check out this older post. I have to agree with that post's #1 answer; it's hard to imagine a scenario where this could be a good design.

Can't you write a service, or standalone application, which would talk to a table in your database? Then you could implement whatever you want as a trigger on that table.

Answer 2

We have also used UTL_HTTP in a manner similar to what you have described. I don't have any direct experience with UTL_DBWS, so I hope you can follow up with any information/experience you can gather.

@kogus, no it's a quite good design for many applications. PL/SQL is a full-fledged programming language that has been used for many big applications.

Answer 3

I have used UTL_HTTP which is simple and works. If you face a challenge with your own package, you can probably find a solution in one of the many wrapper packages around UTL_HTTP on the net (Google "consuming web services from pl/sql", leading you to e.g. http://www.oracle-base.com/articles/9i/ConsumingWebServices9i.php)

The reason nobody is using UTL_DBWS is that it is not functional in a default installed database. You need to load a ton of Java classes into the database, but the standard instructions seem to be defective - the process spews Java errors right and left and ultimately fails. It seems very few people have been willing to take the time to track down the package dependencies in order to make this approach work.

Answer 4

I had this challenge and found and installed the 'SOAP API' package that Sten suggests on Oracle-Base. It provides some good envelope-creation functionality on top of UTL_HTTP.

However there were some limitations that pertain to your question. SOAP_API assumes all requests are simple XML- i.e. only one layer tag hierarchy.

I extended the SOAP_API package to allow the client code to arbitrarily insert an extra tag. So you can insert a sub-level such as , continue to build the request, and remember to insert a closing tag.

The namespace issue was a bear for the project- different levels of XML had different namespaces.

A nice debugging tool that I used is TCP Trace from Pocket Soap. www.pocketsoap.com/tcptrace/ You set it up like a proxy and watch the HTTP request and response objects between client and server code.

Having said all that, we really like having a SOAP client in the database- we have full access to all data and existing PLSQL code, can easily loop through cursors and call the external app via SOAP when needed. It was a lot quicker and easier than deploying a middle tier with lots of custom Java or .NET code. Good luck and let me know if you'd like to see my enhanced SOAP API code.

Answer 5

Rob, Could you please share you SOAP API enhaced code. I am writing pl/sql to make soap api calls on https services. Just trying to figure out if I should go for DBWS or ULT_HTTP is good enough to serve my purpose as I really need to call lots of methods of entities using vaious parameters.