How PL/SQL can participate in a Service Oriented Architecture

Today was another AMIS day at ODTUG 2006. After yesterday’s three presentations, today we have done another three. Aino kicked off at 8.30 AM with his presentation on How PL/SQL packages can participate in Service Oriented Architectures. He had a pretty good turn out, with an audience of close to 40. For the location of the room and the time of day (last night was the big party), that is certainly a good crowd. Of course the combination of PL/SQL – a topic close to heart for over 80% of the conference participants – and Service Oriented Architecture – one of the hot topics that everyone feels obliged to be getting into – guarantees quite some interest.

After a rousing welcome by Ambassador Mathhieu, Aino started by introducing the concept of Services and Web Services, (briefly) discussing the types of services you may want to publish or consume, SOAP WebServices, the WSDL document and the HTTP protocol. He then reminded the audience of the various channels we have available to reach out from the database to the world at large – UTL_FILE, UTL_TCP, UTL_SMTP, UTL_HTTP, Stored Java procedures – and the ways there are to call into the database – SQL*Net, JDBC, ODBC, WEBDAV, FTP, HTTP.

Aino briefly introduced the structure of SOAP messages that are used to invoke WebServices and pass input parameters into them. He also showed the SOAP return message with the results returned by the WebService.

Consuming WebServices from PL/SQL

Using this knowledge of the various communication channels as well as the structure of SOAP messages, Aino went on to talk about consuming WebServices from within PL/SQL. He mentioned three ways of doing so: stored java wrapped in PL/SQL, utl_http and utl_dbws. He had set up an ODTUG WebService that implements various operations that for example return the number of registered conference visitors and produce the location of the conference. The demo involved creating the SOAP Request Message in PL/SQL and sending it as part of an HTTP Request using UTL_HTTP to the WebService url. The response is parsed using an XMLType and a simple XPath expression is used to extract the meaningful parts of the SOAP Response.

It was a good example of the decoupled nature of a SOA. We do not know anything about the implementation of the WebService – it could have been built in any programming language – C#, Java, COBOL or… PL/SQL. Yet we can invoke its functionality in a very generic, straightforward way. Of course if the implementation were in PL/SQL, this indirect invocation through the SOAP layer has a lot of overhead and is therefore much slower than a direct call would be.

The second approach uses UTL_DBWS instead of UTL_HTTP. It means our own PL/SQL code is at a much higher level. We do not have to build the SOAP envelope for example. Using UTL_DBWS can be a bit tricky at first. It requires an additional installation into the database for example. Yet the PL/SQL code for invoking a WebService is much neater than with using the low level UTL_HTTP approach: you set up a service, set up a call and do the invocation.

Since BPEL Processes are exposed as WebServices by the BPEL PM, we can start BPEL Processes and leverage BPEL Services from PL/SQL in the same way. So PL/SQL applications can easily participate in BPEL based Service Oriented Architectures.

Publishing WebServices from PL/SQL

With the 10g Release of the RDBMS, XMLDB has a built in webserver. This means that in order to publish PL/SQL based WebServices, we do not have to implement a Web Application that calls the PL/SQL through JDBC and publishes the WebService in a Web Server like OC4J or Apache Tomcat.

Using the dbms_epg package’s create_dad procedure we can set up a URL that is connected to a schema in our database. When that is configured, accessing the url is relayed to a procedure call. The contents of the HTP buffer – that should be set by the PL/SQL procedure that is called – is returned as the HTTP Response.

It is quite simple to write a procedure that returns a piece of XML. This procedure can be invoked from a browser or an application sending HttpRequests and the XML is returned as the response. This is all we need to implement REST WebServices (SOAP free). All we need to do to implement a REST WebService therefore:

• configure the DAD with DBMS_EPG
• implement a PL/SQL procedure that returns a piece of XML

Of course the procedure can accept specific parameters and act upon them. And if we really feel the need to do so, we could return a fullblown SOAP Messaage.

Messaging

Since Aino spent a lot of time discussion the above topics, he had to accelerate considerably in what should have been the second half of the presentation. He now delved into Messaging. His first conclusion was that in messaging we now have a real industry-wide (not counting Microsoft) standard: JMS (Java Messaging Services).

The main messaging concepts and terminology: queue, topic, producer, consumer, enqueue, dequeue, publish, subscribe, point-to-point. In Oracle RDMBS, the messaging infrastructure is called (Oracle Streams) Advanced Queuing. The main package we need: dbms_aq for sending and receiving messages. The other one: dbms_aqadm for setting up and managing the queues. Messages can contain payloads of any type, like Varchar2 and XMLType – the latter being the obvious choice for most messages. We can access Oracle AQ over the internet using the SOAP Protocol – directly sending messages into the queuing infrastructure from anywhere and any system.

The ensuing demo was very interesting. Another ODTUG specific setup where a message containing a conference registration is enqueued on the relevant queue using dbms_aq.enqueue. Aino could show three implementations of the Conference Registration Service based on the AQ Queues: one entirely in PL/SQL, one in Java and one using the Mule ESB (Enterprise Service Bus).