Packages in the Oracle Database are a fine construct to use for creating a service API at PL/SQL level – that through JDBC or other connections into the database can quickly be exposed at other levels than just internally for PL/SQL. A service API has a number of characteristics, that typically include a structured, well documented contract – definition for the data that goes in and comes out of the service -, complex, nested message structures and the ability to communicate in terms of XML.

Using ADTs or user defined types in the Oracle RDBMS is quite often very useful. Presenting such a service-style interface from PL/SQL packages is quite easily realized using custom type definitions – easier both for the definition of the package’s "service contract", the consumer of the package (at least the Database Adapter used in the SOA Suite) and the developers implementing the service contract. Such a contract in terms of nested types quite close resembles the typicall WebService contract that used nested XML documents.

So the first layer of service API can be one in terms of user defined types. However, many potential consumers of the service do not speak ‘Oracle type lingo’. They can deal with strings and numbers and a string may contain a complex block of data when it is constructed as XML, but Oracle Types are beyond them. So we may need a second layer on top of the user defined type service API – a second layer that speaks XML.

Fortunately it is rather simple to turn the XML input to this second layer and convert it to Oracle Object speak (user defined types) and also to take the result from the innner API that is in terms of the user defined types and turn it into [an] XML [message]. The mechanisms we can use for the conversion from UDT (or ADT) to XML and vice versa are XMLType, SYS_XMLGEN and the TOOBJECT operation on XMLType.

Read the rest of this entry »