Weblog for the AMIS Technology corner
Posts tagged xmltype
Absolutely Typical – The whole story on Types and how they power PL/SQL Interoperability (UKOUG, 2011)
Dec 12th
This presentation will hopefully convince database developers that types in the Oracle Database are worth their salt – and more. With the recent improvements in 11gR2, the pieces are available to complete the puzzle of structured and modern programming with a touch of OO and more importantly to create a decoupled, reusable API that exposes services based on tables and views to clients that speak SQL, AQ, PL/SQL, Types, XML or RESTful, through SQL*Net, JDBC or HTTP.
This session shows through many demonstrations how types and collections are defined, how they are used between SQL and PL/SQL and how they can be converted to and from XML and JSON and how they drive Native WebServices as well as RESTful services based on the Embedded PL/SQL Gateway. Everyone doing PL/SQL programming will benefit immediately from this session. Every Database Developer should be aware of Types and Collections. For structured programming, for optimal SQL to PL/SQL integration and for interoperability to client application. This session introduces Types and Collections, their OO capabilities, the conversion to XML and JSON, their use in Native and RESTful WebServices and the pivotal role they can play in encapsulation and decoupling.
The slides can be reviewed here:
View more presentations from Lucas Jellema.
Resources
Download Slides plus Demoscripts here: AbsolutelyTypical_UKOUG2011_jellema.zip.
Choosing the best way for SOA Suite and Oracle Service Bus to interact with the Oracle Database
Jun 21st
In the past few weeks, I have encountered a similar discussion in various organisations. Each organisation uses either SOA Suite (11g) or Oracle Service Bus (11g) at the core of their SOA infrastructure – either for integration purposes, for workflow and process orchestration or for both. In each organisation, the role of one or more databases is crucial and interaction between the SOA environment and the database is one of the most common and therefore important functions in their enterprise IT environment.
Given the importance, it is only logical that the way(s) chosen for linking the SOA component to the database is carefully selected – and evaluated every now and again. Technology may have progressed, experience may have taught us a lesson or two, the knowledge and skills may have evolved.
Interestingly enough, the number of ways for SOA Suite and OSB to communicate with the database is quite high. This article includes an illustration that shows over 20 different interaction channels that we can choose from with a fairly wild variation of attributes, required skills, productivity and performance characteristics.
Among the choices we face is the question of the communication protocol to use for accessing the database. Roughly speaking, we can go through JDBC (via a JDBC Data Source on the WebLogic Server), via HTTP or using ‘another’ channel such as File System, FTP, Email, XMPP (Chat) etc. The communication protocol may in part determine another choice that awaits us: what will be the format and nature of the messages that we exchange: text based (XML, JSON, CSV) or strongly (Oracle-ly) typed. We know that on the one hand we have the world of XML in the SOA middleware components, and on the other side the relation world of the Oracle database – at least relational and strongly typed at the core.
So when choosing the method for having the SOA components interact with the database, we have to decide on the communication protocol and we have to determine where in the route from table to SOA component (and vv.) the conversion from Relation to XML or from XML to Relational will take place.
Considerations when making that decision include the question who has to do the work of making the XML <=> Relational conversion at design time and at run time – what is the productivity of an approach and what the performance impact. Other considerations include robustness and agility or ability to maintain and evolve the implementation. Of course the required skills factor into this decision as well as the complexity of the architecture, the design, development, test and deployment process and the nature of the interaction: read only, data manipulation, small or large documents, synchronous or asynchronous (or even fire and forget) etc.
Handling Complex XML Schemas
Sep 23rd
Currently sitting in at Sam Iducula, Consulting member of the tech. staff and Mark Drake, Sr. Product Manager for Oracle XML DB. Before getting into the more in-depth topics Sam explained XML schema usage, for validation via XML schema validators like for example XML Spy or JDeveloper. This is currently really needed because those more used XML Schema like the really big ones out there like H7, etc, are nowadays so very very big that a good XML Schema validator is really needed. XML Schema in binary XML format is stored in a post parsed binary format. This has the advantage that Oracle knows about the format when storing the XML document. Extra information can be shared by the database by registering the XML Schema in the database that validates the Binary XML content.
There can be a lot of recursive dependencies, via the import or include references in a XML Schema, which make it even more difficult to make optimal use of this information. For example in the H7 setup this includes over 100 included XML Schemas. Oracle 11gR2 has been greatly improved performance and handling of those very huge metadata information as stored in such XML Schemas. Via streaming schema validation and adding hints via xdb:annotations, this provides the database with even more information on how to optimal handle these structures and as such performance can be improved even more. Some of those hints could be used to avoid the creation of objects, in this case while using XMLType Object Relational storage via, for instance, xdb:defaultTable=”" (providing an empty string) or store parts of the XML document information out of line. By the way for this last example you should use JDeveloper because it will annotate the XML Schema incorrectly (the bug has been reported by me). One of the improvements in 11.2.0.2.0 is huge improvements were made in cycle detection recognition, so they are handled even better in the mentioned version.
On the XMLDB home page on the Oracle OTN website a package of tools provided (“Oracle XML DB Ease of Use Tools for Structured Storage“) which can make your life easier regarding those xdb:annotation’s especially for those enormous big XML Schemas. This toolset which enable you to automate a lot of hints in XML Schema optimization you would like to make. Via XQuery or other XML DB update statements you are also able to override the by the database generated naming or storage options. Via some simple anonymous PL/SQL blocks this can be very easily done via for example, DBMS_ANNOTATE-x packages contained in this XML toolset, as said which is freely available on the XML DB OTN Oracle website.
This toolset comes with a white paper that shows and demonstrates some of the best XML handling ideas and experience gathered trough a lot of years handling customer use cases the Oracle XML DB Development team had. For example if you know it’s not applicable to your XML document you are able to switch off or alter DOM validation handling while storing or handling your XML document in the database. You can override ordering for example if it is applicable for your XML Schema, this avoids oracle checking it, which improves handling, but, be very aware, it can also be dangerous doing this if it was implemented by the person who created the XML Schema, but just didn’t care about the real life implement and/or it’s importance regarding being a actual mandatory requirement in practice.
Oracle SQL and PL/SQL: juggling with user defined types (ADT) and XMLType – for creating an ADT based XML Service API
Sep 4th
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.
