Generating XML from SQL & PL/SQL and Code Instrumentation

From time to time you’ll have to represent your data stored in a database in an XML format, eg. to exchange it between systems, to send it to external parties, etc.
In an article on All Things Oracle I give an introduction on how you can generate XML documents:


Another interesting article from my colleague Jan Leers is about Code Instrumentation.

Whenever we execute a procedure, it’s out of our hands. We expect it to do what it was designed to do, but what if it doesn’t?
What if it takes a lot more time then expected?
All kind of questions start to arise:

  • Is it almost finished, should I wait just a few more minutes?
  • Is it trapped in an infinitive loop? Or are my queries running slow?
  • Can I safely kill it? Or did it already commit some changes and should I reverse them?
  • Which job should I kill, is this mine?

Want to find out how you can answer these questions?  Read his article on All Things Oracle

All things Oracle: Generating XML from SQL

Did you ever needed your table data in an XML format?
Well, it isn’t that hard…  Oracle provides several ways to generate XML, in both SQL and PL/SQL.

I wrote an article on All Things Oracle in which I explain step by step how you can generate XML from table data.
In this first part I show you some SQL functions which are easy to use.  Check it out!

XMLTYPE column larger THAN 4000 bytes => ORA-19011

When you try to convert an xml file, which has a tag that contains for example one or more pdf files, into an XMLTYPE object, you will get the following error:
ORA-19011: CHARACTER string BUFFER too small

This error will be thrown because the content of a tag of an XMLTYPE is limited to 4000 bytes(this problem should be solved in 11g).
I have developed a solution/workaround for this.

Continue reading

A Canonical Data Model, the missing link within a Service Oriented Environment

Chris Judson has given an interesting presentation regarding a Canonical Data Model within a Service Oriented Architecture.

First he gave an example of the different aspects and problems you could be facing when defining the existing arhictecture and business flows within an organisation.

One of the aspects that’s needed to accomplish this, is getting IT and business to consolidate and collaborate with eachother to have a clear understaning of today’s architecture and the goals defined for the future.

The Canonical Data Model will define a common format to describe business entity within the enterprise wide organisation, as well for business as IT.

Take aways from this session:

  • The CDM will reduce the interface maintenance and encapsulate business logic in one central place
  • Put the CDM on the bus: you can plug in new applications to listen to existing events, without the need to define a new format for the new consumer + there’s a common understanding of the data model for as well business as it
  • Use the 80/20 rule to define a CDM: First you take all the unique identifiers combined with a super set of data which will be used by most consumers. In other words, if 80% of the consumers have the needed data within the CDM, the 20% can be delivered using the enrichment pattern, without the need to enlarge the payload of the CDM
  • Managing change is hard within such a model, because the dependencies between several applications are mostly high. To manage change, the 80/20 rule is applicable as well. When 80% of the consumers need new attributes, changes in the existing attributes, … the CDM can be changed. The other consumers can be delivered the same functionality using the enrichment pattern again.
  • For schema versioning the Format Indicator Pattern is mostly used
  • Use generic XML types for the XSD instead of DB specific types
  • Use declarative namespaces to manage data domains to have a generic enterprise wide data definition strategy in place

The presentation of Chris was very enlightning, because a lot of these tips & tricks are valuable for each design or implementation using XML type data and service enablement.

extract() function gives a ‘problem’ whith spaces and lines in XML

I was developing a dequeue function for an XML file and I wanted to check if a certain returned value from the xml file was correct.
To retrieve the value of a tag I used this=>
v_text := v_record.extract(‘/record/Fruit/text()’,’xmlns=””‘).getStringVal();

Ones I captured this record I wanted to check if the content of this tag was equal to, for example, some fruit, let’s say ‘Lemon’.
In most of the cases I received the correct result, but there were some xml files which didn’t gave me the correct result. Nevertheless I was pretty sure that the value in my xml file was ‘Lemon’.
So what happened? I did some checks and then I found out that the syntax of this specific xml file was something like this:

When extracting the content out of the Fruit tag he also took the spaces, tabs and lines with it, and that was the reason why he thought that this wasn’t a ‘Lemon’

Conclusion, always use trim for the spaces and put some replace clauses for lines and tabs round the value. So you should use something like this:
v_text := replace(replace(replace(trim(v_record.extract(‘/record/Fruit/text()’,’xmlns=””&#8216;).getStringVal()),chr(10),”),chr(13),”),chr(9),”);

Apex and Charts

In Application Express 2.2 and previous releases is it possible to build a Chart with the use of the SVG Viewer technology of Adobe. A time ago Adobe has decided to discontinue support for Adobe SVG Viewer starting from January 1, 2008. They even plan to remove Adobe SVG Viewer from the download area on January 1, 2009, and redistribution by third parties is not allowed by the license.

Consequently the end of building a SVG Chart in Apex is near. The Apex development team came out with another solution for building charts, the Flash Chart.

Flash is known for his rich animations. With the use of Flash, you will be able to have full control in the design of your chart. The developer’s creativity can be maximized. Despite the more possibilities, it will not be more difficult to create a chart in an application. The Flash Charts in Apex are set the same way SVG Charts were set. The chart definition will also consist of several query series and its attributes can easily be set in the chart attributes. When you want a more advanced chart with rich animation, you can update the source of the chart in its region.

If you can not wait until Apex 3.0, you can use the XML Charts developed by Denes Kubicek. These charts also use the Flash technology. The difference with Apex 3.0 Flash Charts is that the charts are setup with the use of database objects. Denes created a way to define charts more generically. The chart’s definition will be setup from the database. The chart settings are kept in a database table as well as the chart layout is hold as several templates in another table. With a request from the application to a procedure in the database package, the chart definition will be set and sent back to the application.

The XML charts of Denes Kubicek, have certainly their advantages. It isn’t necessary to define separate series. All the chart values will be sent by one SQL query. It is also possible to debug the chart definition. The XML that is used to setup the chart could be loaded in a predefined Oracle directory.

Because the XML charts have most of the advantages of Apex 3.0 Flash charts and because it is much more generic I recommend you to go for this method when you have to create several charts in a specific layout. It will also be easier to define one template layout that is used for more than one chart.
When you only have to create one simple chart, I recommend to not choosing for an XML chart. An Apex 3.0 Flash chart will be better in this case because it is easily built in the Apex tool and the database schema doesn’t have to be extended with three objects just for creating one chart.

For more information about XML Charts, have a look at Denes’ sample application:

The charts represented in some examples:

SVG Chart

Flash Chart

XML Chart

Apex: XLIFF Translator, generate the translated file.

The final step in our translation process is exporting the translated data back to an xml-file; i.e. the reverse process of step 1 where we translated an xml-structured file to a relational table-structure.

This time, the relational data has to be converted back to the original xml-structure (similar to the one of the imported XLIFF-file).

We need to recompose the following structure:

By using the standard SQL/XML functions available in Oracle XML DB, it is quite easy to realise this.

Don’t get frightened by the syntax. At first sight, it seems rather complex with all those nestings, brackets, …. But after a while, you’ll notice (that) it can be extremely powerful. Documentation on the syntax, can be found here.

We embedded the above query in a PL/SQL package which we use in the following Apex page:

So, now it is up to you. We made the tool available on the following URL. You first have to register (which only takes about 30 seconds) and then you can give it a try.

Any suggestions are welcome!