Oracle Reports 6i

I am working on a big update to our software system. This update includes two new reports. At the same time, the rest of the team is working on redoing how the application produces reports.

We currently use Oracle Reports 6i. That is some old but stable technology. The customer has said we cannot upgrade to Oracle Reports 10g as it requires a server which they do not wish to deal with right now. So we are rolling our own reports with custom code.

I had to integrate the new reports with the new changes I am making to the application. Right now they do not have the new report technology ready. So I figured I had better code in some stubs where the new reports will go. I decided to use Reports 6i for this.

To my surprise, it was very simple to use the Oracle Reports wizard to knock out some quick report prototypes. Things got a little trickier when I tried to proceed with some layout modifications. However I decided the wizard based output was good enough for stubs. Maybe it is a bad idea to abandon Oracle Reports technology.

XML DB

Today I listened to a pitch from one of our team members. The topic was the technologies available to deal with input files we will be getting in XML format. One of the recommended approaches was to use XML DB.

To tell you the truth, I knew little to nothing about XML DB. Apparently it is a part of the Oracle database. It is supposed to allow native XML processing. Oracle says it is a high performance solution. They give you different options to store the XML within the database. Some options have better performance than others.

Once the XML is stored in a repository within the database, you can get to it using languages such as Java, PL/SQL and C. You access it via the DOM API. There is also a new XMLType. You can use this type in table columns or PL/SQL variables. Currently one of our DBAs cautioned us that it might be slow to put big files of XML data in the database. We are going to do a proof of concept to see what kind of performance we will encounter.

Triggers

One of the last things I learned at my instructor led Oracle training class was trigger. Now I had worked a little with triggers before. However I learned more about them in training. A theme I got from my instructor was that derived data is bad. That means you should not use triggers to then update other tables based on data that just changed in the table with a trigger. Oooops. That’s exactly what we do with some of the triggers on my project. Then again, I never said we were not bad.

You can write either before or after triggers. After triggers are good for auditing. That is what we mostly use them for. Perhaps my project is not all that bad. You have the ability to change the new values for an update in a before trigger. We also do that on our project. However it usually feels kind of like a hack.

Here is another theme I got from my instructor. You should not use triggers to enforce integrity constraints. That is what constraints themselves are for. Oooops again. We do some integrity constraint checking within some of our triggers.

The source code for the triggers you compile into your database is stored in DBA_TRIGGERS. That sounds logical. It is best if the source for the triggers is very small. If you need to do complex work, have the trigger call another procedure that has the meat in it.

In retrospect, my company shelled out a lot of money for me to attend a week of PL/SQL training. This training alone did not prepare me for the certification test that I subsequently took. Nonetheless this training was very valuable. The material in the class was adequate. However the instructor’s knowledge was outstanding. I was able to ask a whole lot of questions. And I received excellent consultant style answers. Your mileage may vary. But I am one satisfied customer, even with the hefty price tag. I plan to attend some more training as soon as I can find some time within my busy schedule.