Tag Archives: Reports

Printing directly with APEX

When looking for a print solution with APEX you will find .PDF

You will find a lot of .PDF

And .PDF is good. There is nothing wrong with .PDF. In fact, .PDF looks cool and you can do a lot of neat stuff with it. With toolkits like pl/pdf you can create .PDF’s directly from PL/SQL.

But sometimes there is the need to be able to print directly.
For instance with batch-processing or with nightly print-runs or whatever. And this is where you would find yourself locked out with .PDF and, glancing Google, you would guess you’d be out of luck!
Since we had:

  • created a web based solution
  • the need to print directly
  • print in nightly-runs

plus we had:

  • about 400 reports (.rdf files) which we need to reuse (without having the opportunity to rebuild them in something like pl/pdf)
  • combine different output / distribution mechanisms

we needed to tackle this challenge!

So we did !!

It was fixed by using some old and new technology mixed together:

Oracle reports builder
Oracle Fusion Middleware, more specifically, Oracle Reports Server, aka WLS_Reports

By using this combination of products, you can create a printing solutions which is capable of printing directly to your network printer, create HTML or PDF reports.
Schedule them, e-mail them, and all this by URL-control!


Use the following (much used, but far from a complete list of) control-commands:

  • report=<name of your .rdf>
  • userid=<userid/password@database>
  • desformat=HTML/PDF
  • destype=type of output of the report
  • desname=name of your output (device, file, whatever)

More commands in the link to the documentation on the bottom of this post!!


  • You can post these parameters to the Reports Server without calling them in the original URL!
  • You can set a “local” on your Reports Server for omitting <@database> in ‘userid’ for your default database
  • Actually you can set all environment variables, like TNS_ADMIN, NLS_LANG, REPORTS_PATH, etc.

What we found is we needed to run Oracle Reports Server on Windows, just to take advantage of the Windows Printing System which is quite stable and easy to configure. (So, yes, okay, there you have it, a good thing about Windoze!)

Basically you can create a simple solution, but you can easily expand it quite a bit, making a printing and reporting solutions worthy of and enterprise environment, with distributing reports via e-mail, creating reports in file-systems, embedding reports in websites, and basically anything you want or would need.

And, you get a nice Management Console for free with this installation!

Oracle Enterprise Manager Console

From this management console you can administer your print-jobs, set all kinds of parameters, which is quite neat!!

But, wait… the catch… It’s gonna cost you!

Or, can you keep it under control?

But of course!

Printing is mostly a half-on-line thing, and for a lot of stuff, it’s not extremely performance / time critical… So what can we do?

Oracle Reports Server is licensed as “Oracle Forms & Reports Server” and it will set you back € 370 per Named User or € 18.200 per CPU (being Oracle CPU’s according to the Core Factor Table!)
It’s still a whole lot of money, but would you really need more than 2 cores? If you give the machine enough memory and fast disks? Probably not.

Is it worth considering taking another node in your environment? Perhaps. This print-solutions could be a viable reason to do so. It brings you quite a bit of functionality straight from the box. But, as always, do your math and make educated choices.

The documentation link promised:

If you would like more info, please just drop me a line!

Idempotent, a parameter with adverse effect

To create and distribute, since long, we have had the power of Oracle Reports at our fingertips. These same report definitions can still be used successfully nowadays.

With all the technical changes over the years, we’ve come to a point where Oracle Reports Server, is now part of Oracle Fusion Middleware proposition. It is a (small) container, application or component of this software stack, where it is loaded in an application server and thus deployed.
Although this takes Oracle Reports Server into the modern age, it adds a lot of complexity for smaller implementations, a lot of tweaking and tuning to get it all running smoothly. One of these examples I would like to address here…

We were faced with a situation where we were running a report, an e-mail distribution report, sending out specific information to a multitude of e-mail addresses. And there was this situation where this report was run on a system, which was somewhat overloaded. This is not good, but not always completely predictable or avoidable.
For this report to run very long, actually was not a big issue as these individual e-mails were just informational and not time-bound on a minute-scale.

The problems started when we were getting word that some recipients of these e-mails were getting multitudes of copies of these e-mails! Which was sloppy at best…
Wait… multitudes of e-mail from a job which is running slow… That’s odd, they should be getting either a late e-mail or perhaps no e-mail at all! Not a lot of e-mails, that is contradictory!

This called for an investigation.

Finally we stumbled across a technology designed to do no harm, I would like to call your attention to idempotence!
Funny thing is, this setting is not part of Oracle Reports Server, but of another component used in this complexity, Oracle HTTP Server (OHS).

  • When set to ON and if the servers do not respond within WLIOTimeoutSecs (new name for HungServerRecoverSecs), the plug-ins fail over.
  • As stated on WikiPedia, an idempotent operation is “that can be applied multiple times without changing the result beyond the initial application

Obviously, this was not the case with our situation! This idempotent operation was applied multiple times and it did change the result far beyond the initial application. Up to 40 e-mails per recipients on one specific occasion.

In effect, what happened, was that the Oracle Report, which was called through a URL, did not complete within WLIOTimeoutSecs, was just restarted. The URL, as it was called to start the process, was called again from withing the Oracle Fusion Middleware Stack, starting a new run of e-mail distribution… over and over again.

We resolved the situation by two completely different actions.

  1. we made sure this operation was not run on a system which was too busy to handle the load
  2. we made sure Oracle Fusion Middleware wouldn’t get the crazy idea to re-run this operation again.

Number two was fixed by the adding of the following instruction to the installation guide (and actually doing this too):

Toggle parameter IdemPotent to ‘off’ in reports_ohs.conf in:


This can be accomplished through the Enterprise Manager

Webtier -> Ohs1 -> Oracle http Server -> Administration -> Advanced Configuration -> Choose a file -> reports_ohs.conf

Add the following line to the body:
## Added <name> <date>
IdemPotent off

Our problems are gone now!