Skip to Content
Scenario: To call a webservice from a database, for example there is a row level trigger set on a database table, and for every insert of a new row, the trigger is supposed to be pushing changes by calling a webservice. (sounds familiar)
Solution: latest versions of all major database Oracle / SQLServer / Sybase now support the ability to consume webservices in stored procedures as a part of their new push towards Enterprise Orientated Services.

1. Sybase

Sybase Adaptive Server Anywhere has a flexible stored procedure language which, together with triggers, events, Java stored procedures, and schedules lets you add business logic right in the database. The fact that Adaptive Server Anywhere databases can be copied from machine to machine lets you deploy your logic right along with the data.

This ability to encode logic in the database has been extended to web services. You can include a call to an external web service in a stored procedure definition, so that it can be invoked by client applications. For example, when a client application calls the following stored procedure it returns a document from the example.com:

CREATE PROCEDURE MyOperation (a INTEGER, b CHAR(128) )
URL 'http://www.example.com/webservice'
TYPE 'SOAP:DOC'

Find more information on how to call webservices from sybase http://www.sybase.com/detail?id=1023454

2. Oracle 9i

Oracle9i Database Release 2 provides a number of capabilities that make calling an external Web service, from within the database.

CREATE OR REPLACE PACKAGE BODY time_service AS -- Location of Web service definition -- http://www.alethea.net/webservices/LocalTime.asmx?WSDL FUNCTION get_local_time(zipcode IN VARCHAR2) RETURN VARCHAR2 IS req demo_soap.request; resp demo_soap.response; BEGIN req := demo_soap.new_request('LocalTimeByZipCode', 'xmlns="http://www.alethea.net/webservices/"'); demo_soap.add_parameter(req, 'ZipCode', 'xsd:string', zipcode); resp := demo_soap.invoke(req, 'http://www.alethea.net/webservices/LocalTime.asmx', 'http://www.alethea.net/webservices/LocalTimeByZipCode'); RETURN demo_soap.get_return_value(resp, 'LocalTimeByZipCodeResult', 'xmlns="http://www.alethea.net/webservices/"'); END;
BEGIN
/*
* Since the Web service resides outside of the firewall, we need to set
* the proxy in the current session before invoking the service.
*/
utl_http.set_proxy('www-your-proxy', NULL);
utl_http.set_persistent_conn_support(TRUE);
END;

Find more information on how to call webservices from Oracle, http://www.oracle.com/technology/tech/webservices/htdocs/samples/dbwebservice/DBWebServices_PLSQL.html

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Elias Marin
    Hello Naveen Pandrangi,

    What about SQL Server 2005?
    Could you provide some links?

    Thanks in advance.

    (0) 
    1. Anonymous
      Hi,

      Sorry for the delayed response, i was looking into some usefull links for SQL server. I will update the weblog shortly.

      Cheers,
      naveen

      (0) 

Leave a Reply