Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Consuming web services from Oracle PL/SQL

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

like image 542
steevc Avatar asked Sep 01 '08 07:09

steevc


People also ask

Can we call REST API from PL SQL?

So you need to call a third party REST API from PL/SQL? You'll need to setup your database access control lists (ACLs) to allow it to make external calls first. Then you can call the endpiont using utl_http. You just need to know the URI of the API and it's parameters.

Can we call API from Oracle database?

That's it, you can call any external APIs from the Oracle database using PL/SQL.

How do you expose a PL SQL web service?

To expose a PL/SQL package as a service interface, it is mandatory to annotate the package with standard annotation. Below steps to be followed to expose a PL/SQL package as a REST interface. PROCEDURE save_employee_PRC(c_id IN VARCHAR2, name IN VARCHAR2);


3 Answers

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.

like image 172
Sten Vesterli Avatar answered Oct 17 '22 00:10

Sten Vesterli


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.

like image 33
Rob McCauley Avatar answered Oct 17 '22 02:10

Rob McCauley


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.

like image 1
Mark Harrison Avatar answered Oct 17 '22 02:10

Mark Harrison