Tuesday, November 03, 2009

Is it Friday yet?

Ever had one of those weeks (and yes I know today is Tuesday) when you wish it would not end? Well this week is that week for me. I am energized and delighted that my offer to you concerning E-Learning has been so well received and today the first CDs went out in the post.

Don't know what I'm talking about? Where have you been? From now until the end of the year we're offering you the deal of the year on our Discoverer E-Learning. You can have your own personal copy of our best selling end user training for only $199.

Why wait? You might never see these prices again. Click here now to get more information.

Tuesday, October 27, 2009

Fifth year anniversary of Armstrong-Smith Consulting and we have an offer for you

Armstrong-Smith Consulting is over 5 years old
It was five years ago, way back in 2004, that Armstrong-Smith Consulting was founded and in that time we have helped over 200 customers with Discoverer training or consulting. We are proud to have attained our five year status and as a means of saying thank you to all of our customers and friends around the world, we are making you an amazing offer on our world-class E-Learning initiative.

As you know we have developed what is arguably the best Discoverer training in the world. Mind you, you don't have to take my word on this, click here to see some of the comments we have received about our training.

We curently offer standard training for both Discoverer administrators (click here for outline) and end users (click here for end user outline).

Our E-Learning initiative was introduced during 2009 and has been very successful. This initiative provides attendees with a full video recording of our courses, with 13 modules in the administration class and 17 modules in the end user class.

If you haven't sampled any of these we have two free demos of our end user training and one free demo of our administrator training available for trial on our website at these locations:
  • For the first example end user module (Understanding Discoverer) click here
  • For the second example end user module (A First Look at Viewer) click here
  • For the administration example module (Creating the Business Area) click here
Thank You Offer
Until further notice we will let you have our E-Learning courses available for the following amazing prices:
  • Administration Class - only $99.00

  • End User Class - only $199.00

  • Buy both for only $249.00
This is the offer of the year and these prices represent a saving of 1000s of dollars over our standard price list.

We know that these courses will be of particular interest to people outside of the USA, or for people on a limited budget who cannot get to one of our fantastic instructor-led sessions in beautiful Tennessee, or for someone like you who would like to get training but your company has cut back on your training budget. Whatever the reason, you cannot afford not to take part in this offer to get a personal copy of one or both of our world-class training.

What will you get?
When you buy our E-Learning training through this offer, you get everything you need to complete our training. Here is what you get:
  1. You get the 13 Administration or 17 End User interactive video modules

  2. You get a PDF copy of the full Armstrong-Smith Consulting manual

  3. You get a copy of the Armstrong-Smith Consulting database schema along with full instructions for installing onto your database

  4. You get the full Armstrong-Smith EUL complete with test workbooks
Purchase Now
So what are you waiting for? Drop me an email or click here to get more information on make a purchase. The material will be delivered to you by CD in the mail and we ship within a couple of business days of receiving your order.

Wednesday, October 21, 2009

Release and patch numbers for all Discoverer versions - October 2010

