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.