Simplest way to debug your Oracle Java Stored Procedure

Oracle 9i/10g/11g

Today I came across a situation where I had to debug a Java Stored Procedure. I was under time pressure and just needed to understand where the procedure was failing. 

The simplest way of debugging pretty much any code is the good old print to standard output. And luckily for us, Oracle thought the same way and provided a super simple method of reading from the standard output stream by redirecting the output.

So my solution was easily implemented using the good old System.out.println, placing the print calls strategically throughout the Java procedure. The last little trick that allows you to actually see what was written, is to enable the output in SQL*Plus by turning the server output on and setting the DBMS_JAVA output size.

Base64 decode using PL/SQL

Oracle 10g

For those of us that deal with internet based communication, Base64 encode/decode is a must. It is a method for us to move binary data files over a text based communication channel.

In my environment, I receive files over HTTP via the MOD_PLSQL gateway. MOD_PLSQL implements the bare necessities in terms of HTTP communication, so it comes as no surprise that it does not decode uploaded files if those files arrive as GZIP or Base64 encoded. If I think about it, it shouldn't decode them automatically, but at the very least it should record the encoding of the file for later use.

Fortunately, I just happen to know that the 3rd party sends the file encoded using Base64. Only thing is, the PL/SQL package for decoding Base64 strings UTL_ENCODE only decodes RAW datatypes which limits us to 32767 characters. For Base64 strings larger than 32767 characters in size, we needed to role our own solution. So here is my version of decode to support decoding clob to blob.

Solved "Setup was unable to open information file games.inf"

Windows XP

Windows XP was actually a great OS, and I still use it extensively in testing on virtual machines. Of course, for my test environment, it did not make sense to install all of the components of XP, so I made sure I only installed the lightest components that I thought I needed. 

It was only later that I discovered that I could not add components back in because the Add/Remove Programs dialog would error with "Setup was unable to open information file games.inf". Fortunately, this problem is not that difficult to solve. 

Example: How to print a Oracle report using a URL

Oracle Reports 10g

Windows Server 2008

For those of us that use Oracle Reports in our environments, most of us (I assume), use the web browser to run the report. The report is then returned back to the browser window for viewing (typically). But what happens if I want to run the report directly to a printer without viewing it? 

Perhaps I want to have a payroll run that prints a few thousand pay sheets. Not something I want to preview or download to my browser. It's also not something I would print to my local printer that only does 15-20ppm.

Or, maybe I want a document that requires particular stationary, delicate watermarking and copy protection only available on a specialised corporate printer. Like perhaps a vehicle license document or access card printer.

