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.

Rights, Hints, and Bulk Collect

I am back to recounting what I learned at my hand on instructor led Oracle training. The first topic deals with who is authorized to execute database objects like functions or procedures. The default method is called definer’s rights. This means that the rights of the user who created (compiled) the function or procedure is used when determining what the code can do. This authorization is checked at the function and procedure level.

On a totally different topic, you can provide Oracle with a NOCOPY hint when specifying parameters to a function or procedure. This is truly a hint that Oracle may end up ignoring. This hint implies that the developer recommends the parameters be passed by reference. The benefit is a speed increase especially when the parameters are large. However there is a danger when using this. If your program blows up in the procedure or function, the state of your data may be undefined.

Finally I want to briefly mention the BULK COLLECT mechanism. I have seen top notch Oracle developers use this feature. It has always mystified me. Perhaps it has something to do with how the words BULK COLLECT sound. Anyway this technique is used when exchanging data between the database and a collection.

Normally you would iterate through a collection one record at a time and exchange the data with the database. This however is a slow technique as you are switching context between the PL/SQL and SQL governors. The BULK COLLECT let’s you do all the work at once. It is good when you are dealing with a lot of data (a big table). It is also easier to code than to manually iterate through the records yourself.

Dynamic SQL and Such

On the third day of my instructor led Oracle training, we seemed to cover a number of points. We also touched upon the techniques for performing dynamic SQL. So this post may be a bit erratic. I just thought I would get some more info out there for you.

Database Control Language (DCL) is the way to control permissions to database objects. These commands begin with GRANT or REVOKE. On a different topic, you must provide bind variables to an EXECUTE IMMEDIATE statement. This allows you to supply positional parameters to the SQL. The name of the bind variables is arbitrary. It is the order that determines which variables are matched with the placeholders in the SQL.

There are two main types of cursors. These are strong and weak cursors. A strong cursor will return data in a record type. The weak cursor will just return unstructured data. There are two major techniques to executing dynamically formed SQL statements. The older technique is to use the DBMS_SQL package. This method has been around a long time. However it is much slower than Native Dynamic SQL (NDS). Note that the OPEN_CURSOR command in DBMS_SQL does not actually open the cursor. It just creates it.

Finally I got a peek at the latest Enterprise Manager for the Oracle database. Since I am a programmer and not a DBA, I do not use the Enterprise Manager. This product is now web based. It can be used to assist with SQL tuning. Next time I will probably write about invoker’s and definer’s rights. Until then be well.

SQL Execution

Part of the reason to attend in person training is to pick up the secrets that you cannot read in a book. I got plenty of that at my last Oracle hands on training class. In addition to that, I got some pieces of information that were beyond the scope of the class. However they were nevertheless very interesting and potentially useful. Today I want to discuss a sample of one topic that I have learned. That is what Oracle does when it is working towards executing a SQL statement.

The SQL execution portion is separate from the PL/SQL processor in the database. This SQL execution portion is governed by a common subsystem which handles all SQL requests, whether they come from the result of PL/.SQL or a user typing SQL at the SQL*Plus command prompt.

Here is the order of things that happen when Oracle prepares to run a SQL statement. First there are syntax checks. Then there are semantic checks. Third there are checks whether all the permissions are in place for the objects being queried. Then the cost based optimizer (CBO) is consulted on how to best approach the query for performance purposes. The CBO step has a number of sub-steps. Finally the row selector identifies the results.

There are many parts to the CBO step. Indexes are analyzed to determine an access path to the data. Then the join order is evaluated if more than one table is involved. Then the plan costs are counted and evaluated. Obviously Oracle is going to want to choose a plan that has the least costs.

My instructor for PL/SQL programming told me there was a whole separate class for performance tuning of Oracle. In that class he would delve deeper into the SQL execution process. A coworker of mine is encouraging me to attend this class and get tested on it. However I think my heart is with PL/SQL development. Therefore my next class will be an advanced PL/SQL development class. However it is nice to know that there is a whole world of information to learn about within the Oracle universe.

Built In Packages

Oracle has quite a few built in packages available for PL/SQL developers to use. Some packages are optional for the database and must be manually installed. You can find information on the built in packages at Tahiti dot Oracle dot com. Look under Application Development, then SQL and PL/SQL, and finally PL/SQL Packages and Types Reference.

If you are not planning any commercial uses for it, Oracle allows you to download, install, and use any version of their databases. The Express Edition of Oracle is a good choice for basic Oracle database needs. It was created in response to the open source databases available now. It competes with MySQL for example. It has a 3TB maximum size limit. And it is completely free for any type of use.

Normally the exceptions raised by a package are defined by it. However generic errors sometimes cause a package to raise an exception that it does not define. One sample package is UTL_FILE. As you can imagine, it is used for file input/output. Note that you will probably not want to use this package to output reports. There is other software specialized for that purpose.

Another package available is UTL_MAIL. It works with an SMTP server. The package itself does not actually send email. The SMTP server does that. Note that this package is not installed by default during an Oracle install. You must manually install and configure it to work with your SMTP server.