Transaction Dependent Webservice Invocation…or how to call a webservice from a database trigger and make the call part of the transaction

One of our clients asked us to help with calling a webservice from a database trigger. This webservice would duplicate the actions done by the database in a different system, SAP in this case. It was a type of “fire-and-forget” webservice.
Oracle 10 offers UTL_DBWS to interact with webservices, but the client doesn’t have an Oracle 10 database. Instead they work with an Oracle 9.2.0.7, so UTL_DBWS could not be used. (While writing this Blog I discovered that UTL_DBWS could be used with an Oracle 9 database, the Oracle-Base website has an article on this subject, more info below.)
At first glance, this should be easy to implement. Good thing that my colleague Lucas Jellema wrote a blog on how to call WebServices from PL/SQL, this made things a little easier.

Things to consider

The client wanted the webservice to be called as Inserts, Updates or Deletes were taking place. Sounds straight forward, but what about this scenario:

• End User enters some data
• Trigger fires and webservice is called
• End User decides to roll back his entry

When the End User rolls back the entry made, the webservice is already called and cannot be revoked. The webservice call needs to be dependent on the database transaction.
As far as I am aware, the only way to make the webservice part of the database transaction is to wrap the call in a Job. Jobs submitted by DBMS_JOB won’t start to run until a
commit is issued. Jobs are removed from the queue when a rollback is given.

What about:

1. End User enters data
2. Trigger fires, Job is submitted
3. End User commits transaction
4. Job run, webservice called
5. webservice unavailable for whatever reason

Jobs will “automagically” try again, and again and again and so on, until they either succeed or break. The breaking of a Job occurs after 16 failed attempts. If this happens
something is definitely wrong and you should check this. Querying DBA_JOBS provides you the information regarding Failed and Broken jobs.

Using Jobs can cause database action to cross however, consider this:

1. User One changes data
2. Trigger fires, Job is submitted
3. User One commits transaction
4. Job One runs, webservice called
5. webservice unavailable for whatever reason
6. User Two changes data for the same record
7. Trigger fires, Job is submitted
8. User Two commits transaction
9. Job Two runs, webservice called
10. Webservice completes successfully
11. Job One runs, webservice called successfully

The second webservice call will arrive at the target before the first webservice call. The changes to the data have crossed while calling the webservice. This would mean that the data in the target system will be out of sync with the data from the source system.
The only thing I could think of, was to create a Timestamp attribute so they could replay the actions in the correct order they were processed by the source system. This still needs to be done manually, but at least now you know the order in which they should be processed.

First Trials

The first thing we did was follow the tutorial as it was described in the Blog mentioned earlier. Using JDeveloper we could generate a SOAP message based on the WSDL that given to us. How to do this is described in great detail in this blog.
We simply used this SOAP message in an anonymous PL/SQL block to check whether we were able to call it from the database. Inside the anonymous block we used this code:

declare
    soap_request varchar2(32767);
    http_req utl_http.req;
    http_resp utl_http.resp;
begin
   soap_request := 'The Soap request as it was generated by JDeveloper';
   http_req := utl_http.begin_request
               ('The location to where the SOAP enveloppe should go'
               ,'POST'
               ,'HTTP/1.1'
               );
   utl_http.set_header(http_req, 'Content-Type', 'text/xml');
   utl_http.set_header(http_req, 'Content-Length', length(soap_request));
   utl_http.set_header(http_req, 'SOAPAction', '');
   utl_http.write_text(http_req, soap_request);
   http_resp:= utl_http.get_response(http_req);
   utl_http.end_response(http_resp);
end;

Unfortunate for us, it didn’t work. Turns out we needed Authentication to call the webservice. How did we discover this? Even though it was a “fire-and-forget” webservice, there is still information available to determine whether the call was done successfully or not.
In the code above, the local variable HTTP_RESP is a record type as defined in the UTL_HTTP package:

TYPE resp IS RECORD (
   status_code    PLS_INTEGER,
   reason_phrase  VARCHAR2(256),
   http_version   VARCHAR2(64),
);

This provide the information we needed, using DBMS_OUTPUT.PUT_LINE we could see why we were unable to call the webservice successfully.

   dbms_output.put_line (http_resp.status_code);
   dbms_output.put_line (http_resp.reason_phrase);
   dbms_output.put_line (http_resp.http_version);

We were not authorized to access the webservice. This was solved quickly, after contacting the client. They provided us with a username and password for authentication. Adding this line
of code before the utl_http.set_header calls solved this problem:

   utl_http.set_authentication(http_req, 'user', 'password');

We needed to make sure that the job would fail when the call to the webservice would fail for whatever reason. This needed to be done explicitly after the webservice was invoked.

   if http_resp.status_code <> 200
   then
      raise no_data_found;
   end if;

Next Hurdle

While implementing this functionality at the client site, turns out the database parameters related to running Jobs in the database were not set. Executing this statement

alter system set JOB_QUEUE_PROCESSES=5;

solved that problem.
We were all set to go, the triggers were implemented in a flash. The trigger would generate an anonymous block similar to the one mentioned before, this would be submitted as a Job. The Job would run when a commit is issued, or it would be removed when a rollback is issued.
The first tests were promising, calling the webservice were only done when needed, no more no less.
Just when we started to rest on our laurels, the jobs started failing. At least the default retry-mechanism for DBMS_JOB worked…. but they kept failing. Something was definitely wrong.
Long story short: the maximum size for what you can submit to a Job is 4000 characters. The anonymous block that was generated was way longer than this.
The solution was quickly found; Instead of generating an anonymous block to be submitted as a Job, a call to a packaged procedure needed to be done. This packaged procedure would do the webservice call and hide the implementation of the actual call.
Why did we fall into this trap? Probably because the original WSDL file that we got was pretty small, the resulting code (the anonymous block) wasn’t that big either. What we tried to do, was too quick and too dirty.

Conclusion

At first I was intimidated by Calling a WebService from PL/SQL, apart from the blog mentioned earlier I didn’t have any “real” experience with this sort of thing.
And I have to say, there is no need to be intimidated by it at all. All in all, it is simpler than I initially thought.
Thanks to:

• Jurgen Kemmelings for his help at the client side
• Peter Ebell for our introduction to Webservices with JDeveloper
• André Crone for the introduction to Webservices

Links

http://www.oracle-base.com/articles/10g/utl_dbws10g.php If using UTL_DBWS is simpler, more intuitive than the method we used in this Blog I can’t tell. It is definitely worth investigating.
This site also contains a nice example on how to call a webservice from the database.

https://technology.amis.nl/blog/?p=358
The Blog written by my colleague Lucas Jellema which served as our starting point for calling the webservice

http://www.oracle.com/pls/db92/db92.vbook?search=utl_http
The Oracle documentation related UTL_HTTP for the Oracle 9i database