Anyway, there is quite a bit of documentation about printing from Oracle Reports on UNIX servers (because it's so much more complex on UNIX systems), but basically none on Windows servers because it is so simple. Still, it took me a little while to find just the right parameters to set when running the report. So here it is then, a simple guide to printing Oracle Reports to a designated printer via a URL browser command....

Extending the simple MS Excel procedure to include headers

Oracle 10g

MS Excel

In my previous post titled "Simplest example of writing to MS Excel using an Oracle Stored Procedure", I demonstrated what I believe to be the simplest way of outputting a file that can be used by MS Excel. But the example does not include the column headings.... Okay, thats simple enough. In this post, I will extend on the previous example to include the headings.  So without further ado....

Simplest example of writing to MS Excel using an Oracle Stored Procedure

Oracle 10g
MS Excel

First of all, we need to define exactly what is meant by "writing to Excel worksheet". Most people just mean produce something that MS Excel can show as a worksheet. A CSV file is the simplest file that can be displayed by MS Excel and since it is a simple text markup, is the easiest to produce via PL/SQL.

Generating the file using a stored procedure is seriously simple. But you will need to also give thought to how you will make the file available to excel (how to distribute the file). In my example, I make the file available on a network share. The user will find the file available there and open it using MS Excel.

Solved problem with fonts in Oracle Reports 10g on Windows 2008 Server

Oracle Reports 10g

I have seen countless posts of people battling with fonts in Oracle Reports. I battled my Reports Server on Windows Server 2008 for two days trying to get the font to display. In the end, it turned out to be relatively simple, but not obvious. 

The trick with Oracle Reports fonts is to make sure that you have got the font names right in the font aliasing configuration file (uifont.ali). If you don't get the font names right, then the alias will not be used and your report will not use the correct font. Get the font names correct and the report will show as expected.

Javascript not running in IE8

IE8

I encountered a really weird scenario today involving Internet Explorer 8 and javascript. On one particular users computer the browser would not execute any javascript. Even after making sure that javascript was enabled by every other means, the javascript just would not run.

While instpecting all the different IE settings I could find that I thought could influence script execution, I did notice that McAfee had installed scriptproxy as a browser extension. I assumed that this was the most likely culprit causing the unusual application behaviour. I tried disabling the extension, but of course it did not change the situation. 

The reason is that the scriptproxy works by tricking other COM based applications into thinking that it is the jscript or vbscript engine. It does this by changing the registry entries of the jscript and vbscript components so that they actually point back at scriptproxy. So even though I disabled the browser extension, the browser would still be trying to instanciate an instance of scriptproxy and not the jscript component.

Thankfully the system already had Microsoft anti-virus installed and I was given the go-ahead to remove the McAfee component from the computer. 

Unfortunately the McAfee uninstall does not put things back exactly where they were and I was forced to re-register the jscript component manually

regsvr32 jscript.dll

did the trick.

AWR and Oracle Licensing

Oracle 10g, 11g

When 10g came along, we thought we were getting some great new performance monitoring and tuning tools to help us diagnose problems and generally make sure our database was running smoothly. This seemed to come in the form of the Automatic Workload Repository (AWR). 

All sorts of articles were written about how you can use AWR to troubleshoot various performance problems that you may be having. Many forums and articles showed off how STATSPACK had been eclipsed by AWR, and countless more have written examples on how to use AWR. Testing Oracle 10g for the first time, I was really excited about AWR. 

"Look how easy it is! And it's installed and available by default!" 

Well, yes it is, but actually.... you're not allowed to use it! 

AWR Report missing diagnostic information with ORA-20023 errors

Oracle 11g Release 1 

I seem to be hitting all of the AWR report bugs lately. The latest one is that the AWR report is missing data and shows lots of errors of the form:

WARNING (-20023)
ORA-20023: Missing start and end values for time model stat: parse time elapsed

A bit of searching on Metalink reveals that this seems to be a bug (#7532789) and is discussed in document 1181573.1. The bug information seems to note Enteprise edition only, but I have found the problem on Standard edition too.

ORU-10028: line length overflow, limit of 255 chars per line

Oracle  9/10/11

I wish Oracle would update their DBMS_OUTPUT package for the modern world. 255 characters per line just does not cut it anymore. 

There are quite a number of solutions around this and all of them involve writing your own wrapper for DBMS_OUTPUT. I quite like the solution provided by Karl Reitschuster. His solution is simple enough to incorporate into anonymous PL/SQL blocks, a factor that I found most attractive over the others.

Evaluate relative paths using DOS functions (call:MakeAbsolute)

Window 7, Windows XP

MS DOS

call:MakeAbsolute

I often hear people harp on about linux shell scripting and how M$ DOS is no where near as powerful. Well that may be so, but DOS can still be really powerful in it's own right. I have found an awesome resource recentely; DosTips - The DOS Batch Guide. They have developed a set of DOS functions that make DOS a breeze and show how powerful DOS can be. They also have tutorials on writing your own DOS functions.

In my case, I needed a method of evaluating relative paths against a given absolute path. The MakeAbsolute function on the DosTips website really helped me out and is now incorporated into almost all of my DOS scripts.

Open the Windows Command prompt at the selected folder

Windows 7

I am always open to anything that will allow me to work more efficiently. Way back in Windows XP, the various sysutils would install a context menu into Windows Explorer that would allow us to open the command prompt with the selected folder set as the current directory. ContextConsole Shell Extension is another tool which will do the same for the Windows 7 environment.

However, with Windows 7, you no longer need to install additional extensions and software. Instead, the context menu option, "Open command window here", is available by holding down the shift key and right clicking on the folder.

AWR snapshot creation hangs

Oracle 10g R1

I encountered a problem with the AWR snapshot in which the creation would hang. This phenomenon is due to an Oracle bug #7284976. The Metalink bug report indicates that it is applicable to DB version 10.2.0.3 Enterprise Edition and doesn't list any other affected versions, however I seem to be encountering this on DB version 10.1.0.4 Standard Edition running on IBM AIX platform.

Great tools for identifying a font

I recently wanted to use a font I has seen used on a business card. I spent hours trawling through massive lists of fonts when it occurred to me that there are probably tools on the web that can help me find it faster.

A quick google search provided many really good resources. Two stood out immediately:

• If you are lucky enough to have an image of the text, then WhatTheFont is without a doubt the place you want to be. Simply upload an image of the font and the site will provide you with a list of closest matches.
• In my case, I had a business card on my desk and didn't feel like hassling with the scanner. So my choice was Identifont. The Identifont engine asks a number of questions about the look of the font and provides a list of matching fonts based on your response. A great feature is that it provides an updated list of fonts after each question.

Workaround for ORA-24347: Warning of a NULL column in an aggregate function

Oracle 10g R2
ORA-24347: Warning of a NULL column in anaggregate function

I encountered this error while using DBMS_XMLGEN to generate someXML output. And if you're reading this, it's most likely because you encountering the same problem.

Well, it turns out there is an Oracle bug (#5911073,consult metalink) that throws this error when an aggregate function (such as SUM, COUNT or AVG) receives a NULL input value and is used in conjunction with DBMS_XMLGEN.

The following script demonstrates the problem: