Given the task of generating a new report (or even repeating the generation of an existing report), how often do you find yourself (or others) going through a process like so?

• Identify the source file
• Create an external table
• Write a SQL script
• Run the script in a SQL*Plus session
• Format the spooled or captured output
• Dump (or format) the data into another application
• Send an email or post the file

The example used in this article will be based on your new mission: display the contents of an Apache Web server log. What does this have to do with Oracle, you may ask. Actually, this may be quite relevant if using Forms & Reports, Business Intelligence, Application Server, or anything hitting a Web site that eventually leads to a logon to a database, especially given the fact that most of Oracle’s Web server architecture is based on Apache.

Where are the users coming from, what status code is generated, what icons are requested, which forms are accessed, which restricted pages are attempted, and how busy is the site are but a few questions which can be answered by analyzing the contents of the log file. In fact, you generate a report about reports, and in this case, Oracle Reports is what we’re looking at.

I’m a DBA, what does Oracle Reports have to do with me? Maybe nothing, but chances are you are missing out on a simple means of accomplishing what’s shown in the bulleted list from above. Like many, if not all other Oracle products, you buy a number of seats (so to speak) when dealing with limited quantities. If Developer Suite is licensed out at a minimum of five seats (named users) and your company only needed two or three, then it is obvious you have a wasted or under utilized resource. In other words, there is nothing stopping you from using one of the unused licenses.

Okay, so getting access to Developer Suite, in particular Reports Builder, is easy and a done deal. How do you learn how to create reports? If you search for books on the Internet (using various combinations of Forms, Forms & Reports, Oracle Reports, and so on), it may be apparent that the book market for Developer Suite as a whole has dried up since about 2002. At first glance, while looking at Oracle’s resources, you’re faced with having to learn ADF, JDeveloper, Java, and a host of other not-very report sounding technologies. However, dig a little deeper into the Oracle Reports technology site at Oracle Technology Network and you will find two items/areas, which will make the chore of learning how to use Reports Builder much simpler. One is the published tutorial, and the other – in particular – is the lessons at Oracle By Example (OBE).

Navigating to and through the OBE tutorials is a chore in itself at times, and if you go straight to the OBE page, you won’t necessarily see what you are looking for. If you first go to the Products A-Z site, scroll down to Developer Tools, navigate to Developer Suite, and then go to Oracle Reports, you will see the Oracle by Example link for reports. The available lessons are shown below.

You may overhear reports developers discussing terms such as anchors, frames, repeating frames, before page, after page, and query source. Your eyes glaze over as they get into a heated discussion about the best way to wrap text around an image, (just like their eyes do when they hear you talk about blocks, extents and segments). Don’t panic, you’re not going to be dealing with those right away, and maybe even never, because you’re involvement with Reports Builder is going to be through its rich wizard interface. The wizards help you generate output that is good enough (the content layer – show data), and with a slight bit of extra work, you can go the extra mile to add zing (the presentation layer).

Not covered here, but relatively easy to do when compared to using Oracle Universal Installer for other products, is the installation of Developer Suite on your PC or desktop. You should (and need to) be able to run the OC4J instance (it is the same one for both forms and reports), and the only minor glitch may be that the view web layout for reports can’t find the path to your browser (or think that you are using Netscape). Edit HKEY_CURRENT_USER\Software\Oracle\Toolkit\Tkbrowser and supply the correct information.

Now, back to the Apache log file. You can identify the file as an external table and query it via a SQL statement, or you can treat the file as a Text Pluggable Data Source. The “text pluggable” part sounds good, as in; can you plug in a text file for a report? That’s exactly what takes place here. The OBE tutorial for this feature uses a couple of examples, one being an Apache log file, and the other being a customized file. Oracle Reports comes with the Text Query data source already configured to recognize an Apache log, and the steps to create a customized source are quite easy to implement.

Once you’ve made the Apache log file available (path-wise) to Reports Builder, you can create a report in less than two minutes. The sample shown in the tutorial is shown below.

It is also just as easy to generate the report to a PDF file, which can then be attached to an email or posted to a shared directory or copied to an htdocs location and made accessible via a URL. All of this is without impacting reports developers. Once you go through the tutorial, you’ll have to admit that this was pretty easy to do.

In Closing

As a DBA, it always helps to know more about Oracle than just the database side of things. Reporting on data can be just as important as storing/maintaining it, obviously neither is very useful without the other. Run through the other tutorials under Oracle Reports and it will be easy to see where these can be used within your organization. Questions about how long a nightly job has been running? No problem, the same query you would use in SQL*Plus can be dumped into Reports Builder and then the tool generates a bar or trend line chart or graph. Want to chart out disk space usage or growth? Number of sessions per hour across a day? These questions and much more are easily answered – and displayed – in a matter of moments.

Back to DBAsupport.com