Hello everyone
From time to time I publish a release guide for Oracle Discoverer. This posting contains the latest information, as of October 26, 2010. I will endeavor to update this every couple of months with the latest information, with a complete new posting every 12 months.
Best wishes, Michael
  • Discoverer 11g - release 1 of this new version was released during summer 2009. Over the years, many people have tended to stay away from first releases in production but it does depend on the size and scope of your organization or project as well your resources and further needs. Personally, I would err on the side of caution before installing this in a production environment but rather wait either for patch set 1, which I believe is now out, or for 11g release 2 which will be in 2010. Many exciting new features will be added as part of Discoverer 11g R2, including interfacing to many of the OBI EE tools.
    Note 1: Discoverer 11g will be the first version of Discoverer to be certified with the Microsoft IE 8 browser.

    Note 2: Premier Support for Discoverer 11g is scheduled to end in June 2014, with extended support available until June 2017 followed by indefinite sustaining support. Discoverer 11g is pat of Oracle Fusion Middleware. Click here for more details
  • Discoverer 10g ( - Originally known by the code name Drake, this is the current and most up-to-date release and the one you should be on if you can. Notwithstanding the fact that this is the current version it is also an excellent piece of software. It has a much improved user interface when compared to previous versions and is packed with lots of new and exciting features, and can even be interfaced to BI Publisher (part of OBI EE).

    This version has two current release levels depending upon which patch you have applied. (installed using patch 4960210) is the version number for, while (installed using patch 5983622) is the version number for Ideally you should be using

    As for Microsoft IE certification, is certified for use only with IE6, while is certified for use with both IE6 and IE7. is the recommended version that you should be on with the most recent cumulative patch applied. As of this posting the most recent cumulative patch was CP8 (patch number 9694503) which was released during October 2010. The last cumulative patch for was CP8 (patch number 7111816 for Windows and Sun Solaris x86, path 7306816 for all other platforms). For more information regarding patch levels for 10.1.2 please click here.

    Note 1 Oracle have no plans to certify 10.1.2 with IE8. The first version of Discoverer that will be certified with IE8 is the 1st patch set for Discoverer 11g which you should expect to see during 2010. For more information regarding IE8 certification please refer to My Oracle Support (formerly MetaLink) note 843865.1

    Note 2: Discoverer is covered by Oracle's Lifetime support policy and is included as part of what is now known as Oracle Fusion Middleware. Premier Support for Discoverer 10.1.2.x is scheduled to end in December 2010, with extended support available until December 2011 followed by indefinite sustaning support. Click here for more details
  • Discoverer 10g ( - The terminal release is You can upgrade to by applying the patchset which will bring you to the code level. No ECS or ES dates have been announced for Please so not use this release as it is very buggy. You need to upgrade to with the latest cumulative patch.
  • Discoverer 10g (9.0.4) - premier support ended as on December 31, 2006, with sustaining support being available only until December 31, 2009. The recommended release is and you will need to be on this release before you can migrate to 10.1.2.

    Note 1: The upgrade from 9.0.4 to 10.1.2 was a destructive upgrade. This is because, while 9.0.4 and 10.1.2 share a common EUL prefixing system, namely EUL5x, the End User Layers are different. Therefore, if you upgrade from 9.0.4 to 10.1.2 using the same schema be warned that you will not be able to use your 9.0.4 installation after the upgrade. You therefore may want to consider installing 10.1.2 into a separate schema, using Discoverer's export import mechanism to effect the EUL upgrade.
  • Discoverer 9i (9.0.2) - Error Correction Support (ECS) ended on July 1, 2005 with Extended Assistance Support (EAS) being available until July 1, 2008. When installed from CD, the release was The terminal release was Patch 3079459, for Administrator and Desktop, which upgraded the software to, was released on August 1, 2003. Various one-off patches to support Administrator and Desktop bug fixes have been released since, with the last being on December 16, 2004.

    Patch 3079487 is the one which upgrades Plus and Viewer to It was released on August 5, 2003. Oracle continued to release one-off Plus and Viewer patches until desupport with the last one, 4104258, being released on February 18, 2005.

    You are urged to upgrade to as soon as you can.

    Note 1: The upgrade from any version of 9i to 10.1.2 is a destructive upgrade. This is because, while 9i and 10.1.2 share a common EUL prefixing system, namely EUL5x, the End User Layers are different. Therefore, if you upgrade from 9i to 10.1.2 using the same schema be warned that you will not be able to use your 9i installation after the upgrade. You therefore may want to consider installing 10.1.2 into a separate schema, using Discoverer's export import mechanism to effect the EUL upgrade.

    Note 2: So long as you are running at least, the upgrade from 9i to 9.0.4 is a non-destructive upgrade. This is because the two systems share a common EUL. Therefore, if you upgrade from 9i to 9.0.4 using the same schema you will still be able to use your 9i installation after the upgrade.

    Note 3: Discoverer 9i was never certified for use against E-Business Suite.

    Note 4: Discoverer 9.0.2 is not covered by Oracle's Lifetime Support policy.
  • Discoverer 4.x (with E-Business Suite 11i) - Error Correction Support for Discoverer 4i with the E-Business Suite 11i applications stack ended on October 31, 2006. According to Oracle "since Discoverer 10g ( is fully certified with E-Business Suite 11i, we HIGHLY recommend implementing or migrating to this release". Extended Support (ES) will continue only until October 31, 2009.

    The terminal release for use with E-Business Suite was When installed from CD, the release was 4.1.37. It is interesting to note that was issued only as a one-off patch for Administrator and Desktop as 3451630 on February 18, 2004.

    This release can be applied on top of any 4.1 release from 4.1.37. However, if applied to any release prior to 4.1.46 it will upgrade the EUL. The patch for Administration and Desktop, 3201601, was released on November 25, 2003 and did an EUL upgrade. Since these major releases, Oracle has continued releasing additional one-off patches, mainly for bug fixes, to support E-Business Suite, with the most recent being 5231872 on June 8, 2006.

    Patch 3201610, for 4i Plus and Viewer, was released on November 25, 2003 and upgraded the system to The required E-Business Suite release is which was released on February 18, 2004 and is patch number 3451636. Since then, Oracle has continued to release various one-off patches, with the last one being 3759425 which was released on February 15, 2006.

    Note: Discoverer 4.x with E-Business Suite 11i is not covered by Oracle's Lifetime Support policy. See My Oracle Support note 337576.1 for obsolescence details.
  • Discoverer 4.x (stand-alone, not as part of E-Business Suite) - desupported from June 30, 2004 with Extended Support (ES) available until June 30, 2007. You should migrate to as soon as you can. The final release for 4.x not running against E-Business Suite was See above for patch numbers.

    Note 1: The upgrade from 4i to 10g is a non-destructive upgrade. This is because the two systems do not share a common EUL prefixing system. Discoverer 4i uses EUL4x while Discoverer 10g uses EUL5x. Therefore, if you upgrade from 4i to 10g using the same schema you will still be able to use your 4i installation after the upgrade. You may want to consider installing 10g into a separate schema, using Discoverer's export import mechanism to effect the EUL upgrade.

    Note 2: Discoverer 4.x (without E-Business Suite) is not covered by Oracle's Lifetime Support policy. See My Oracle Support 231072.1 for obsolescence details.
  • Discoverer 3.x - completely desupported. The terminal release was 3.1.46 for Administrator and Desktop, and 3.3.62 for Plus and Viewer. When installed from CD, the release was 3.1.36. Patch 2229365, for Administrator and Desktop was released on January 15, 2002 and upgraded 3.1.36 to 3.1.46. Patch 2240408, for Plus and Viewer, upgraded Discoverer 3i to 3.3.62 and was released on February 25, 2002. A further patch, 2675005, released on 19th November, 2003, upgraded this to the final release of

    You should migrate to as soon as you can.

    Note 1: There is no direct migration path from 3.1 to 10.1.2. This migration must be done via either or However, because 4.1 has been long desupported, from October 31, 2006, you need to do this as soon as you can.

    Note 2: Discoverer 3.x is not covered by Oracle's Lifetime Support policy. See MetaLink note 162395.1 for obsolescence details regarding 3.1 Administrator and Desktop, or note 162402.1 for obsolescence details regarding the 3.1.x and 3.3.x Plus versions., note 162405.1 for Viewer.
  • Older Discoverer Products such as Discoverer 3.0.8 or Discoverer 2000 - these products are long desupported with EAS for Discoverer 2000 ending on 30th December 2002 (see My Oracle Support note 74821.1 for more details) and EAS for Discoverer 3.0.8 ending on 30th September 2003 (see My Oracle Support note 74129.1 for more details).

    You can find a lot of information relating to their desupport notices in MetaLink by clicking on the Certify tab at the top left, followed by clicking on the Desupport Notices link at the far right and then selecting BIT Desupport Notices from the drop-down list.
Footnote 1: Click here for the definitions of Premier Support, Extended Support and Sustaining Support. For products that were deupported before September 2005 you can find more information pertaining to their on My Oracle Support in document number 187553.1

Footnote 2: According to Oracle: A Terminal Release is the latest patch level for a release once Premier Support ends. This release contains all the latest bug fixes produced before error correction support ended. If Extended Support is available, then it is important to be on the Terminal Release version. If a new bug is encountered, one-off patches are created over the latest code-line and are not feasible to backport below the terminal release. To emphasize again, no new patches or backports are accepted by Development once a product is in the Sustaining Support phase of Lifetime Support.

Have you applied any patches to Discoverer that are different to those listed above or have you applied any of the patches listed? Did they fix the problem or not? If you have any additional information, let me know and I will gladly post it here for free to help the community.

Monday, October 12, 2009

Vista installer available for Discoverer 10g Administrator and Desktop

As many of you have already found out, if you attempt to install the base software for Discoverer Administrator or Desktop onto Vista the install will fail. I am pleased to let you know that there is now a patch available on Oracle Support (formerly MetaLink) that will allow the install to go ahead on Vista.

The patch number is 6153263 and obviously it is only available for the Microsoft Windows 32-bit platform.

After you have downloaded the patch make sure you unzip it into a folder where there are no spaces in the path. A folder like this will work fine: c:\downloads\oracle\vista

After you have unzipped the patch you should notice a new folder called Disk1 containing a setup.exe file.

With the new setup you can install onto Vista in one of the two following ways:

Install Method 1:
In command line mode (aka DOS) go into the folder containing the new setup.exe and launch it pointing to the products.xml file of the original install, like this:

setup.exe FROM_LOCATION=c:\downloads\oracle\10gTools\stage\products.xml

Note: if your path contains a space you must enclose the whole file location in double quotes, like this:

setup.exe FROM_LOCATION="c:\downloads\oracle\10g Tools\stage\products.xml"

Install Method 2:
  1. Within the Vista UI, use Explorer and navigate into the folder containing the new setup.exe
  2. Double-click setup.exe
  3. On the Specify File Locations screen, navigate to the products.xml file in the location where you unzipped the original Tools software

Saturday, October 03, 2009

Become a follower of my blog

If you would like to receive automatic notification of new postings in my blog why not become a follower?

All you have to do is scroll to the bottom of this page where you will see the Followers section. In there you can click the Follow button to become a follower. The system will take care of the rest.

Adjusting the Plus Font Size

There is a known Java bug that causes a worksheet's results to display in a different font in Plus and Viewer. This posting will explain how, for most instances anyway, to rectify this situation.

If you create a workbook in Plus and then open the same workbook in Viewer you may have noticed that the font size in Viewer is bigger than the one in Plus. This can be very annoying and what I have seen happening with many report writers reduce the font size in Plus to something which is very hard to read like size 9. However, when the same workbook is opened in Viewer this same workbook displays in what looks like size 12.

As I say, this is a known bug and there is a fix. Let me show you with example screenshots from my own system. First of all, here is the output from Plus without the fix:

And here is the same report in Viewer. Notice how the font in Plus is smaller?

Ok, now let's equalize the fonts so that Plus displays in the same size as Viewer. You need to edit the pref.txt in the Oracle home for your middle tier. You will find it at this location:

$ORACLE_HOME\Discoverer\util, which in my case was here: c:\oracle\bihome1\discoverer\util

With me so far? I will continue.

You edit this file and look for the entry called AdjustPlusFontSize (with the first letter of each word capitalized and no spaces between the wrods). The entry in most systems will look like this: AdjustPlusFontSize = "false" with the following additional instructions:

# false - Do not adjust the font size to workaround the java bug, 1 Adjust the font size

The instructions are wrong.

The setting you need to apply to fix this is "true" not 1. By the way, if you have my Oracle Discoverer 10g Handbook, take a look at page 791. It gives the correct entries of false (to not adjust the size) and true (to adjust the size). The entry needs to be in double quotes as shown above.

After you save the pref.txt you will need to apply your preferences by executing applyprefences.sh (if running on Linux or Unix) or running applyprefences.bat (if running on Windows). The applyprefences file can be found in the same folder where pref.txt resides. A sort of one stop shopping.

Having changed and applied your preferences you need to stop and restart the Discoverer middle tier. On my system, after I did this I reran the report and got the following output:

Take a close look at where the report ends when compared to the menu bar above and now go and compare this with the compare the output that I gave earlier in the posting. As you can see, the Plus font has indeed got bigger. In actual fact, it is now displaying the same as it does in Viewer. With this fix done, your report writers can now focus on Plus when creating reports rather than having to reduce the Plus font manually to accommodate Viewer.

Bottom line: the fault is not with Viewer but with Plus not displaying fonts correctly. Oracle have stated that this is due to a Java bug. By the way, the bug, if it actually is a bug and not just an unwanted feature, still resides in the latest Sun Java 1.6 so I advise you to make this change no matter what version of Java you are running.

By the way, unlike some preferences when this one is changed it affects all workbooks not just new ones. What it does is make them display in the correct font in Plus, the same as the way it displays in Viewer. You may have noticed that in Viewer the same workbook seems to take up more space. The reality is that in Viewer the fonts display in the right size whereas in Plus they appear smaller than in other Windows applications such as Word, Excel and so on. This fix makes Discoverer Plus right in tune with other applications.

Saturday, September 26, 2009

Values Based Skills and Success Traning Blog

Take a look at Our newest Blog -
We are excited to offer this Values Based Training to help you get the results in your company and your life. We will be giving frequent Webinars many of them will be free of charge and others which will be priced to fit into your training budget.

We will also be traveling around the country giving speeches and presentations to our clients and the public, so I will keep you posted on where we will be.

Our Armstrong-Smith Consulting clients will already be aquinted with our excellent technical training. Expect the same quality and dedication in all of our Values and Skills based training.

Values Based Success Skills

Armstrong-Smith Consulting is proud to announce our newest training initiative. My wife and the President of our company, Darlene Armstrong-Smith, spent a number of years delivering Success Skills training. She gave that up to support me in building our Business Intelligence initiatives. Now she is ready to launch a new division of Armstrong-Smith Consulting with training and coaching for companies and individuals in Values Based Success Skills.

As a means of launching this initiative she is giving a FREE Webinar on Tuesday September 29th. The title of the Webinar is; Do you know why you do what you do? She will be exploring the area of how paradigms are formed and give many useful tools to help you improve the way you think about things for better results. It is time to understand why you act the way you do, think the thoughts you think and get the results you get.

Darlene will open your eyes to the way you think about things and motivate you to make changes that give real results.

Title: Do you know why you do what you do?

Date: Tuesday, September 29, 2009

Time: 8:00 PM - 9:00 PM CDT

After registering (link) you will receive a confirmation email containing information about joining the Webinar.

Please share this information with anyone who you think would enjoy and benefit from this Webinar.

Thursday, September 10, 2009

Join my LinkedIn group

For everyone who knows me or my company, Armstrong-Smith Consulting, I have recently started a new LinkedIn group.

Whether you're a client, an OTN forum follower or just interesting in anything to do with business intelligence or Oracle Discoverer come and join my group (link)

Wednesday, September 02, 2009

Supported Java Versions

From time to time I get asked what versions of Java are supported for Discoverer Plus. You can find this information on MetaLink in note number 465234.1

To help you, as of 16-DEC-2008 the following Java Virtual Machines are supported:
  • Oracle JInitiator and higher
  • Sun Java Plug-in 1.4.2_06 and higher
  • Sun Java Plug-in 1.5.0_2 and higher
  • Sun Java Plug-in 1.6.0_4 and higher
  • Apple JVM 1.4.2_05 or higher is needed when using Safari on an Apple Mac OS X

Please note that some people experience a slowing down when they upgrade to Sun Java 1.6. I have a posting (link) about this and Oracle has a document on MetaLink (747189.1)

Slow performance when using Sun Java 1.6

Over the past year of so, several of my clients have reported a slowing down of Discoverer Plus 10.1.2 (, and following an upgrade of the Sun Java to version 1.6.0_04 or higher.

This has been recognized by Sun and if you are technically minded you can read more about this bug by clicking here.

From a Discoverer point of view there does not appear to be an issue with these Java versions:
  • Sun Java Plug-in 1.4.2_06 and higher
  • Sun Java Plug-in 1.5.0_2 and higher

To overcome the issue with Sun Java 1.6 please look at note 747189.1 on Oracle MetaLink.

There is a lot of information in this note but of interest there really are only two ways to overcome the problem and get Discoverer Plus working fast again. These are:

  1. Ensure the external facing IP address of the Discoverer server has a reverse DNS entry in the DNS server, or
  2. Add an entry for the Discoverer server to the local HOSTS file

Ever since I've been using Discoverer I've always tended to have an entry for the Discoverer server in my HOSTS file. So for me I rarely see this issue. On your local PC you will find the HOSTS file here:

  • c:\windows\system32\drivers\etc

You can edit it with either Notepad or Wordpad

Maximize your Investment in Discoverer paper

Earlier this year, at the ODTUG 2009 Kaleidoscope conference in Monterey, California, I presented a paper called Maximize your investment in Discoverer.

I have taken the white paper and the PowerPoint presentation and revamped them a little and now have them available for download free of charge from my website (link). They are both contained in a zip file called MazimizeInvestment.zip

Please feel free to download this paper and take a look at the Armstrong-Smith Consulting recommendations for how you can maximize your investment. In a nutshell, because of the introduction of Oracle Business Intelligence Enterprise Edition (OBI EE) you basically have three options:
  1. Protect your investment by staying with Discoverer - there will no forced migration away from Discoverer and Oracle will continue providing enhancements and cumulative patches plus support will be covered by Oracle's lifetime policy. Discoverer 11g is due for full release during 2010. Oracle also have a statement of direction stating that Discoverer will continue to be enhanced and supported.

  2. Extend your investment by combining Discoverer with some of the OBI EE tools. One of the notable extensions is to interface Discoverer with BI Publisher

  3. Migrate your investment by converting your Discoverer application into OBI EE. In order to do this effectively you will need to migrate your EUL and migrate your workbooks. Oracle currently have a migration utility for the EUL and hope to have a workbook migration utility available either as part of 11g or as a separate release around the same time.

Monday, July 27, 2009

CP5 for released

Just wanted to let you know that Oracle has just released CP5 for You will find it on MetaLink as patch number 8354043. When compared to CP4, 28 bugs have been fixed.

So far this cumulative patch has been released for the following platforms:
  • Micrpsoft Windows 32-bit
  • Linux x86 (works for both 32 bit and 64 bit)
  • Sun Solaris SPARC (32-bit)
If you are upgrading to CP5 from any patch level other than CP4 then JDBC patch patch p4398431_10105_GENERIC.zip for bug 4398431(release needs to be installed before you apply CP5.

This patch needs to be applied to all Oracle Homes, i.e. Infrastructure home as well as all related midtier homes.


The following posting has been updated:

Tuesday, July 14, 2009

Getting virtually there with Discoverer

With so many different versions of Discoverer and versions of the database it always difficult to know what tools and patches you need for everything to work together. One way to make this much easier is to use virtual machines to install the software.

The computer I use the most is my laptop running Windows Vista and using the VMWare workstation tool, I have Discoverer Administator, Desktop, Plus and Viewer all working together with the Oracle 11g database on the laptop. I haven't got Discoverer 11g working yet in a VM, but I will try the software in the VM environment before installing elsewhere. You need lots of memory (I have 4Gb) and don't expect stunning performance. But having all the tools installed together is invaluble for testing, evaluation and training.

One thing I really like with this approach is that you can snapshot the VMs at any point in time. Then if you make a mistake, for example, an installation does not work properly, then you can quickly revert the VM back its previous state.

There isn't enough time for a detailed instructions to install all the Discoverer tools but here are the basic steps you need to follow:

  1. Download and install VMWare workstation (http://www.vmware.com/download/ws/) which you can get on a one month trial.
  2. Download the Oracle Enterprise Linux DVD from the e-delivery site (http://edelivery.oracle.com/linux). Check which releaes you want as OEL release 5 does not work with some versions of Oracle 10gAS.
  3. Create a VM of type Red Hat Enterprise Linux. Give the VM the maxium recommended memory. Use a bridged network and specify the hostname and IP address. You can then add the hostname and IP address into the windows hosts file so that you will be able to access Discoverer running in the VM from the windows browser.
  4. Install the VM tools which is an addition Linux package supplied with the VM workstation.
  5. Add the ip address and hostname in the /etc/hosts file.
  6. There are some addition Linux packages (such as gcc and gcc-c++) need to be installed. These are listed in the database and application server installation manuals. All the packages are available in the OEL DVD.
  7. Some Linux kernal parameters have to be increased. Details of how to do this are in the 11g database installation guide. For an 10gAS installation you need shmmax = 4294967295 and msgmnb = 65536
  8. There are some other pre-installation tasks for the Oracle 11g in the installation manual. These are adding the oracle, dba and oper unix groups and changing the shell limits.
  9. Use VM Workstation to create a windows shared drive.
  10. Download Oracle 11g database (http://www.oracle.com/technology/software/products/database/index.html), unzip in the shared drive and run the installer to install Oracle 11g in the VM.
  11. Download the Discoverer software e.g. Business Intelligence (http://www.oracle.com/technology/software/products/ias/htdocs/101202.html) for Linux CDs. Unzip into the shared drive and run the installer from the Linux command line. Download and install any patches you need.
  12. Download the Discoverer tools software for windows (Admin and Desktop) and install on Windows Vista.
  13. Create a new tablespace for Discoverer and create an End User Layer. You are now ready to start importing objects into the EUL and creating workbooks.

Another thing I like about this approach is that you can run Discoverer Plus and Viewer in the windows browser connecting to the application server and database in the Linux VM just as you would if the Linux was on a separate server. You can create many different VMs, each with different versions, different software tools etc and switch between them to try out different ideas.

I can post more detailed instructions if anyone is interested.

Oracle releases Discoverer 11g

Hi everyone
The long and eagerly awaited release of Discoverer 11g has just been announced by Oracle and it is available for download right now. This new version of Discoverer is now part of Oracle Fusion Middleware 11g.

Here is a (link) to the software download. Scroll down to the section entitled Portal, Forms, reports and Discoverer and click on the appropriate link(s) for your platform. The download is beigger than you have been used to.

Here is a (link) to the documentation download.

My good friend Abhinav, who runs the Oracle BI Blog and was the techincal editor for the Discoverer 10g Handbook, has more information available (link). Please pay close attention to Abhinav's comments regarding the order for installing the software.

My friend, Mark Rittman, who also runs a blog and wrote the OLAP chapter in the Discoverer 10g Handbook, has further information (link) including some screenshots.

Here are some important notes for you to consider:

  • Currently the software is only available for the Microsoft Windows and Unix x86 platforms. The other operating systems as well as the 64-bit versions will be available soon
  • The OC4J engine that we have all got used to has been dispensed with and replaced with the WebLogic Server, otherwise known as WLS. Get used to this acronym as it will soon become common place within the Discoverer environment
  • From 11g, Discoverer is still available as a stand-alone install but the install is done from a single interface. If you want to use the full-blown install there is now a single install instead of the two as there are with 10g.

I will be making more postings in the coming months as we all come up to speed on the new version.

One thing to note, as far as I know at this stage, is that once up and running the functionality of Discoverer 11g is virtually the same as the later versions of Discoverer 10g. I am not sure which cumulative patch it is equivalent to but as soon as I find out I will let you know.

Saturday, July 11, 2009

Keeping it simple

There's a lot to be said for the KISS principle.

Very often I find that all a company wants is a simple report generated out of an easy to use query tool. Yet, all too often, somewhere along the way a salesrep for a competitive product has persuaded them that the simple to use end-user tool they have been using all these years is now old hat and needs to be replaced. Sound familiar?

Not so very long ago I was at a conference when I overheard a friend of mine speaking with the senior sales representative for one of Oracle's competitors. Unaware that I was within spitting distance the sales rep was heard to say "and I bet that English so and so is here somewhere!"

Because the simplest of situations amuse me immensely, "Good morning" is all I had to say and off he stalked. What had transpired is that one of my Discoverer customers had been approached by a competitor who had somehow picked up a vibe that Oracle would no longer be supporting their beloved query tool. After dispelling this idea my client decided to stay with Oracle and asked the other vendor to leave.

The truth of the matter is that my client really does have a very simple set of requirements revolving around variances of "tell me how many widgets I sold last month". They don't need a sledgehammer to crack that walnut and keeping it simple is something their report writers are very good at.

Thursday, April 30, 2009

Discoverer CP4 is available again

Last updated: May 5, 2009
I am delighted to be able to let you know that CP4 (patch number 7595032) is no longer password protected and the issue with the non-English language versions (see below) has been resolved. You are therefore free and encouraged to apply this patch.

By the way, this patch has all of the updates from CP8 and many more.

Important Note:
Please note that if you are planning on installing CP4 that you will also need to install a patch for JDBC. I have made a comment on this in this posting and will post more information shortly.

Last updated: May 4, 2009
The latest information I have on this is as follows:

It was initially communicated that it only affected a small subset of customers as it was thought to only affect Japanese customers using Discoverer Plus. It was later discovered that some other locales were also affected.

Customer's were later updated with: Note: CP4 has temporarily been withdrawn due to a late breaking regression tracked in Bug 8477401 NLS: Discoverer Plus Always Runs In English Even If Japanese Locale Is Set'.

This can potentially affect all non-English locales. Oracle Support cannot provide a password or give access to the patch until the regression is corrected and the patch is re-uploaded to MetaLink. If you have have already downloaded the patch and use the English locale, then you should not be affected and can continue using the patch. This note will be updated when the updated patch is released.

Original Posting made: April 30, 2009
If you are vigilant and keep up with Oracle's cumulative patches and this blog (see here) you will be aware that CP4 (patch number 7595032) came out a couple of days ago.

Earlier today I noticed that it has now been password protected so I contacted Oracle. Here is what they told me:

CP4 has temporarily been password protected due to a late breaking regression tracked as bug number 8477401: Discoverer Plus Always Runs In English Even If Japanese Locale Is Set. This should only affect a small subset of customers. We are trying to get this password protection removed asap since it only affects a narrow band of customers.

Oracle plans to have this password protection removed quickly so it could even be removed before the information filters out too far. I will let you know when the password requirement has been lifted. If you are using English then you should go ahead and request the password as you won't be affected.

Please note that if you are planning on installing CP4 that you will also need to install a patch for JDBC. I have made a comment on this in this posting and will post more information shortly.

Tuesday, April 28, 2009

Michael presenting at ODTUG Kaleidoscope

In June, I will be at the ODTUG Kaleidoscope conference between June 21 and June 25 in Monterey, California, where I will be presenting a paper entitled Maximize your investment in Discoverer. If you happen to be going please look me up. I will be presenting the same paper and also sitting as a panelist on the Business Intelligence / Data Warehousing Panel.

I am also scheduled to appear at the Baton Rouge Oracle Users Group meeting in July and have submitted a paper for Oracle Open World which is in October in San Francisco. I will provide more information on ODTUG, BROUG and Open World as the times draw closer.

For the eagle eyed among you, you will notice that originally this posting said that I was also going to be at the IOUG / OAUG Collaborate conference in Orlando next week. Unfortunately, something came up at the last minue which has prevented me from attending. This is the first time in 10 years of going to conferences that I have had to cancel an appearance like this. However, the good news is that Mike Durran from Oracle and Mark Rittman have agreed to step in and give my presentation for me. More on this to follow.

Michael is now a US citizen

Hi everyone
I thought I would let you all know that last Friday afternoon in Nashville I took the oath to become a citizen of the United States. This is such a privilege and honor for me and something I have been looking forward to for quite some time, ever since I first started coming here for business way back in 1994.

I emigrated to the United States in 1999 while working for Logitech as their world-wide manager of business intelligence. Within a few months of my arrival I met Darlene and we were married within the year and have been together ever since.

We moved to Tennesses 5 years ago when we formed Armstrong-Smith Consulting and bought ourselves a small ranch so that we could enjoy the fine weather and get away from the hustle and bustle of consulting when time would allow. If you would like to see a few photographs of our place you can find them here on the About Us page of our website.

Cumulative Patch 4 for

I am very pleased to announce that Oracle have released the eagerly awaited cumulative patch 4 for Discoverer (patch number 7595032). When compared to CP2, almost 80 bugs have been fixed including the bug that was preventing usres from logging in correctly after applying CP3 (see here).

So far this has been released for the following platforms:
  • Microsoft Windows 32-bit
  • Linux x86 (works for both 32 bit and 64 bit)
  • Sun Solaris (64-bit)
  • HP-UX PA-RISC (64-bit)
  • IBM AIX Based Systems (64-bit)

There is also a very important note inside the readme regarding installing an interim patch. Here is the necessary information:

Please apply the JDBC patch p4398431_10105_GENERIC.zip for bug 4398431(release patch needs to be applied to all Oracle Homes, i.e. Infrastructure home as well as all related midtier homes.


Then proceed with applying the current patch.

The following postings have been updated:

Tuesday, April 21, 2009

Oracle buys Sun

If you haven't already heard, Oracle has announced (see here) that it has entered into an agreement to acquire Sun Microsystems (Sun).

My good friend Mark Rittman has a very good article on his blog (see here).

According to the Oracle press release:

The acquisition combines best-in-class enterprise software and mission-critical computing systems. Oracle plans to engineer and deliver an integrated system—applications to disk—where all the pieces fit and work together so customers do not have to do it themselves. Customers benefit as their system integration costs go down while system performance, reliability and security go up.

So what do you make of it? Personally I think it's a great move and will only strengthen Oracle's hand when it comes to vying with Microsoft in the marketplace. I think it was a deal that was always going to happen as Sun's Java is a product that means an awful lot to Oracle as most of their products require it to run effectively.

Of course, this also means that Oracle will now own the Solaris brand of hardware. So if the deal does go through, and I don't see why it should not, you will then be able to get one-stop shopping for hardware and software from the same vendor.

Personally, I say this is a great deal for Oracle and an even greater deal for Oracle's customers.

Charles Phillips, President of Oracle, today said the following:

Oracle's ownership of two key Sun software assets, Java and Solaris, is expected to provide our customers with significant benefit. Java is one of the computer industry's best known brands and most widely deployed technologies. Oracle Fusion Middleware is built on top of Sun's Java language and software. Oracle can now ensure continued innovation and investment in Java technology for the benefit of customers and the Java community.

The Sun Solaris operating system is the leading platform for the Oracle database. With the acquisition of Sun, Oracle can optimize the Oracle database for some of the unique, high-end features of Solaris. Oracle is as committed as ever to Linux and other open platforms, and will continue to support and enhance our strong industry partnerships.

Our customers have been asking us to step up to a broader role to reduce complexity, risk, and cost by delivering a highly-optimized standards-based product stack. Oracle plans to deliver these benefits by offering a broad range of products, including servers and storage, with all the integrated pieces: hardware operating system, database, middleware and applications. We plan to preserve and enhance investments made by our customers, while we continue to work with our partners to provide customers with choice.

Monday, April 20, 2009

April 2009 Patch Levels for Discoverer

Oracle is continually refining and upgrading Discoverer. Because of this refinement process, from time to time, Oracle periodically withdraws support for older releases. These are called obsolescence desupport documents and can be found on MetaLink.

I will summarize the major releases here, along with their patch and desupport numbers.
  • Discoverer 3.x - completely desupported. The terminal release was 3.1.36 for Administrator and Desktop and 3.3.62 for Plus and Viewer. You should migrate to Discoverer 10g Release 2 ( or as soon as you can. Note that there is no direct migration path from Discoverer 3.1 to Discoverer 10g Release 2, as you must migrate via The obsolescence desupport notice on MetaLink is document 162402.1.

  • Discoverer 4.x (stand-alone, not as part of E-Business Suite) - completely desupported. The terminal release was You should migrate to Discoverer 10g Release 2 ( or as soon as you can. The obsolescence desupport notice on MetaLink is document 231072.1.

  • Discoverer 4.x (with E-Business Suite) - desupported as of October 31, 2006. According to Oracle "since Discoverer 10g ( - now or is fully certified, we HIGHLY recommend implementing or migrating to this release". Extended Support (ES) will continue until October 31, 2009. You are advised to migrate to Discoverer 10g Release 2 as soon as possible. The obsolesence desupport notice for Discoverer 4i against E-Business Suite is MetaLink document 337576.1.

  • Discoverer 9iAS (9.0.2) - completely desupported as of July 1, 2008. The terminal release was You are urged to upgrade to Discoverer 10g Release 2 ( or as soon as you can. The obsolescence desupport notice for Discoverer 9iAS is MetaLink document 162403.1.

  • Discoverer 10g Release 1 (9.0.4) - ECS support was withdrawn December 31, 2006. The recommended release is (Application Server Extended Support will continue until December 31, 2009, however you should upgrade to Discoverer 10g Release 2 as soon as you can. The obsolescence desupport notice for Discoverer 10g Release 1 (9.0.4) is MetaLink document 295948.1.

  • Discoverer 10g Release 2 initial ( - The terminal release is You can upgrade to by applying the patchset which will bring you to the code level (see below). No ECS or ES dates have been announced for The support notice for Discoverer 10g Release 2 is MetaLink document 329361.1.

  • Discoverer 10g Release 2 production ( - This is the current and most up-to-date release and the one you should be on if you can. Out of the box the base version you will get is Notwithstanding the fact that this is the current version it is also a brilliant piece of software. Having installed the base release you need to upgrade to either ( or ( and then apply the most recent cumulative patch. No ECS or ES dates have been announced for this release. The support notice for Discoverer 10g Release 2 is MetaLink document 329361.1.

Notes: the definitions of ECS and ES can be found on MetaLink in note 187553.1

Friday, April 17, 2009

Be careful when upgrading to Java 1.6

If you install Discoverer 10g out of the box you will get Java 1.4 on the server.

Last year Oracle announced that the server was now certified with Java 1.6 and issued a MetaLink note complete with instructions on how to upgrade the JVM.

On my travels, I have noticed that some administrators do not read the release and installation notes properly and overlook the fact the configuration.xml (found in $Oracle_Home/discoverer/config) needs to be updated as well. If you only install a new java executable but leave the configuration.xml file untouched you will run into serious user interface issues. One such problem is that parameter values will no longer be displayed from your user's last run or from when the worksheet was created thus causing your end users a great deal of anxiety.

An application server in such a state will also run much slower too, so please make sure you update the configuration.xml correctly.

To help you, here is the JVM section from the application server running on my own laptop:

< name="sun" classid="clsid:CAFEEFAC-0016-0000-0012-ABCDEFFEDCBA" plugin_setup="https://asclaptop0.learndiscoverer.com:7778/jpi/jre6u12.exe" version="1.6" versionie="1,6,0,mn" type="application/x-java-applet" plugin_page="http://java.com" disco_archive="disco5i.jarjar" d4o_archive="d4o_double.jarjar">

1. This is all one line with no line breaks or carriage returns. There should also be no space following the leading or before the last brackets.

2. I have spaces to make sure that you can see the text and to stop your browser interpreting this as HTML.

3. The version number 12 only appeard in one place, in the classid switch.

The areas of importance are these switches:
  • classid
  • plugin_setup
  • version
  • versionie
  • plugin_page

In the following help I will show you the settings for Java 1.6.0_12. All of the switches are enclosed in double quotes.

classid: this complete switch has 36 characters broken down as follows:

  • first 9 always CAFEEFAC- all in uppercase
  • next 5 are always 0016- meaning Java 1.6
  • next 5 should be 0000- meaning Java 1.6.0
  • next 5 should be 0012- meaning release 12 (only place this is referenced)
  • last 12 should be ABCDEFFEDCBA again all in uppercase
  • full switch is: classid="clsid:CAFEEFAC-0016-0000-0011-ABCDEFFEDCBA"

plugin_setup: this switch should be fully qualified domain name pointer to the Java executable on the server. Even though this switch always has /jpi/ in the pointer the actual executable must be stored in /jpi/bin/ under the $Oracle_Home for the middle tier. The name of the executable can be anything you look but must be the same as the executable stored in $Oracle_Home/jpi/bin.

version: this switch should be 1.6 with a period between the two numbers

  • full switch looks like this: version="1.6"

versionie: this switch should be "1,6,0,mn" this time with commas between the 4 elements. Yes, mn should be used and not 12 as you might think.

  • full switch looks like this: versionie="1,6,0,mn"
plugin_page: this switch should just be http: //java.com and not a pointer to the complete version as it was in 1.4

  • full switch looks like this: plugin_page="http://java.com"

After you have upgraded the server to Java 1.6 I recommend you go to each of your user machines and clear the local Java cache. Discoverer will then ensure that they have the correct applet and will send down the right Java version the first time the user connects.

If your users have an earlier version of Java installed or no Java installed then the first time that they connect to Discoverer on an application server that is running Java 1.6 they will be prompted to install it. All they have to do is follow the on-screen prompts and they will be ok. If your users don't have administrative rights over their local PC or if you are making them connect via a Citrix terminal server then your administrator will need to install JVM 1.6 for them.

Thursday, April 16, 2009

No more cumulative patches for

If you have been keeping up with the latest information for Discoverer on Oracle MetaLink you will have noted that Oracle recently announced that cumulative patch 8 for is the last one for that platform and that the platform currently being developed is

If you haven't already upgraded to you should skip it and proceed direct to The steps to installing on a native are identical to the steps that you would have to apply to install This means that if you are using an infrastructure then you will have to upgrade the metadata repository from to at least

If you have already installed and any of the cumulative patches the good news is that (patch 5983622) will install directly on top as there is no need to de-install anything, the upgrade will take of it for you. After you have upgraded to you should then proceed directly to install cumulative patch 2 (patch 7198716).

You might be wondering why I say to should upgrade only to cumulative patch 2 when cumulative patch 3 was released earlier this year. This is because there are some bugs in cumulative patch 3 and you are advised to wait until cumulative patch 4 which, by the way, I have heard is due for release within the next 2 to 3 weeks. Just as soon as it released I will update my master patch blog note.

Saturday, March 14, 2009

Clamping Down Discoverer

Performance is often a big issue with Discoverer especially when Discoverer users share resources with other system users. It is often said that Discoverer must always use a separate reporting database because the Discoverer users would slow down the production system. This is rubbish; there are some good reasons for putting Discoverer on a separate system, but performance is not one of them.

Much better is to put all your system resources into one database and then clamp down Discoverer so that it cannot interfere with other processes. You can reduce the priority or limit the CPU; you decide what database resource Discoverer should use and when it should use it.

This post describes how to clamp down the Discoverer so that it does not slow down the rest of the system.

Resource Consumer Groups

The Oracle Database Resource Manager provides essential functionality to help you do this. It allows you to manage the hardware resources that are allocated to a database session. The Resource Manager provides a flexible way to control the Discoverer sessions because it allows you:
  1. limit the CPU or I/O used by a session
  2. lower the priority of session
  3. terminate sessions that use too much resource
  4. terminate sessions where the optimizer estimates that the SQL run time will be over a threshold

Even better, the resource scheme used can be changed dynamically, so that you can for example, have one resource allocation for during the day and another for night time and weekends.

Setting up a resource plan can be quite complex but fortunately there is a default SYSTEM_PLAN provided which has a low priority group into which you can add the Discoverer sessions. All other sessions will use the default consumer group so that any Discoverer session in the low priority group will only use CPU not consumed by other database sessions.

A listing of the plans and consumer groups available in the database can be obtain from the DBA_RSRC_PLANS and DBA_RSRC_CONSUMER_GROUPS views.

By default the Resource Manager is not enabled. So first you need to enable the Resource Manager by setting the database initialization parameter to a resource plan:

Alternatively, you can activate the Resource Manager on the fly by entering:

Next you need to ensure that the Discoverer sessions are added to the low priority consumer group. There are basically two ways that you can do this; using the consumer group mapping functionality to automatically add the session to the consumer group when the session is created in the database; or run an initialization procedure at the start of the session to switch the consumer group from the default group to the low priority group.

Consumer Group Mappings – The database lets you set up a mapping between session and consumer groups. Many new mapping options were added in Oracle 10g and therefore on databases prior to 10g the mapping option you need may not be available. A mapping that automatically adds sessions to a consumer group can be created using DBMS_RESOURCE_MANAGE.

There are lots of mapping options available. You can add all the sessions create by a user into a consumer group. For example, to add all the sessions created by the DISCO_USER database user into the low priority consumer group the following call to the DBMS_RESOURCE_MANAGER package can be used:


You can add all the sessions created by a particular program. For example, the sessions created by the Discoverer Desktop program can be added using:


You can also add sessions by service name, so here you would define a service name in the TNSNAMES.ora files used by Discoverer and map the service name to a resource group.

Initialization Procedure - This is the method we use. A procedure is run at the start of every Discoverer session which switches the consumer group. Because we are in an Oracle Applications 11i environment we use the ‘Initialization SQL Statement – Custom’ system profile to call the initialization procedure rather than a trigger. The procedure includes the following statement:

DBMS_SESSION.switch_current_consumer_group('LOW_GROUP',v_varchar2_240, FALSE);

to switch the session to the lower priority consumer group.

The initialization procedure gives you the best control over the consumer group setting. For example, you can set the priority of scheduled Discoverer sessions to be lower than the priority of regular sessions.

RAC Nodes

Real Application Clusters (RAC) provide an effective way of segregating Discoverer from other processes. Typically you would have a database node dedicated to processing Discoverer reports. Discoverer can then be directed to the dedicated node by setting the RAC node in the TNSNAMES.ora file on the Discoverer Application server (and on the desktop if Discoverer Desktop is used). You can use service names to specify preferred and failover nodes.

Using a dedicated RAC node is the cleanest way of separating Discoverer from other processing in a RAC environment however, if you use parallel processing in your Discoverer reports then some further configuration is required.

By default on a RAC node any SQL statement that uses parallel processing will parallelize the workload by running slave processes on all RAC nodes in the database. So if the parallel_max_servers database parameter is set to 8 then 8 parallel processes will be started on each RAC node in the database. That's great if you want your Discoverer report to use CPU resource across all the RAC nodes, but if you want Discoverer just to use the dedicated RAC node then in Oracle 10g you need to set up a RAC instance group. In 11g it’s a bit easier as the nodes are service aware and only run the named service.

1. For example in 10g, if PROD is the database instance and PROD4 is the dedicated RAC node then an INSTANCE_GROUP called DISCO for the Discoverer dedicated node can be defined by including in the database initialization file:

2. Then in the Discoverer initialization procedure include the following statement:

The Discoverer processes will then join the DISCO parallel instance group when the process is started and any subsequent parallel SQL run by this process will only use the RAC nodes in the DISCO instance group.

Temporary Tablespace

A Discoverer report that is very large or has been badly written can use a lot of temp space. The database uses temporary tablespace when a database sort is too large to fit in memory and so if Discoverer has to sort lots of data for example in a hash join then it will use temp space. The size of the temp space needs to be controlled to stop the disks filling up and hence a maximum size limit for the temporary tablespace should be set.

This is a problem if Discoverer is sharing the temp space with other processes. When a Discoverer report runs out of temp space then it will fail with ORA-01652: Unable to extend temp segment. The problem is that the temporary tablespace is a database user resource and so is shared with other processes that use the same temporary table space.

Hence when Discoverer processes are sharing a database with other processes it is important that the Discoverer processes use a separate tablespace. Otherwise one or more rogue Discoverer processes can gobble up all the temp space causing another process to fail when there is no temp space available.

Now if your Discoverer processes always connect to the database as separate database users then the solution is easy; you create one or more separate temporary tablespaces for these database users. Then the Discoverer processes may still fail with the ORA-01652: Unable to extend temp segment error but at least other processes will be unaffected.

However, where Discoverer is used with the e-Business Suite (and also in many other systems) Discoverer connects to the database using a common database user. In the e-Business Suite all processes connect as the APPS database user and therefore by default all processes share the temporary tablespace associated with the APPS database user. The OLTP processes probably do not use any temp space but interface and batch processing often use temp space and could error as a result of Discoverer processing.

The solution is not easy and different solutions may be required for different systems, but where Discoverer is implemented with the e-Business Suite a separate temporary tablespace can be implemented as follows.

1. Create a new user with a limited tablespace.



2. Create an initialization procedure for the Discoverer which changes the current schema to the APPSDISCO user for the Discoverer process. The current schema defines which database schema is used as the default schema where no schema is specified in front of a database object. Consequentally the temporary tablespace used is the temporary tablespace associated with the APPSDISCO user.

PROCEDURE initialize
END initialize;

3. Call the initalization procedure from the ‘Initialization SQL Statement – Custom’ system profile for all the responsibilities that use Discoverer. So if your initialization procedure is held within the DISCO_UTILITIES_PKG PL/SQL package then the profile would be set to:

begin disco_utilities_pkg.initialize; end;

However, there are some complications that may need to be addressed:

  1. The SYS_CONTEXT('USERENV','MODULE') only works in Oracle 10g. In Oracle 9i you need to check in the V$SESSION table.

  2. Although Discoverer puts the APPS schema in front of all the database objects I found that there were some database bugs in the version of the database I was using (Oracle 9i) and I had to create some synonyms for some of the APPS objects, for example, to get all the Discoverer reports to run I had to create synonyms in the APPSDISCO schema for all the packages used by functions mapped into the EUL.

  3. Again, although the Discoverer processes still connect as the APPS user and therefore have all the APPS database privileges I found that database/Discoverer bugs caused privilege errors. To workaround this I had to grant privilege on some APPS database objects to the APPSDISCO user.


Where Discoverer is reporting on transactional data then Discoverer should use the same database as the transactional system. A single database allows Discoverer to report on real-time data and saves all the complications of replicating and managing separate databases.

A single database allows more efficient use of the hardware resources by allowing Discoverer to use spare CPU that would otherwise be wasted. Long running Discoverer processes can run at lower priority than other processes in the system increasing the overall throughput of the system. Provided Discoverer has been properly clamped down then the Discoverer processes will not affect other processing in the system.

You can go further and separate Discoverer scheduled processes from interactive Discoverer processes. Then you can run the scheduled larger Discoverer reports at a lower priority and separate temp space than the interactive reports.

When Discoverer Users and management know that they will not be able to affect the performance of the system as a whole by running Discoverer reports, they will then have confidence to build and run more complex reports making greater use of the Discoverer reporting system.

Wednesday, February 25, 2009

Caching data for Discoverer

I thought it was about time for another post and this one is about how you can cache data for use in Discoverer.

Caching is just one strategy that you can use to make your Discoverer reports run faster. It is a bit like bribing a wayward child. You give it some cash and it will get on with the job a bit quicker. But the performance improvement doesn't come for free. You have to load your cache first and keep it ready for the Discoverer report to use which will use additional resource.

Caching is big a topic; too big to cover in a single post. There are different levels where you can cache data for Discoverer reports. You can use OracleAS Web cache to hold HTML web pages generated by Discoverer Viewer. You can cache data in the database ready for use by Discoverer. You can schedule Discoverer workbooks to run so that the results are cached ready for the user to access.

This time I will look at how you can cache data in the database where you hold records in a cache ready for Discoverer to use. Before I go into the details I think it is important to say that caching is not a technique you should use to make a badly written report run faster. If it badly written and inefficient the solution is to fix it, not add more complexity by caching data to try and make it run faster. Nor should you use caching to try and make one tricky report run a bit faster. It is something you need to design into your Discoverer environment at the beginning to give you the maximum benefit.

Caching data in the database is where records are cached in the database ready for Discoverer to use. Again there are many techniques you can use; some of these techniques depend on new features introduced in Oracle 11g. A database cache can make a big difference to the performance of your reports as it will enable Discoverer to run simpler SQL for the reports which will it turn give better performance. But, like with all performance work, it all depends on many factors; a database cache could make no difference or make your report run slower. It is all about using the right technique for the right situation.

Basically there are four points where data can be cached for a Discoverer report:

  1. Before the user logs on
  2. When the user logs on
  3. On-the-fly when a report runs
  4. In a SQL result cache
Unfortunately there is no eul_trigger$post_open_document trigger available in Discoverer or any mechanism that would allow you cache data when a workbook was first opened or run.

Caching data before the user logs on

Here the data is cached overnight or at regular intervals so that it is available when a user logs on.

The traditional technique is used is a materialized view. Materialized views (MVs) have two really cool features. Firstly, they can be automatically refreshed either on commit, or periodically or on demand. Secondly, they can use query redirection where an SQL statement that references the underlying tables but which only requires summary information is re-directed to the MV.

Oracle 11g introduces some new features for materialized views which could be very useful for Discoverer developers. One of the most interesting is that you can set up an OLAP cube in the database that summaries your data and then use a MV to redirect queries to the OLAP cube. Hence you could use the Discoverer relational tools to gain the benefits of running against an OLAP cube without having to switch to the Discoverer OLAP product.

Having said that, I would avoid using MVs and would only use them as a last resort. Here are some of the reasons why:

  1. Normally, you cannot use an ‘on commit’ refresh on the MVs and therefore the information in the MVs is always older than the underlying tables. This means the results obtained from MVs are always slightly out of date.
  2. Also, often you cannot use an incremental refresh on the MVs because this requires a primary key defined on the underlying tables. So the MVs are often completely refreshed periodically and this needs a lot of database resource.
  3. When the MV is refreshed you don’t know which users will be accessing the information and therefore it is difficult to ensure the data security available in the underlying tables is replicated in the MVs.
  4. Frequently the query used to define the MV is complex and therefore the database is not able to use query redirection because the database must be able to match query being redirected to the query defining the MV.

I have seen a number of projects come unstuck because they have tried to improve performance by making some of their reports into MVs. The result is many complex MVs which are constantly out of date and need huge database resources to refresh.

I have found the best approach is to design a small number of simple MVs that summarise some core tables that can be used by many reports.

Caching data when the user logs on

Here the data is cached when the user logs onto Discoverer. The mechanism that you use to run a PL/SQL procedure to cache the data will depend on your environment but could be either:

  1. a database trigger that checks whether the current session is a Discoverer session
  2. the Oracle Applications 11i ‘Initialization SQL Statement – Custom’ system profile can be used to run an initialization PL/SQL procedure for Discoverer sessions
  3. an eul_trigger$post_login trigger is used to run a PL/SQL procedure.

When the session initialization process runs it can run SQL to cache data from the session which then can be used in all subsequent Discoverer reports. There are different places that you can cache data in the database.

Database Contexts – This is useful for storing session parameters that you can use in subsequent database queries.

Temporary Tables – You use one or more temporary tables as a cache for the Discoverer. The table then can be used within the Discoverer reports or as a base table for a list of values. The data in the temporary tables will only be visible to the current Discoverer session so is useful for storing information specific to the user that can be used to improve performance in reports.

PL/SQL arrays – This is useful for caching data that is required by the PL/SQL functions run from Discoverer. See the next section for more details of this technique.

For example, if many of your queries need to use an ORG_ID associated with a user then create a context namespace using:


Then in the process initialisation obtain the ORG_ID for the user and set the context using:

dbms_session.set_context('DISCO', 'ORG_ID', v_org_id);

You can then check the context in a condition in your view or Discoverer folder using SYS_CONTEXT('DISCO', 'ORG_ID') .

If you have lots of contexts that you need to set at initialisation then it is useful to put all the queries to obtain the contexts values in a view then you can set all the contexts using:

FOR xcon IN (SELECT context_name, context_value
FROM xxmod_dis_context_value_v)
dbms_session.set_context('DISCO', xcon.context_name, xcon.context_value);

Then any changes to the context set up can be accommodated by simply changing the view.

However, where you need to save many ORG_IDs or, for example, a list of PRODUCT_IDs that a user can see then the technique to a temporary table. For example, you can create the temporary table using:


Then just insert the product ids into the table in the initialisation PL/SQL procedure and use the table in a Discoverer folder or view just like a normal table. When the session finishes the data is deleted.

Caching Data on-the-fly

Here data is loaded by the first call to a PL/SQL function into a PL/SQL array. Subsequent calls to the function use data from the internal array. This technique is very useful for converting internal codes into text descriptions that are required in a report. For example, in an Oracle Applications 11i environment where you need to make repeated lookups into tables such as FND_FLEX_VALUES_VL and FND_LOOKUP_VALUES to obtain segment and attribute descriptions. It can also be used to store values that are hard to obtain but need to be used many times in a report.

Taking a generic approach using a single cache_lookup function gives the best results. The cache_lookup function can be defined like this:

FUNCTION cache_lookup(lookup_type IN VARCHAR2,

The lookup_type parameter determines which SQL statement is used to obtain the data for the cache. The lookup_code1 is a bind variable for the SQL statement. The cache_lookup function is overlaid with multiple versions with different numbers of bind variables, because some lookups require multiple bind variables. For example, the call to

cache_lookup('LOOKUP_MEANING', 'MAR_STATUS', papf.marital_status)

obtains the marital status text from the FND_LOOKUP_VALUES table.

The function actually works by using DBMS_UTILITY.get_hash_value to hash all the input parameters together and check in a BINARY_INTEGER indexed PL/SQL array to see whether the value is in the cache. If it is not then the SQL statement is run and the value is added to the cache. Subsequent calls to the function with the same parameters retrieve the value directly from the cache.

Oracle 11g introduces the concept of a function result cache which provides similar functionality so this could be the way to go if you are using 11g.

There is always a trade-off between the overheads of making a call to a PL/SQL function against the benefits of caching the data also bearing in mind that the Oracle database is often very efficient at caching data internally when processing SQL statements.

However here are four reasons why I often use PL/SQL caching:

  1. It’s quicker. Joining lookup tables in to query often makes finding an efficient execution plan difficult for the optimiser. The most efficient SQL is often to use a scalar query within the SELECT statement but this is not supported by Discoverer or in a materialized view. An alternative is to outer join the lookup tables but the outer join always generates less efficient SQL and cannot be used with an OR condition. Using a PL/SQL cache can be quicker because it removes all the lookup tables from the main SQL giving the optimiser the best chance of finding an efficient execution plan. But really it all depends on the complexity of the lookups as to whether you will see a performance improvement using a PL/SQL cache.
  2. It’s quicker again. A Pl/SQL caches lasts for the duration of a session. Often running ad-hoc Discoverer involves run using similar queries many times. Your caches is loaded on the first query so subsequent queries are quicker.
  3. Better instrumentation. This is the big bonus of using a PL/SQL cache. Especially, with Oracle Applications there are often errors encountered when processing the lookups. Sometimes, there is more than one description for the lookup code, or no description is found when one is needed. Finding the row that has caused the problem can be very difficult. With a PL/SQL cache you can build in instrumentation code that raises a meaningful exception when an error is encountered. Even better you can make this behaviour configurable so that for normal users lookup errors are ignored.
  4. Less code. Less code is always good. By using a generic lookup function you can put all your lookups in one place. This function can then be called in views, Discoverer calculations and anywhere else where you need to convert a code to text.

Caching data in SQL result cache

This is a Oracle 11g technique that allows you to save the results of an SQL query (or subquery within a query) in an SQL cache located in the SGA. This is probably most useful to cache the results from a complex query fragment within your Discoverer views. For example, if the results from dept are complex and difficult to obtain you might define a view based on a query like this:

SELECT employee_name, dept_id …
FROM emp_table
, (SELECT /*+ result_cache */ dept_id, dept_name, …
FROM dept) dept
WHERE emp.dept_id = dept.dept_id

I haven’t had a chance to benchmark this feature so cannot make any claims for performance improvement but I believe it would help in some situations.

Friday, February 20, 2009

Word of warning about and CP3

Hello everyone
I have been monitoring and taking part in a thread (click here for thread) on the OTN Discoverer Forum concerning an issue with 8-character passwords not always being accepted in Discoverer with CP3 installed.

Oracle Support have now confirmed that this is a bug and have posted it on MetaLink as bug number 8267360.

Basically - if you access the Discoverer login page for either Plus or Viewer with a URL parameter like &database=(TNS name) AND the user has exactly 8 characters in his / her password, then you will get an ORA-01017 logon denied, even though the password is correct.

These login bugs have been fixed with Cumulative Patch 4 (patch 7595032 on MetaLink)

If you are therefore on with any cumulative patch or on with CP1 or CP2 installed and you like to use the EUL switch in the launch URL you should install CP4, which was released on 28th April, 2009.

Jacob, I am indebted to you for bringing this to my attention.

Tuesday, February 03, 2009

February Ask Michael

The January Ask Michael webinar was a great success.

The next Ask Michael is scheduled for Wednesday, February 11th at 3pm Central time. If you have not signed up or want to send me a question please use this link.

During the January webinar I answered a question on setting Discoverer preferences for Lee from the Corporation for National and Community Service in Washington. Here is what Lee said:

"I had asked for Michael's help regarding query restrictions. We had spent a number of days researching and applying Oracle MetaLink fixes to no avail. Michael's step-by-step explanation showed us a step we had missed and we look forward to 'curing' our production Discoverer during our next code update. We've tested Michael's solution in both of our test environments and it worked flawlessly! It's too late to save me from gray hairs, but at least our Discoverer report writers now aren't calling to complain about out failure to fix this issue"

I am delighted that I was able to help in this way. These online webinars are a free service offered by me and Armstrong-Smith Consulting to the Discoverer and Business Intelligence community. All you have to do is register in order to attend.

You can see all of our upcoming offerings such as webinars, training and conference attendance, on the ASC calendar page of our website.

See you on the 11th.

Wednesday, January 07, 2009

Cumulative Patch 3 for

I am very pleased to announce that Oracle have released a third cumulative patch for Discoverer

So far this has been released for the following platform:
  • Windows 32-bit
  • Linux x86 (works for both 32 bit and 64 bit)
  • HP-UX PA-RISC (64-bit)
  • HP-UX Itanium
The following posting has been updated:

New Armstrong-Smith Consulting Web Site

Hi everyone
I'd like to point you in the direction of our new website. We all think this is a vast improvment on our old website.

First of all we are no longer only focused on Discoverer. We are business intelligence. However, because most of you reading this entry are interested in Discoverer of course the new website is still the place to go.

By the way, there's still time to sign up for my Ask Michael Seminar series which kicks off next week on January 14th.