Learn Discoverer Blog

CP10 for Discoverer 10.1.2.3

2011-05-16T16:56:00.000-05:00

Just wanted to let you know that on April 18, 2011, Oracle has released CP10 for 10.1.2.3. You will find it on MetaLink as patch number 11674847. When compared to CP9, 10 bugs have been fixed.

Note: when you download the readme from MyOracle, from CP9 Oracle has placed the new bug fixes at the top of the list.

So far this cumulative patch has been released for the following platforms:

IBM AIX on POWER systems (64-bit)
Linux x86
Microsoft Windows 32-bit
Oracle Solaris on SPARC (64-bit)

If you are upgrading to CP10 from any patch level prior to CP4 then JDBC patch patch p4398431_10105_GENERIC.zip for bug 4398431(release 10.1.0.5) needs to be installed before you apply CP5.

Note: please take a look at the comments posted below and if anyone has any experience of CP10, good or bad, please let me know.

CP9 for Discoverer 10.1.2.3

2011-05-12T14:28:00.000-05:00

Just wanted to let you know that on January 11, 2011, Oracle has released CP8 for 10.1.2.3. You will find it on MetaLink as patch number 10233659. When compared to CP8, 6 bugs have been fixed.

Note: when you download the readme from MyOracle, from this release Oracle has started to place the new bug fixes at the top of the list.

So far this cumulative patch has been released for the following platforms:

HP-UX Itanium
HP-UX PA-RISC (64-bit)
IBM AIX on Power Systems (64-bit)
Linux x86
Microsoft Windows 32-bit
Oracle Solaris on SPARC (64-bit)

If you are upgrading to CP9 from any patch level prior to CP4 then JDBC patch patch p4398431_10105_GENERIC.zip for bug 4398431(release 10.1.0.5) 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.
Bug 4398431 - HANG WHEN RETRIEVING A CONNECTION FROM THE IMPLICIT CONNECTION CACHE

CP2 for Discoverer 11g released

2011-05-12T14:16:00.000-05:00

Just wanted to let you know that on January 11, 2011, Oracle released CP2 for Discoverer 11.1.1.2.0. This is applicable for both Discoverer Plus and Viewer. You will find it on My Oracle Support (formerly MetaLink) as patch number 10409451. There are 5 bugs fixed in this cumulative patch.

So far this cumulative patch has been released for the following 5 platforms:

Linus x86
Linux x86-64 bit
Microsoft Windows (32-bit)
Microsoft Windows x64 (64-bit)
Oracle Solaris on SPARC (64-bit)

Configuring Discoverer Plus to pre-populate login credentials

2011-05-12T13:20:00.000-05:00

Have you ever noticed how Discoverer does not remember your user name, database and EUL whenever you log out and wished there was a way to make it do so?

Well, there is a way but you need to add some parameters to your URL to make it do so.

Let's assume the following:

User Name is michael
Database is prod
EUL is eul5_us

All you need to do is to add switches to your URL and then save it in your favorites. The switches you need are:

For User Name use us=
For Database use database=
For EUL use eul=

Putting this altogether I can use: http://myserver.com:7779/discoverer/plus?us=michael&database=prod&eul=eul5_us

If you are using E-Business Suite you can also pre-populate this setting too by adding: lm=applications, like this:

http://myserver.com:7779/discoverer/plus?lm=applications&us=michael&database=prod&eul=eul5_us

Running Plus in IE8

2011-05-12T13:15:00.000-05:00

If you are experiencing issues running Discoverer Plus inside Microsoft IE8 the following comments may help.

So far, I have noticed that under no circumstances with Discoverer run in IE8 when it is configured to use JInitiator. If your company has enabled Discoverer to run primarily using JInitiator try adding the following parameter to your URL: _jvm_name =sun

Your URL should look something like this: http://myserver.com:7778/discoverer/plus?_jvm_name=sun

Now all this assumes that your Discoverer administrator has enabled a more recent Sun Java than Discoverer comes installed with, namely 1.4.0_06

Should you find that you have this version installed please upgrade the server Java and try again.

CP8 for 10.1.2.3 released

2010-10-26T03:35:00.000-05:00

Just wanted to let you know that on October 5, 2010, Oracle has released CP8 for 10.1.2.3. You will find it on MetaLink as patch number 9694503. When compared to CP7, 10 bugs have been fixed.

So far this cumulative patch has been released for the following platforms:

IBM AIX on Power Systems (64-bit)
Microsoft Windows 32-bit
Linux x86 (works for both 32 bit and 64 bit)
Oracle Solaris on SPARC (64-bit)
Oracle Solaris on x86 (32-bit)

If you are upgrading to CP8 from any patch level prior to CP4 then JDBC patch patch p4398431_10105_GENERIC.zip for bug 4398431(release 10.1.0.5) 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.

Bug 4398431 - HANG WHEN RETRIEVING A CONNECTION FROM THE IMPLICIT CONNECTION CACHE

The following posting has been updated:

Useful Patch Numbers

CP7 for 10.1.2.3 released

2010-07-26T00:34:00.001-05:00

Just wanted to let you know that on June 4, 2010, Oracle has released CP7 for 10.1.2.3. You will find it on MetaLink as patch number 9112482. When compared to CP6, 9 bugs have been fixed.

So far this cumulative patch has been released for the following platforms:

HP-UX PA-RISC (64-bit)
Microsoft Windows 32-bit
Linux x86 (works for both 32 bit and 64 bit)
Oracle Solaris on SPARC (64-bit)

If you are upgrading to CP6 from any patch level prior to CP4 then JDBC patch patch p4398431_10105_GENERIC.zip for bug 4398431(release 10.1.0.5) 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.

Bug 4398431 - HANG WHEN RETRIEVING A CONNECTION FROM THE IMPLICIT CONNECTION CACHE

The following posting has been updated:

Useful Patch Numbers

CP6 for 10.1.2.3 released

2010-07-26T00:31:00.001-05:00

Just wanted to let you know that on November 18, 2009, Oracle has released CP6 for 10.1.2.3. You will find it on MetaLink as patch number 8746296:. When compared to CP5, 19 enhancements or bugs have been fixed.

So far this cumulative patch has been released for the following platforms:

HP-UX Itanium
HP-UX PA-RISC (64-bit)
IBM AIX on POWER Systems (64-bit)
Microsoft Windows 32-bit
Linux x86 (works for both 32 bit and 64 bit)
Sun Solaris SPARC (32-bit)

Useful Patch Numbers

Be inspired today

2010-06-21T00:11:00.000-05:00

This truly inspirational clip makes me proud to hail from Liverpool. I hope you enjoy it and if you do please pass it on and make someones's day.

Be inspired and go on and inspire someone else.

Support for Discoverer

2010-06-18T11:59:00.000-05:00

As you probably know, if you are using Discoverer 3 or Discoverer 4 Oracle no longer provides support for these. I've seen a lot of folks recently who are still on these older versions and who are now out of support.

I wanted to let you know that I am able to offer remote support using gotomeeting for troubleshooting issues. I can connect to your system and help diagnose problems. In most cases we can get issues resolved within an hour.

Obviously there is a fee for this service but if you are really stuck and need help who you going to turn to?

Sending me an email is probably the simplest method of getting started. Click here to send an email.

Corporate Dashboards

2010-05-28T12:43:00.000-05:00

During a recent consulting engagement I was asked about dashboards and where one should begin when the boss comes in and says I want a dashboard. I decided what I needed to do was step back and look at the dashboard concept, then explain my understanding in simple terms. I share those thoughts here and invite your comments.

Dashboards are unique to an organization and what works in one place will not be suitable in another. But of course, it all depends on your definition of a dashboard. The one that I like and the one that keeps me out of mischief is this one:

A dashboard or dash board is a panel located under the windscreen containing indicators and dials such as the tachometer / speedometer and odometer. I bet you never thought it was so easy.

Seriously, look again at this definition and you will see the foundations of business dashboards. It is not the dials such as the tachometer, odometer and fuel gauge that are important. It is not the numbers either.

What is really important is the meaning or significance (aka the KPI) that is applied to the numbers. Thus, depending upon the situation, a speed of 100 mph might be considered excessive, particularly if being chased by an irate police officer down a busy city street. Do the same thing on a race track and you might be considered a menace for going too slow. But do 100 mph on an autobahn in Germany and no-one will bat an eyelid because it is perfectly acceptable. You can see that the gauge, in this case the speedometer and the 100 mph reading, is by itself meaningless as a KPI. It is only when you apply the criteria which states that 100 mph must be highlighted in red because it is excessive that a real KPI is born.

The concepts of dashboards in automobiles and in business are the same - they give us a snapshot of critical information at a moment in time. If you happen to be running out of fuel the dashboard will bring this fact to your attention. It does this by turning on a light or sounding a bell when a certain low point in the fuel tank is reached.

The vehicle dashboard needs to provide enough pertinent information so that informed decisions can be made as to how the vehicle is functioning.

Business dashboards need to provide enough pertinent information to the manager or executive so that they can make informed decisions as to how the department or company is functioning. Just like with a vehicle, a corporate dashboard needs to provide all of the critical information that is needed to run the organization's daily operations.

Most corporate dashboards are a snapshot in time, typically midnight, that tell an organization if it is spending cash too fast; or whether the percentage of patients who needed a repeat visit is higher than 5%; or whether the number of requests for service this week exceeded the number from last week by more than 10%. The common factor here is that a rule is being applied to the data to indicate that something needs to be brought to someone's attention.

In a business, you can imagine that every employee has a steering wheel and an accelerator pedal. However, it is not necessary that everyone gets the same dashboard. Since the user roles are different not everyone needs the same level and kind of information. The worker bees need to work, the managers need to manage, and the executives need to improve their golf handicap. Typically, higher executives want to manage by exception and will only become really interested when something out of the ordinary happens.

If an organization is truly managing by exception then it should have a goal to move routine work from the manager to the employee, thus leaving the manager more time to manage. By creating a dashboard that displays the KPIs that the manager is interested in, a quick glance to see that all is green is all that is needed. Good KPIs, and thus good dashboards, reduce micromanagement which is good for everyone involved.

Now that reminds me, golf anyone!

Traditional vs OLAP

2010-05-21T23:04:00.002-05:00

I have been following a very interesting thread on LinkedIn in the group called Data Warehouse & Business Intelligence Architects. The thread is discussing the pros and cons of OLAP as compared to more traditional methods of modeling. Personally I love these discussions. Here's what I recently said:

For me, probably an oldie in terms of these discussions, I have been working with modeling and data warehouses coming up on 25 years. I find it very, very strange that for some reason the term OLAP gets pushed around as if it is the answer to everything. This is probably being unfair to the technique because it's actually been around in one form or another a lot longer than most people realise.

Long before the term was invented or, more to the point shall we say, the technique was discovered, documented and given a formal name, we have been able to model enormous data warehouses with enormous amounts of data. Databases with terabytes of data are not new.

If I'm following the thread correctly I see two schools of thought, one pushing OLAP as the bees' knees and one pushing relational modeling. As someone who entered this field not too many years after Dr. Edgar Codd was first touting his ideas to IBM I can tell you that if a relational model is done correct with the right partitions, indexes and joins I can design a data warehouse using traditional methods for far less money than most folks would have you believe it should cost.

I'm somewhat of a historian and I actually have in my possession a set of Dr. Codd's early drafts. It makes for fascinating reading. So to anyone who is not sown on the idea yet I would urge you to read one of the many good books on the subject. You can do no worse than start with one of Ralph Kimball's books but you might also want to look at Bill Inmon.

Personally, I don't adhere strictly to any of the father's of data warehousing. I have read them all and I mix and match as the situation arises replete with a little tangential leap from time to time, sometimes of faith but mostly based on experience. Oh yes, and occasionally I mix them all, you know, just for fun because, after all, this is a beautiful world and we are in a beautiful profession and we have beautiful problems to solve.

So, what do you think? Are you a purist, a traditionalist or a modernist, somewhere in between or an amalgum of all three?

New Discoverer Books

2010-05-18T20:08:00.000-05:00

I thought I would let you know that McGraw-Hill may well be interested in doing 2 extra versions of our best selling Discoverer book. As you know, the current book is on version 10g and incorporates both end user and administration. We are going to separate these out into a brand new Oracle Discoverer 11g Administration Handbook and a smaller one for end users as a sort of tutorial for getting to know the tool. There is still demand for material on Discoverer and now, following the release of 11g, I believe would be a good time to bring our current book up to date.

The end user book will basically take our end user training, extend it and convert it into book format. The bulk of this material already exists so is almost written.

The main book that I will be working on the 11g Administration Handbook and I wanted to get your thoughts.

As a launch point I will be taking the original book and stripping out everything to do with end users leaving just the administration chapters. Then I am going to add brand new material. The topics I definitely want to include are:

Managing PL/SQL functions – nothing on this in original book
Java command line – again nothing on this in the original book
Interfacing Discoverer with BI Publisher
Application Server Management using Weblogic – one, maybe two chapters on this
Interfacing with Oracle E-Business Suite

I’m also thinking about adding a chapter on what’s next for Discoverer with a discussion about upgrading to OBI EE and perhaps even covering the Discoverer to OBI EE migration mechanism in some detail.

I'd like to get your input. From the administrators point of view, what would you like to see covered in such a book? Do you have any thoughts as to new material that should be covered?

If so, please contact me via email

Flooding in Tennessee

2010-05-02T22:54:00.001-05:00

As many of you will be aware there has been unprecedented and extensive flooding throughout Western and Central Tennessee this weekend. My home town is Cookeville which lies about 75 miles to the east of Nashville which as you know is one of the worst hit areas with well over 14 inches of rain in the last 48 hours. To everyone who has asked after me and my family I just want to say thank you and to let you know that we are safe. Even though there is water all around the area with trees down and rivers over their banks our property, because it is at a higher elevation than most, is safe.

Unfortunately, the same cannot be said for the rest of the state. Not so very far away there are lots of houses under water and I know that my home state is being devastated even as I write. For anyone who has ever been here you will know that this is one of the most beautiful parts of the United States which makes it even harder to take. While Tennessee may not be the richest state in the union the people here are hard working, God loving, gentle folk who didn't need this.

If you have the opportunity to donate anything to a relief effort, should one be organized, please do so. At the very least, please keep the people in this area in your thoughts and prayers as you go to sleep tonight.

NVARCHAR2 versus VARCHAR2

2010-04-29T15:02:00.000-05:00

I've been coming across more and more databases using NVARCHAR2 instead of the more usual VARCHAR2 data type and found some issues.

First, I had issues inserting data from an NVARCHAR2 column into a table where the corresponding column in the other table has a data type of VARCHAR2.

Second, I had an issue joining tables together where the same column was defined with a different data type, one being NVARCHAR2 and one being VARCHAR2.

Here is the way I solved these issues:

Inserting NVARCHAR2 into VARCHAR2
If you try inserting data from an NVARCHAR2 column into a table where the corresponding column in the receiving table is defined as VARCHAR2 you will get a character set mismatch error. You will need to use the TRANSLATE USING command, like this:

TRANSLATE(nvarchar2 USING TABLE1.NVARCHAR2_COLUMN) INTO VARCHAR2_COLUMN

Joining NVARCHAR2 to VARCHAR2
If you try creating a join using

WHERE TABLE1.NVARCHAR2_COLUMN = TABLE2.VARCHAR2_COLUMN

you will get an error. You will need to do this:

WHERE TO_CHAR(TABLE1.NVARCHAR2_COLUMN) = TABLE2.VARCHAR_COLUMN

Group Sort will not sort

2010-03-25T01:20:00.000-05:00

Problem Description
Have you ever encountered a tabular worksheet that when you tell it to Group Sort an item that it doesn't display as a Group Sort even though in the Sort list Discoverer says it is a Group Sort? If so, I have the reason and a solution.

Background
Let's say you have a worksheet that has the following 3 fields:

Department Name
Supervisor Name
Employee Name

And let's say we have two optional parameters:

Department Name
Supervisor Name

This worksheet simply lists who the employees are within a department along with the associated supervisor. The parameters will let you pick departments or supervisors or both at the same time or none due to the optionality of the parameter.

Now, clone this worksheet to another one to add the projects that an employee is working on. You will now get many instances of the employee. The fields in this worksheet are:

Department Name
Supervisor Name
Employee Name
Project Name

In this worksheet, the parameters are, again optional,:

Department Name
Supervisor Name
Employee Name

Now, if you return to the first worksheet and create a Drill Link on the Department Name to the second worksheet you will be asked to provide a value for the 3 parameters in the second worksheet. So far so good.

Next, try and add a Group Sort to the Department Name in the first worksheet. It will not display as a Group Sort?

Why do you think this is?
The answer lies in the fact that you will be passing values from below the Group Sort to parameters in a linked drill.

If the Department Name was Group Sorted you would not be able to click in any row other than the first instance of the department. Discoverer understands that this cannot be right and so does not display Group Sorted items as a Group Sort when lower level items are needed in a drill.

Solution
The solution is to either remove the Group Sort or place the drill to link on one of the lower non-Group Sorted items.

Corporate E-Learning

2010-03-18T23:14:00.000-05:00

We at Armstrong-Smith Consulting are pleased to be able to offer corporate E-Learning for Discoverer.
When you purchase our corporate E-Learning package for Discoverer.

Here is what you get: Click here for more information

You will get a site license to use both our Discoverer Administration and Discoverer End User classes for 2 years
You will get two installation CDs, one containing the 13 Administration and a second containing the 17 End User interactive video modules
You will get a PDF copy of both of the full Armstrong-Smith Consulting manuals
You will get a copy of the Armstrong-Smith Consulting database schema along with full instructions for installing onto your database
You will get the full Armstrong-Smith EUL complete with test workbooks
You will be authorised to install the training modules onto any number of files servers within your corporation
You will be licensed to use the material in either course any number of times over the 2 year period
Your materials will be sent free of shipping and handling to any location in the world

Are there any Caveats or Restrictions?
The only caveats and restriction that we have are:

You must own the relevant Discoverer licenses from Oracle
You may not use this material to give training to anyone outside of your company without express permission from ASC
You may not use this material in a country to which the United States has prohibited export

Click here for more information

Discoverer E-Learning Offer Extended Until Further Notice

2010-01-28T12:51:00.000-06:00

Due to the great response we had from folks purchasing our Discoverer E-Learning software for Administrators and End Users we are delighted to let you know that we have extended the offer until further notice. Interested in buying? Click here.

How much does it cost?
We will let you have our E-Learning courses for the following amazing prices:

Discoverer Administration course only $99.00
Discoverer End User course only $199.00
Buy both course for only $249.00

This is the offer of a lifetime 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 your training. Here is what you get:

You get the 13 Administration or 17 End User interactive video modules
You get a PDF copy of the full Armstrong-Smith Consulting manual
You get a copy of the Armstrong-Smith Consulting database schema along with full instructions for installing onto your database
You get the full Armstrong-Smith EUL complete with test workbooks

How do you buy?

To purchase this training please click here. You will be able to pay either by credit card or via PayPal.

Useful Patch Numbers posting updated

2010-01-28T12:34:00.000-06:00

I have today updated my Useful Patch Numbers posting with full details for 10.1.2.3 CP6

Click here for more information

There's Nothing like NULLs for Confusion

2010-01-01T15:31:00.002-06:00

Happy New Year everyone. Here is a cautionary note about using NULLs and the 'NULL' string with Discoverer. If you want to select rows containing NULLs you need to use the condition IS NULL. However, this causes a problem if you want to select null values using a parameter because the condition column=:parameter would not return any rows if the parameter was NULL.

Before 10gR2, Discoverer did not handle selecting null values from a parameter so you needed to convert the NULL values into a NULL string and include the the NULL string in the list of values. So for example, if you had a column that contained the values 'YES', 'NO' and NULL you would create a list of values that returned the strings 'YES', 'NO' and 'NULL' then use a condition NVL(column, 'NULL') = :parameter. So when you wanted to select rows where the column contained 'YES' or was NULL, you selected 'YES' and 'NULL' from the list of values and Discoverer generated the condition NVL(column, 'NULL') IN ('YES', 'NULL') returning the correct rows (assuming your column did not actually contain any 'NULL' strings).

So far so good, but this changed with Discoverer Plus 10gR2. In this release Discoverer tried to deal with the NULL value in the list of values. So if you had 'NULL' in the list of values it would create an IS NULL condition. So in the above example you could use the conditioncolumn = :parameter and if you selected 'YES' and 'NULL' from the list of values Discoverer would generate the condition column IN ('YES') OR column IS NULL.

Great, but this release would change the 'NULL' string to NULL anywhere it was entered into a condition. So for example, if you entered the condition column = 'NULL' it would change it to column IS NULL. Also if you had the condition NVL(column, 'NULL') = 'NULL' it would change it to NVL(column,NULL) IS NULL. This would give you unexpected results and to add to the confusion, Discoverer Desktop continues to work as before so the results would be different from the Desktop edition.

However, the confusion is compounded in Discoverer Plus 10gR2 CP6. In this consolidated patchset the functionality changes yet again. Discoverer Plus nolonger converts the condition column='NULL' so Plus now returns the correct results. However it stores the condition internally as column = '''NULL''' and when you open and run the workbook in Discoverer Desktop the condition column = '''NULL''' is used so again you get different results. There are more differences when you create the conditions in Desktop and open the same workbook in Plus.

One would hope that with a product with the longevity of Discoverer the basic functionality of the product would be stable, but NULLs are nothing and nothing changes like 'NULL's.

Is it Friday yet?

2009-11-03T21:36:00.000-06:00

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.

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

2009-10-27T02:21:00.004-05:00

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, clic k 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:

You get the 13 Administration or 17 End User interactive video modules
You get a PDF copy of the full Armstrong-Smith Consulting manual
You get a copy of the Armstrong-Smith Consulting database schema along with full instructions for installing onto your database
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.

Release and patch numbers for all Discoverer versions - October 2010

2009-10-21T01:20:00.012-05:00

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 (10.1.2.0.2) - 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. 10.1.2.54.25 (installed using patch 4960210) is the version number for 10.1.2.2, while 10.1.2.55.26 (installed using patch 5983622) is the version number for 10.1.2.3. Ideally you should be using 10.1.2.3.

As for Microsoft IE certification, 10.1.2.2 is certified for use only with IE6, while 10.1.2.3 is certified for use with both IE6 and IE7.

10.1.2.3 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 10.1.2.2 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 10.1.2.0.2 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 (10.1.2.0.0) - The terminal release is 10.1.2.45.46c. You can upgrade to 10.1.2.48.18 by applying the 10.1.2.1 patchset which will bring you to the 10.1.2.0.2 code level. No ECS or ES dates have been announced for 10.1.2.0.0. Please so not use this release as it is very buggy. You need to upgrade to 10.1.2.3 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 9.0.4.46.00 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 9.0.2.39. The terminal release was 9.0.2.54.10. Patch 3079459, for Administrator and Desktop, which upgraded the software to 9.0.2.54.10, 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 9.0.2.54.10. 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 10.1.2.0.2 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 9.0.2.53, 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 (10.1.2.0.2) 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 4.1.48.08. When installed from CD, the release was 4.1.37. It is interesting to note that 4.1.48.08 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 4.1.48.06 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 4.1.48.06. The required E-Business Suite release is 4.1.48.08 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 10.1.2.0.2 as soon as you can. The final release for 4.x not running against E-Business Suite was 4.1.48.06. 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 3.3.62.08.

You should migrate to 10.1.2.0.2 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 4.1.48.06 or 4.1.48.08. 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.

Footnotes:
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.

Vista installer available for Discoverer 10g Administrator and Desktop

2009-10-12T01:12:00.004-05:00

As many of you have already found out, if you attempt to install the base 10.1.2.0.2 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:

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

Become a follower of my blog

2009-10-03T01:39:00.000-05:00

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

2009-10-03T01:26:00.002-05:00

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.

Values Based Skills and Success Traning Blog

2009-09-26T12:23:00.000-05:00

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

2009-09-26T10:47:00.003-05:00

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.

Join my LinkedIn group

2009-09-10T01:36:00.002-05:00

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)

Supported Java Versions

2009-09-02T23:54:00.002-05:00

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 1.3.1.18 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

2009-09-02T23:38:00.002-05:00

Over the past year of so, several of my clients have reported a slowing down of Discoverer Plus 10.1.2 (10.1.2.0.2, 10.1.2.2 and 10.1.2.3) 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:

Ensure the external facing IP address of the Discoverer server has a reverse DNS entry in the DNS server, or
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

2009-09-02T18:23:00.004-05:00

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:

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.
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
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.

CP5 for 10.1.2.3 released

2009-07-27T11:49:00.003-05:00

Just wanted to let you know that Oracle has just released CP5 for 10.1.2.3. 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 10.1.0.5) 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.

Bug 4398431 - HANG WHEN RETRIEVING A CONNECTION FROM THE IMPLICIT CONNECTION CACHE

The following posting has been updated:

Useful Patch Numbers

Getting virtually there with Discoverer

2009-07-14T15:46:00.002-05:00

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:

Download and install VMWare workstation (http://www.vmware.com/download/ws/) which you can get on a one month trial.
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.
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.
Install the VM tools which is an addition Linux package supplied with the VM workstation.
Add the ip address and hostname in the /etc/hosts file.
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.
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
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.
Use VM Workstation to create a windows shared drive.
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.
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.
Download the Discoverer tools software for windows (Admin and Desktop) and install on Windows Vista.
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

2009-07-14T10:37:00.003-05:00

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.

Keeping it simple

2009-07-11T01:44:00.002-05:00

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.

Discoverer 10.1.2.3 CP4 is available again

2009-04-30T21:02:00.006-05:00

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 10.1.2.2 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.

Michael presenting at ODTUG Kaleidoscope

2009-04-28T16:25:00.009-05:00

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

2009-04-28T16:19:00.003-05:00

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 10.1.2.3

2009-04-28T10:28:00.008-05:00

I am very pleased to announce that Oracle have released the eagerly awaited cumulative patch 4 for Discoverer 10.1.2.3 (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 10.1.0.5).This patch needs to be applied to all Oracle Homes, i.e. Infrastructure home as well as all related midtier homes.

Bug 4398431 - HANG WHEN RETRIEVING A CONNECTION FROM THE IMPLICIT CONNECTION CACHE

Then proceed with applying the current patch.

The following postings have been updated:

Oracle buys Sun

2009-04-21T10:52:00.005-05:00

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.

April 2009 Patch Levels for Discoverer

2009-04-20T11:36:00.008-05:00

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 (10.1.2.2 or 10.1.2.3) 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 4.1.48.08. 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 4.1.48.08. You should migrate to Discoverer 10g Release 2 (10.1.2.2 or 10.1.2.3) 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 (10.1.2.0.2 - now 10.1.2.2 or 10.1.2.3) 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 9.0.2.54.10. You are urged to upgrade to Discoverer 10g Release 2 (10.1.2.2 or 10.1.2.3) 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 9.0.4.46.00 (Application Server 9.0.4.3). 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 (10.1.2.0.0) - The terminal release is 10.1.2.45.46c. You can upgrade to 10.1.2.48.18 by applying the 10.1.2.1 patchset which will bring you to the 10.1.2.0.2 code level (see below). No ECS or ES dates have been announced for 10.1.2.0.0. The support notice for Discoverer 10g Release 2 is MetaLink document 329361.1.

Discoverer 10g Release 2 production (10.1.2.0.2) - 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 10.1.2.48.18. 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 10.1.2.2 (10.1.2.54.25) or 10.1.2.3 (10.1.2.55.26) 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

Be careful when upgrading to Java 1.6

2009-04-17T00:14:00.009-05:00

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">

Notes:
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.

No more cumulative patches for 10.1.2.2

2009-04-16T23:41:00.005-05:00

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 10.1.2.2 is the last one for that platform and that the platform currently being developed is 10.1.2.3.

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

If you have already installed 10.1.2.2 and any of the cumulative patches the good news is that 10.1.2.3 (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 10.1.2.3 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.

Clamping Down Discoverer

2009-03-14T16:44:00.004-05:00

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:

limit the CPU or I/O used by a session
lower the priority of session
terminate sessions that use too much resource
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:
RESOURCE_MANAGER_PLAN=SYSTEM_PLAN

Alternatively, you can activate the Resource Manager on the fly by entering:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'SYSTEM PLAN'

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:

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'DISCO_USER', 'LOW_GROUP');

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:

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM, 'dis51usr.exe', 'LOW_GROUP');

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:
PROD4.INSTANCE_GROUP=DISCO

2. Then in the Discoverer initialization procedure include the following statement:
EXECUTE IMMEDIATE 'ALTER SESSION SET PARALLEL_INSTANCE_GROUP = ''DISCO''';

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.

CREATE TEMPORARY TABLESPACE TEMP_DISCO TEMPFILE SIZE AUTOEXTEND OFF;

CREATE USER APPSDISCO IDENTIFIED BY DEFAULT TABLESPACE TEMPORARY TABLESPACE TEMP_DISCO ACCOUNT LOCK;

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
IS
BEGIN
IF SYS_CONTEXT('USERENV','MODULE') LIKE 'Disco%' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=APPSDISCO';
END IF;
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:

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

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.

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.

Conclusion

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.

Caching data for Discoverer

2009-02-25T15:54:00.005-06:00

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:

Before the user logs on
When the user logs on
On-the-fly when a report runs
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:

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.
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.
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.
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:

a database trigger that checks whether the current session is a Discoverer session
the Oracle Applications 11i ‘Initialization SQL Statement – Custom’ system profile can be used to run an initialization PL/SQL procedure for Discoverer sessions
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:

CREATE OR REPLACE CONTEXT DISCO USING DISCO_UTILITIES_PKG;

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)
LOOP
dbms_session.set_context('DISCO', xcon.context_name, xcon.context_value);
END LOOP;

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:

CREATE GLOBAL TEMPORARY TABLE PRODUCT_ID_TMP
( PRODUCT_ID NUMBER(10),
CONSTRAINT PRODUCT_ID_TMP PRIMARY KEY (PRODUCT_ID) ENABLE
) ON COMMIT DELETE ROWS;

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,
lookup_code1 IN VARCHAR2) RETURN 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:

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.
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.
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.
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.

Word of warning about 10.1.2.3 and CP3

2009-02-20T09:04:00.006-06:00

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 10.1.2.3 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 10.1.2.2 with any cumulative patch or on 10.1.2.3 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.

February Ask Michael

2009-02-03T11:26:00.005-06:00

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.

Cumulative Patch 3 for 10.1.2.3

2009-01-07T15:45:00.002-06:00

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

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:

Useful Patch Numbers

New Armstrong-Smith Consulting Web Site

2009-01-07T15:38:00.002-06:00

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.

Ask Michael in 2009

2008-12-23T14:13:00.002-06:00

Starting in 2009, I will be hosting a free monthly webinar for all aspects of business intelligence and data warehousing. Of course, because I am considered the expert in Discoverer, a large amount of the time will be devoted to answering questions on this hugely popular tool.

The first webinar is scheduled for January 14, 2009

There is plenty of time to submit a question and / or register for the webinar so click here.

A couple of says before the webinar I will contact you with the username and password that you will need for access.

Remember - the webinars are free but you must register with me

ASC Training in Tennessee

2008-12-23T14:02:00.002-06:00

Hello everyone
I want to let you know about an exciting new inititiave that my company is starting in 2009. Because we know that many companies only have one or two staff that need our training and cannot afford to have us come on site, we will be offering the same trainng at our own premises starting January.

The training is built around the Higher Education EUL that we developed for SunGard and their clients. If you are unsure whether this would be suitable for you please email me for more details.

Note: Armstrong-Smith Consulting has partnered with SunGard and are the sole authorized partner able to offer this training.

Click here for more information, for prices or to make a booking. By the way if you are a member of the Nashville or Oklahoma City Oracle User Groups we will give each attendeed a 10% discount on the training price and give a further $50 donation to your user group when you complete the training. A Win-Win if you ever heard one!

If you are interested in this training, click here for the Agendas

If you would like more information please drop me an email.

Happy Holidays to everyone

2008-12-09T11:24:00.004-06:00

Hello everyone. I just wanted to drop you all a line and say thank you to everyone who follows my blog and wish you all very best wishes for the upcoming holidays to you and your family wherever you are in the world. Most of you I know are in the United States but I have a lot of dedicated followers in other parts of the world in countries like the United Kingdom, India, Canada, Australia, Germany, Denmark, China, Kuwait, Saudi Arabia, Russia and dozens more.

If I knew how to send you best wishes in your language I would.

I also wanted to let you all know that I will be starting several new services in 2009 and invite you to email me with your name and email address so that I can let you know when the services will start.

Because many of you have limited budgets and unable to travel for training and help, we are setting up on-line initiatives. One of the most exciting new ventures will be the making available our superb Discoverer training courses to on-line subscribers. We will be offering both our popular Administrator and End User classes for you to follow. We will also have a dedicated trainer available via telephone and email should you have issues.

Discoverer courses:

Discoverer Administrator - 14 hours on-line
Discoverer End-User - 20 hours on-line

Further, and these have been asked for many times, we will be offering the following brand new on-line courses:

Other Courses:

An introduction to SQL - 8 hours on line
An introduction to Data Warehousing - 8 hours on line
How to maximize your business intelligence investment - 8 hours on-line

These are generic courses not aimed at a particular product and will be very popular.

We will be placing a form on-line in the coming weeks but until then please send me an email with your name and I will make sure you get on our distribution list for these courses. Sending me an email in no way ties you in to buying a course and I will not give your name or email address to anyone else for any purposes. You will only be contacted by Armstrong-Smith Consulting.

Metalink Note 304192.1 and contexts

2008-11-20T15:35:00.007-06:00

Update: This metalink note has now been discontinued. Note 282249.1 gives information on how to pass parameter to a folder using the session client_info. The client_info is a database context available to all sessions in the USERENV namespace. Advantages of using client_info instead of a custom context is that you don't need to create the new context namespace at the database level and also the value of the client_info context can be seen in the V$SESSION dynamic view. However, it can only be used for one parameter and the client_info is used by many applications including the e-Business Suite so you run the risk of overwriting data already stored in the client_info.

This metalink note describes how to pass parameters into a custom folder or database view using stored procedures. The note has been around for quite a while, but as I will explain, the method used will never work reliably in all situations. The note suggests that you use two functions held in a package to set and get a parameter. You use a condition in the view or custom folder to retrieve the parameter, for example:

WHERE ENAME=SETPARAM.GET_PARAM1

Then use another condition in the workbook to set the parameter. The function always returns 1 so is always true.

1=SET_PARAM1(:"Parameter 1")

This approach expects that the SET_PARAM1 function will be called first because it uses constants and so can be evaluated without referencing any tables. However, this will only work if the query in the view or custom folder is not too complex. Once an SQL statement has been sent to the database you have no control over the order in which stored procedures are called. With complex SQL statements, for example, using CONNECT BY, the GET_PARAM1 function will be called first and will return either no value or the last value used.

There is NO reliable way of passing parameters from Discoverer into a custom folder or view using a single SQL statement because the database can call the stored procedures in any order. So any parameter added to the query by Discoverer will probably not be processed before the main body of the query held in a custom folder or view.

Database contexts provide a more efficient and reliable alternative. Database contexts work like session parameters, once the context is set it is available to use for the duration of the session. You can set them or change them using a separate Discoverer worksheet.

To implement Discoverer session parameters you will first have to create a context namespace in the database for the Discoverer contexts.

CREATE OR REPLACE CONTEXT DISCO_CONTEXT
USING EUL_US.DISCO_PKG
/

Then you need to create a package to enable you to set and retrieve contexts from in Discoverer.

CREATE OR REPLACE PACKAGE EUL_US.DISCO_PKG
AS
FUNCTION set_context(p_name VARCHAR2,
p_value VARCHAR2) RETURN VARCHAR2;
FUNCTION show_context(p_name VARCHAR2) RETURN VARCHAR2;
END disco_pkg;
/

CREATE OR REPLACE PACKAGE BODY EUL_US.DISCO_PKG
AS
FUNCTION set_context(p_name VARCHAR2,
p_value VARCHAR2) RETURN VARCHAR2
IS
BEGIN
dbms_session.set_context('DISCO_CONTEXT', p_name, p_value);
RETURN p_value;
END set_context;

FUNCTION show_context(p_name VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN SYS_CONTEXT('DISCO_CONTEXT', p_name);
END show_context;

END disco_pkg;
/

Once you have imported the set_context and show_context functions into the Discoverer EUL you are ready to set and retrieve your session parameters.

With this approach you set your session parameters in a separate worksheet from your main reports. For example, the following steps show you how to create a worksheet to define an effective date parameter:

1. Create a workbook based on any folder containing an item of type date.
2. Create a parameter based on a date.

3. Create a calculation to call the set_context function and set the effective date.

4. Remove the contents from the folder so that the folder just contains a parameter.
5. It is also useful to create another calculation retrieving the value of the parameter, so that the value entered for the parameter can be seen when the workbook is run.

The value of the context in a custom folder or view can be retrieved using a condition, for example:
TO_DATE(SYS_CONTEXT('DISCO_CONTEXT','EFFECTIVE_DATE'))

Every EUL of any complexity should use database contexts:

1. Using SYS_CONTEXT gives better performance. SYS_CONTEXT is an SQL function and therefore much quicker than using calls to PL/SQL.

2. SYS_CONTEXT can be used in any code; in views, PL/SQL code and custom folders. The function can also in the EUL and workbooks; you can import the function into the EUL (from the SYS schema) and use it instead of the SHOW_CONTEXT function. SYS_CONTEXT will always return the current value of the parameter for the session.

3. You can store all your parameters in the same database namespace, so there is no need to change any code if there is a need extra parameters.

4. You can set the database contexts to default values using a login trigger. Then the users only have to run the parameter worksheet if they need to change a session parameter. You can even store the last contexts used in a table, so that a new Discoverer session picks up the values used in a previous session.

However, one limitation of this technique is that Discoverer caches the results of some SQL such as the lists of values and so changing a session parameter will not change a list of values.

Discoverer 10.1.2.3 certified for use with E-Business Suite

2008-11-19T12:03:00.003-06:00

I am very pleased to announce that Discoverer 10.1.2.3 is now certified against both E-Business Suite 11i and 12i. You must however have applied at least cumulative patch 1 as the base version which you get following an upgrade is not certifed.

E-Business Suite Versions
For more information concerning Discoverer 10.1.2.2 and 10.1.2.3 and E-Business Suite 11i please look at MetaLink note number: 313418.1

For more information concerning Discoverer 10.1.2.2 and 10.1.2.3 and E-Business Suite 12i please look at MetaLink note number: 373634.1

I have also updated my master blog posting on Discoverer releases: http://learndiscoverer.blogspot.com/2008/04/most-useful-patch-numbers.html

Row Generators

2008-10-23T07:11:00.006-05:00

Thank you Michael for inviting me to contribute to your blog. I thought I would start off by talking about row generators and how to use them with Discoverer.

A folder that you can use as a row generator is always useful to have in an EUL. A row generator is just a folder that always returns a fixed number of rows. You can define a row generator either by using a database view or by creating a custom folder as shown below. There is a dummy item included in this row generator so that the folder can be joined to other folders in the EUL.

Lists of Values

There are many uses for a row generator but they are often most useful when creating a list of values (LOV) where there are a fixed number of values, for example, a Yes/No list of values. To create you LOV first create a new (complex) folder, then drag the N item from the row generator folder into the new folder. You then need to create a mandatory condition in the new folder to restrict the number of rows, for example, in this case N<=2. You can then create a calculated item to return the text used in the LOV. You can use DECODE to convert the number into a text string as shown below.

Then create a LOV item class based on the calculated item so that you have a folder that returns the required values as shown below.

Of course this is a very simple example. But in general you will find it easier to have one row generator folder and have complex folders for each LOV. It is easier to use a CASE or DECODE expression than create a new custom folder for each LOV.

Other useful examples are:

List of Values	Calculation	Condition
Days of the week	TO_CHAR(TRUNC(SYSDATE, 'D')+n-1, 'Day')	N<=7
Previous 12 months	ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 1-n)	N<=12
Letters of the alphabet	CHR(65 + n-1)	N<=26

This last example where you have a LOV containing the letters of the alphabet is useful when you want to show an index on a text field, for example, bookname. You need to create a bookname_char calculated item, UPPER(SUBSTR(bookname,1,1)) and put this in the alphabet item class. You then need to create a hierarchy from the bookname_char item to the bookname item which will let you select the first letter of the bookname and then drill down to see all names beginning with this letter.

Fixing the number of columns

The row generator is also useful if you need to have a fixed number of columns in a crosstab report. For example you wanted 12 columns, one for each month in the year summarising employee earnings. Now, there will be some employees who do not have earnings for all months in the year. When the report is run for these employees there will be less than 12 columns. If you create a LOV folder for the months you need as described above and outer join the LOV folder to the report folder then you will always get a fixed number of columns in your report.

Pivoting

A row generator can be used to pivot a table or part of table. This is where you want to show values that are in different columns on separate rows.

For example, if your employee table has columns for the hours worked on each day in the week and you need a report showing the total hours on each day of the week by department. So in this case you need to pivot the hours onto separate rows. You do this by creating a days of the week folder that always returns 7 rows as described above. Make sure you also include the dummy item from the row generator folder. Then create a dummy item in your employee folder as shown below.

You can then join the employee table to the days of the week folder using the dummy item. Then in the workbook create a calculation to show the total hours for any day using:

SUM(DECODE("Row Generator 100".N,
1, Employees.mon_hrs,
2, Employees.tue_hrs,
3, Employees.wed_hrs,
4, Employees.thu_hrs,
5, Employees.fri_hrs,
6, Employees.sat_hrs,
7, Employees.sum_hrs))

You can then select the day from the days of the week folder, the department from the employees folder and the hours calculated item to get the sum of the hours for each day of the week.

Counting records many times

The row generator can be used to multiply rows when you want to count the rows several times.

For example, if you have a report that shows when users logged on and off your system but you want a chart showing the how many users are logged on during each hour during the day. This means that if there is a record showing user A logged on at 8am and logged off at 11:50am then this record needs to be counted 4 times, once for each hour the user was logged on.

You need to create an hours row generator folder as described above that returns the 24 hours in the day using an hours calculation shown below :

TO_CHAR(n-1,'fm09')

Now if the user logon and logoff times were held in a user_timings folder then you need to join the hours row generator folder to this folder using a dummy item as described in the previous section. This will multiple each row 24 times. Then a condition in the workbook can be used to return only the hours when the user was logged onto the system:

Hours BETWEEN TO_CHAR(TRUNC(logon, 'HH24'), 'HH24') AND TO_CHAR(TRUNC(logoff, 'HH24'), 'HH24')

You can then count the records and group sort on the hours calculation to return the data you need for the chart.

This of course assumes that all users log on and off on the same day. You would need a slightly more complex condition if users where able to logon and off on a different day.

Pictures of Darlene and Michael

2008-10-21T10:07:00.009-05:00

Many of you know Darlene, my wife, and I as the owners of Armstrong-Smith Consulting and the authors of the Oracle Discoverer Handbook. Here are some photographs of us taken in our offices recently:

Interesting condrum with indexed values

2008-10-16T10:51:00.005-05:00

As you know from a previous posting it is possible to create Discoverer lists of values to display a lookup description e.g. Department Name and return the primary key code namely, Department Id when using a list of values in a parameter.

What I have discovered today is something very intersting in the way that Discoverer does this and something that you ought to be aware of.

Let's say a bottle manufacturing company has a whole series of bottles which they describe using the color of the top. Some descriptions would be 1 Litre Blue Top, some 1 Litre Red Top and so on. Let's also say that they start out with code 10 represting 1 Litre Blue Top, and code 11 representing 1 Litre Red Top.

Inside Discoverer Plus, if a user has an indexed item in use and they choose to select using the Values option (see my original posting if you are unsure what this looks like) they will be presented with a list of values like this:

(1 Litre Blue Top) 10
(1 Litre Red Top) 11

What you may not be aware of is what Discoverer submits to the database. This is what will get submitted:

(((o100539.PRODUCT_DESCRIPTION) = '1 Litre Blue Top'
AND o100539.PRODUCT_CODE = '10'))

Yes, Discoverer actually adds an AND clause for BOTH parts of the equation. It searches for both the Code and the Description. I can see why this is happening. This is to solve the following problem:

Let's say the bottling company later decide to introduce an updated version of the 1 Litre Blue Top and decide to use code 20. Because there are two items with the same description, if Discoverer were to only submit this:

(o100539.PRODUCT_DESCRIPTION) = '1 Litre Blue Top' )

then both items would be returned in the query. The only way to guarantee to get the right combination would be for Discoverer to add the AND clause.

Somebody recently asked me why Discoverer didn't use an OR connector, like this:

(((o100539.PRODUCT_DESCRIPTION) = '1 Litre Blue Top'
OR o100539.PRODUCT_CODE = '10'))

If Oracle were to change the code to an OR condition then we would definitely get multiple rows returned so Oracle's choice of using an AND clause seems to be correct.

So why then am I bringing this to your attention. I'll tell you why.

Let's say the bottling company decides to rename the description for bottle 10 to this 1 Litre Top - Blue. No big deal you might think because there is still only one description for that code - wrong!

Let's say a user opens a workbook containing a worksheet that has a such an indexed parameter and that the last time it was used either the code 10 was selected or it is being supplied as the default value. Because the parameter already has the value displayed on screen all the user needs to do is to accept the current value and click Finish. Because the user does not reselect 10 and just clicks Finish what gets submitted to the database will still be the original AND clause:

(((o100539.PRODUCT_DESCRIPTION) = '1 Litre Blue Top'
AND o100539.PRODUCT_CODE = '10'))

Yes, now you can see the conundrum. No data is returned because there is no product called 1 Litre Blue Top in the database. Discoverer does not reassess the situation when an existing parameter is used. Is this a bug? Maybe. I will send this to Oracle for comment but I thought you would like to know.

I think the workaround would be to pick a new paramater value from the list, then go back and repick the original value. Assuming the worksheet now returns data the worksheet needs to be resaved. Of course, this also assumes that the user using the worksheet is the worksheet owner. If they are not the owner then the problem will persist until the owner can be notified.

As I say - an interesting conundrum!

Welcome to Rod West

2008-10-14T16:24:00.005-05:00

I am delighted to be able to let you know that Rod West, a prolific answerer of questions on the OTN Discoverer Forum, will be joining me as a co-author on this blog.

Rod has been using Oracle databases since 1985 and is principal consultant at Cabot Consulting in the United Kingdom. He specializes in Oracle Applications 11i / 12i as well as Oracle Discoverer. Rod can be contacted at rodwest@cabotconsulting.co.uk

Rod has also submitted two white papers, both of which are available for immediate download from my website by clicking here

The papers are as follows:

Scheduling through concurrent manager - this paper describes how Oracle Applications Concurrent Processing can be used to schedule Discoverer workbooks
Using VPD to secure Discoverer reports - this article uses a series of examples to demonstrate how VPD can be used to secure Discoverer reports. The examples have been written with Discoverer in mind but VPD is a database centric approach and so can be applied to any reporting tool

Cumulative Patch 2 for 10.1.2.3

2008-10-06T16:24:00.002-05:00

I am very pleased to announce that Oracle have released a second cumulative patch for Discoverer 10.1.2.3.

So far this has been released for the following platform:
Windows 32-bit

The following postings have been updated:

Useful Patch Numbers
Do not upgrade to Discoverer 10.1.2.3

Important Note: Neither the base 10.1.2.3 nor any of its cumulative patches are certified for use against E-Business Suite 11i or 12i, so please don't upgrade to 10.1.2.3 if you are using or intend to use Discoverer in Apps mode. 10.1.2.2 and all of its cumulative patches are certified so this is where you should be, at least for the time being.

Be careful installing cumulative patches

2008-09-16T14:59:00.002-05:00

I can't stress the importance of making sure you read all of the instructions in the readme files that come with the cumulative patches. For example, the readme for cumulative patch 8 on Windows has the following statement concerning OPatch:

2. It is always recommended to have the lastest 1.0.0.0.xx opatch version.
Minimum opatch version is 1.0.0.0.57
Opatch version can be checked following the below steps:
- set ORACLE_HOME=
- set OPatch in the PATH
- opatch version

However, the same readme for the Linux install has this statement:

2. It is always recommended to have the lastest 1.0.0.0.xx opatch version.
Minimum opatch version is 1.0.0.0.58
Opatch version can be checked following the below steps:
- set ORACLE_HOME=
- set OPatch in the PATH
- opatch version

Notice that the minimum OPatch versions are different. This is very important.

Warning: If you attempt to install CP8 on Linux using OPatch 1.0.0.0.57 the install will fail and you will get a message saying that the inventory could not be updated.

Unfortunately, by this point the cumulative patch code will already have deleted some required files and so now even your original Discoverer will not work. The solution is to install the correct version of OPatch and rerun the cumulative patch. It will now put the right, required files in place and Discoverer Plus will operate correctly.

The moral of the story is to read the installation notes carefully so that you don't get caught out.

Revised Apps mode white paper

2008-09-05T13:31:00.003-05:00

As many of you already know I am the author of several white papers on Discoverer.

I am pleased to be able to let you know that I have today publised a fully revised version of the popular Setting up an Apps mode EUL.

You will find it on my downloads page.

It has the following 5 sections:

Installing the Admin software
Setting up Discoverer to work with E-Business Suite
Patching Discoverer Admin to the correct level
Creating an E-Business Suite End User Layer
Setting up E-Business Suite privileges

Cumulative Patch 8 addendum

2008-08-27T22:30:00.004-05:00

A few days ago on 15th August Oracle released a second edition of CP8. This edition, using patch number 7306816, is only for the following 3 platforms:

Linux x86
Sun Solaris SPARC 32-bit (which is also good for 64-bit)
HP-UX PA-RISC 64-bit

The reason for the updated patch is to fix some additional bugs specific to these platforms, namely:

BUG 6742626 - DISCOVERER DISCONNECT IF WE DRILL TO RELATED TO THE SAME ITEM TWICE
BUG 6933011 - 'AN ERROR OCCURRED WHILE ATTEMPTING TO PERFORM THE OPERATION' WHILE OPENING REPO
BUG 6686944 - FONT SIZE TOO SMALL EVEN IF ADJUSTPLUSFONTSIZE = "TRUE" AND ZOOM SET

If you are using Solaris and have already applied the initial patch then you should be fine, but since 7306816 has three more patch fixes, then this will be the one recommended by Oracle Support going forward if it is available for a specific platform. Personally, if I was on Solaris, I would apply 7306816 even if I had already applied the original 7111816. I like to have the latest fixes where possible.

As I say, it is currently released on Solaris, Linux and HP-UX.

The Windows platform will continue to use 7111816

See also:

My Original CP8 posting

My posting concerning patch numbers

By the way, if you are using E-Business Suite 11i or 12i then all of the cumulative patches for Discoverer 10.1.2.2 are certified for use. You do not need to wait for further confirmation by Oracle as this is part of the patch release testing.

However, neither Discoverer 10.1.2.3 nor its recently released CP1 are certified for use with E-Business Suite so do not upgrade to 10.1.2.3 at this time. 10.1.2.3 is a non-reversible upgrade so don't go there if you are using E-Business Suite. You have been warned!

Cumulative Patch 1 for 10.1.2.3

2008-08-15T09:22:00.003-05:00

I am very pleased to announce that Oracle have finally released a cumulative patch for Discoverer 10.1.2.3.

So far this has been released for the following 3 platforms:

Windows 32-bit
Linux x86
Sun Solaris SPARC (32-bit) - which means 64-bit as well

The following postings have been updated:

Important Note: Neither 10.1.2.3 or CP1 is certified for use against E-Business Suite 11i or 12i, so please don't upgrade to 10.1.2.3 if you are using or intend to use Discoverer in Apps mode. 10.1.2.2 and all of its cumulative patches are certified so this is where you should be, at least for the time being.

Cumulative Patch 8

2008-07-30T23:10:00.007-05:00

Posting updated: August 27, 2008

Oracle have recently released CP8 for Discoverer. The patch number for Windows is 7111816. The patch number for Linux, Unix and HP is 7306816. While 711816 was also released for Unix, this has subsequently been re-released with further improvements as 7306816. If you are running on Unix and have already applied 7111816 you should also apply 7306816. This will de-install the previous CP8 and install the latest version complete with additional bug fixes.

My blog posting relating to patch numbers has been updated to reflect this new cumulative patch.

For more information about patches for Discoverer click here.

At the moment CP8 is available for these platforms:

Microsoft Windows 32-bit - using 7111816
Sun Solaris SPARC 32-bit (which is also good for 64-bit) - using 7306816
Linux x86 - using 7306816
HP-UX PA-RISC 64-bit - again using 7306816

Some welcome fixes:

Welcome fix 1

Looking through the readme it seems as though Oracle may have fixed the CP7 issue that I reported a couple of weeks ago. I have tested this out on my own system and the list of values now populate with no problem.

Welcome fix 2

Another bug, this time one that has irritated end users for some time, has been fixed in this patch. Prior to this patch, if someone shared a workbook with you and you tried to Save As using the same name you would get an error that the workbook identifer must be unique. You would then be forced to save the workbook under a different name which was most inconvenient. I am very pleased to report that has been fixed in CP8.

Let's say that someone shares a workbook with you called Sharing Test.

Under the new functionality, if you use Save As and leave the workbook name the same, Discoverer will automatically assign a unique identifer to the workbook. What it does is to append the number 1 to the end of the identifier. Thus, in my example, the identifier for this workbook will become SHARING_TEST1. I know this fix will be welcome news to hundreds of users.

Good one Oracle - 10 out of 10 from me for this fix.

Welcome Fix 3

One other "fix" that may be welcome to you in CP8 is that changing Page Items in Discoverer Plus cross-tabular worksheets and scrolling has been incrementally improved. I'm not sure if you have ever noticed this as it generally only rears its ugly head when working with more complex type worksheets. Performance is still not "Desktop" speed due to the architecture differences, but it is much improved. The generic scroll properties that were introduced in CP2, I think, may no longer needed. If you have been having issues with those sort of "performance" issues, then you might want to give CP8 a try.

The particular bug fixes that I am referring to in CP8 are as follows (this information is taken from the CP8 readme):

328) Bug 7166233 - WITH SOME SCROLL PREFERENCES, TABULAR WORKBOOKS WILL LOCKUP WHEN SCROLLING

329) Bug 6938007 - CHANGING PAGE ITEMS IN PLUS RENDERS THE PAGE VERY SLOWLY

Speaking in Oklahoma in July

2008-07-16T10:01:00.003-05:00

If you happen to be in Oklahoma City on Tuesday, July 22nd I will be presenting a paper on manipulating dates in Discoverer at the Oklahoma City Oracle User Group meeting.

This will be at the Francis Tuttle Institute of Technology, Rockwell Campus, in Oklahoma City on 12777 N. Rockwell Ave at 5:15pm

For more information please look on the OKCOUG website

I'll see you there

Discoverer polls 1 and 2

2008-06-16T23:37:00.003-05:00

So that I can provide a better service to the industry, from time to time I will be conducting polls of the Discoverer commulity on various topics.

The results from the first 2 polls can be seen in the panel to the right. As you can see they make very interesting reading.

Monitoring and removing old statistics

2008-06-12T21:29:00.006-05:00

This is a posting for Discoverer administrators.

As you may know, there is a switch on the Privileges screen called Collect Query Statistics.

Most organizations leave this turned on. When it is turned on, every time an end user logs in and runs a worksheet, statistics about that run are collected into the table called EUL5_QPP_STATISTICS. Thus, there will be one row in the statistics tables for every execution of every worksheet in every workbook. Even if an end user creates only an ad-hoc query and doesn't save it to the database the statistics will still be captured.

If your organization has lots of users executing lots of worksheets every day you can imagine that this table will get quite large. Another thing that happens is that over time the extents on the indexes, of which there are 3, grow rapidly to many thousands. What this means is that performance is going downhill.

Here are the 3 indexes:

EUL5_QS1_I
EUL5_QS2_I
EUL5_QS_PK

You therefore want to consider purging your statistics from time to time. Many organizations that I have worked with like to keep at least the last six months of statistics, although some only keep 90 days.

What you may not be aware of is the fact that Oracle supply a SQL script that can check the statistics and, optionally, delete those which are over a number of days old. The script also provides a report on how many entries there are in the table, in buckets of 10 day increments, like this:

You can find the script here on your Admin machine:
[ORACLE_HOME]\discoverer\util

It is called: EULSTDEL.SQL

To run the script, use this workflow:

Log in to SQL Plus as the owner of the EUL
Type this @c:\oracle\bitoolshome_1\discoverer\util\eulstdel.sql and press Enter
You will be prompted to enter a parameter. This parameter is the number of days of statistics you want to keep. If you want to keep all of your statistics, enter a large number or just run the SQL at the end of this posting. If you enter 0 the script will delete all of your statistics because it actually deletes rows which have a created date less than SYSDATE minus this number. Therefore, be careful!
The SQL will run and produce the report, optionally deleting your old statistics

Note: in step 2 of the above workflow I have included the most-commonly user location for the Discoverer Admin tool. If yours is not in this location you will need to change this line.

Having deleted the statistics, you will more than likely want to ask your friendly DBA to rebuild the 3 indexes listed above, and again below. I have seen terrific improvements in performance when this has been done.

EUL5_QS1_I
EUL5_QS2_I
EUL5_QS_PK

By the way, if you just want to see your statistics, here is the SQL:

SELECT
TRUNC(SYSDATE-QS_CREATED_DATE,-1) Days_Old,
COUNT(*) No_of_stats
FROM EUL5_QPP_STATS
GROUP BY TRUNC(SYSDATE-QS_CREATED_DATE,-1)
ORDER BY 1 DESC

How many users

2008-06-12T20:45:00.004-05:00

From time to time I am asked to provide scripts that help administrators manage their Discoverer system. One such example is below.

I was asked if I knew of a way for a company to know how many users they had using Discoverer so that they can make sure they are compliant with their licensing. The following script will do just that, plus a little more:

SELECT DISTINCT
UPPER(QS.QS_DOC_OWNER) USERNAME,
COUNT(QS_ID)REPORTS_RUN,
MAX(QS.QS_CREATED_DATE) LAST_USED,
MIN(QS.QS_CREATED_DATE) FIRST_USED
FROM EUL5_QPP_STATS QS
WHERE QS.QS_CREATED_DATE > '01-JAN-2006' AND
QS_DOC_OWNER IS NOT NULL
GROUP BY UPPER(QS.QS_DOC_OWNER)
ORDER BY 1;

Note: there is a restriction to running this script in that it will only tell you about users who have their own workbooks and when they were last ran. If you are using Discoverer connections you will not be able to use these scripts because the workbook will not necessarily be owned by the user who owns the connection or, if using SSO, by the user who is logged in. I am still researching if there is a way to derive this information.

The date parameter is there to check users who have been on the system from that date. This way you can exclude reports that were run a long time ago. You can drop it altogether if you like. If you do this you will see the total number of users you have had running reports. Of course, this assumes that you are collecting query statistics and that you haven't purged them - which will be the subject of another posting.

The report will tell you who has run a report in Discoverer, how many reports the user has executed, when the user first logged in and when the user last logged in to run a report. Using this information you can determine how many licenses you need or just to see who are the most frequent Discoverer users on your system.

To accomplish the latter just add another clause. Let's say you only want to see users who have run at least 25 reports. All you need to do is this:

SELECT DISTINCT
UPPER(QS.QS_DOC_OWNER) USERNAME,
COUNT(QS_ID)REPORTS_RUN,
MAX(QS.QS_CREATED_DATE) LAST_USED,
MIN(QS.QS_CREATED_DATE) FIRST_USED
FROM EUL5_QPP_STATS QS
WHERE
QS.QS_CREATED_DATE > '01-JAN-2006' AND
QS.QS_DOC_OWNER IS NOT NULL
GROUP BY UPPER(QS.QS_DOC_OWNER)
HAVING COUNT(QS_ID) >= 25
ORDER BY 1;

Finally, let's say you want to extend the SQL again to include only users who have run a report within the last month yet still include users who have run at least 25 reports since 2006. This dynamic script will do that:

SELECT DISTINCT
UPPER(QS.QS_DOC_OWNER) USERNAME,
COUNT(QS_ID)REPORTS_RUN,
MAX(QS.QS_CREATED_DATE) LAST_USED,
MIN(QS.QS_CREATED_DATE) FIRST_USED
FROM EUL5_QPP_STATS QS
WHERE
QS.QS_CREATED_DATE > '01-JAN-2006' AND
QS_DOC_OWNER IS NOT NULL
GROUP BY UPPER(QS.QS_DOC_OWNER)
HAVING
COUNT(QS_ID) >= 25 AND
MAX(QS.QS_CREATED_DATE) >= TRUNC(SYSDATE,'MM')
ORDER BY 1;

LOVs in Plus following upgrade to CP7

2008-06-10T04:02:00.005-05:00

Update posted on 30th July 2008:
CP8, which was issued on 11th July 2008 has fixed this issue from CP7. Therefore if you have installed CP7 and CP8 or higher is available for your platform you should apply the most recent patch that you can. The CP8 patch, by the way, is 7111816. If you have not installed CP7 you should install CP8 or the most recent cumulative patch instead. Click here for more information regarding the patches you can install.

I hope you have been following along with my postings concerning staying up to date with cumulative patches. Following the application of CP7 you may find that your users complain that lists of values no longer expand in Discoverer Plus when they click the little plus key alongside an item in the Available Items pane.

A new preference setting controls the number of items that can be displayed. The new setting appears to be defaulting to 0, thus preventing any items from displaying in the list of values. If you have access to MetaLink take a look at document 601996.1. This is the readme for CP7 (patch 6778560). Scroll down to Special Instruction 17. It appears to indicate that the preference is only needed if you have more than 1000 items in the list of values.

I have found this not to be the case and that, certainly for all the Discoverer servers that I have upgraded, the preference always needs to be set, even if there are less than 1000 items in any of my lists of values.

If you are experiencing a loss of the list of values, to rememy this, do the following:

If Discoverer Plus is running, exit from Plus
If Discoverer middle tier OPMN is running, it needs to be stopped as follows:
a. Launch cmd window in Windows or connect to Unix / Linux as ORACLE user
b. Navigate to $ORACLE_HOME\opmn\bin
c. Type opmnctl stopall and press Enter
d. Leave this window open
Navigate to $ORACLE_HOME\discoverer\util
In Windows: ORACLE_HOME will usually be C:\Oracle\BIHome1
Edit the file called pref.txt – it may just be called pref if you are doing this inside Windows Explorer
Add the following 2 lines to the bottom of the file (use copy and paste):

[Generic Properties]
genericLovSizeSoftLimit = 100 # items allowed in LOV

Note: if you already have a Generic Properties section that was added for the inclusion of a previous bug fix you should add the new preference in that section. Most installations will not have this section so adding it to the bottom of the list of preferences is the logical place.
Save the file
Still in $ORACLE_HOME\discoverer\util: execute the file called applypreferences (it will be called applypreferences.bat in Windows and applypreferences.sh in Unix / Linux)
Navigate back to $ORACLE_HOME\opmn\bin
Type opmnctl startall and press Enter

Discoverer Plus should now display your lists of values.

Note: The preference and value of 100 we just set allows up to 100 items to appear in a LOV. You need to alter this value if your site has lists of values that have more than 100 items. The whole point of this new preference is to remove the restriction of 1000 items like there used to be in previous versions of Discoverer.

Using CASE to solve Outer Join issues

2008-05-19T21:01:00.012-05:00

As many of you know, one of the services that my company offers is Discoverer workshops. What this means is that we send one of our experienced trainers on site and work, sometimes one on one, with the end users on their real system to help them generate the reports that they need. Sometimes I get to go and while I would like to do this more often I just don't have enough time so when the chance does come around I jump at it.

Anyway, one of the most frequent problems that I come across with end user reports is how to effectively create and work with reports when two or more of the folders have outer joins. Such a situation happened in one of the recent workshops that I conducted.

Picture if you will an EUL with 2 folders, folder A and folder B, where folder A is the master being outer joined to B. Thus, if you include something from both folders you will get one row for every item in folder A even if there is nothing in folder B. This is the benefit of the outer join.

Let me get more specific. Let's say that we are working in Human Resources where folder A is Employee Master and folder B is for Employee Leave. Because not all employees have taken leave there is an outer join between the two. Now let's say that in the Leave folder there is an item called Leave Desc that contains descriptions to indicate what kind of leave the employee had taken. With me so far? Here's a screenshot:

Here's some example output:

Ok, so now we want to build a report that allows the end user to exclude one or more Leave Desciptions from the report. To be specific, We want to see all Employees who either have not been sick or have not taken any leave yet. Looking at the report we should be including Carol, Michael and Susie, but not George.

Simply creating the following condition will not work:

Leave Desc <> 'Sick'

Here is that output:

As you can see, George is still included but now appears to have not taken any leave, which isn't true. How about if we also say that Leave Desc IS NOT NULL?

This doesn't give us the right answer either because now both George and Carol have been omitted.

Let's create this calculation which we will call Exclusion:

CASE WHEN "Leave Desc" = 'Sick' THEN 1 ELSE 0 END

We now get this answer, which as you can see has annotated the rows we do not want with 1.

You might think that adding this condition: Exclusion <> 1 would work but it will not. Try it for yourself and you will see what I mean.

You actually need to do add a Boolean OR condition, like this:

Exclusion <> 1
OR
Exclusion <> 1

Problem solved - isn't it? Try removing the Exclusion from the report and see what happens. In Plus you will get the right answer. However in Desktop you will find that your sorting goes wrong. The solution in Desktop is to add a hidden group sort on the Exclusion!

The problem is solved because of the way the CASE statement works which dictates that the function will end as soon as a true condition is encountered. So even there are NULL records these will all get Exclusion of 0 which is the default.

How to find Discoverer patches

2008-05-16T12:42:00.002-05:00

A few people have contacted me recently saying they were having troubles finding the latest Discoverer patches. To help you, here is a workflow:

Launch Metalink and enter your login ID and password
Click the Patches & Updates tab
Click the Simple Search link
Change Search By from Patch Number/Name to Product or Family
Enter Oracle Discoverer Family in the search box
Change Release to iAS 10.1.2.2
Set both Patch Type and Classification to Any
Select your operating system or use Microsoft Windows (32-bit)
Click the Go button
The patches will be listed in order of release with the most recent first

Discoverer 10.1.2.3 update

2008-05-07T12:56:00.004-05:00

August 2008 Important Update: Oracle released CP1 for Discoverer 10.1.2.3 on August 7th (link to patches)

In a recent posting of mine (link) I advised you not to upgrade to Discoverer 10.1.2.3 just yet.

I have heard that Oracle is working on a critical patch update for 10.1.2.3 that will incorporate all of the 10.1.2.2 critical patches (CP4, CP5 and CP6) plus fix any specific 10.1.2.3 issues that are unique to that release. The latest information I have is that you should expect to see the 10.1.2.3 patch before the end of the summer. I don't have any better timeline than this I'm afraid. As soon as I hear something more definite I promise I will let you know.

Therefore, for the time being I still recommend not upgrading to 10.1.2.3. You should, however, upgrade to 10.1.2.2 and apply the most recent critical patch (link).

BI Publisher and Discoverer - 1

2008-04-30T21:47:00.003-05:00

Well it took me a while and lots of digging but I finally managed to get BI Publisher and Discoverer to interface to each other. If you are one of the hundreds who have tried and been unsuccessful, or maybe got there in the end you will understand what kind of an achievement this is.

I made lots of notes as I did the installation(s) and am now formulating a plan of action for publishing a series of articles that will describe in detail exactly how to go about taking a 10.1.2.2 Discoverer and making it interface with BI Pubisher.

Watch this space in the coming weeks.

Performance degradation between IE and McAfee

2008-04-24T22:27:00.008-05:00

It has recently come to my knowledge, via Mike McGrath, that there is a serious degradation in performance if you use Discoverer in the MS IE6 or IE7 browsers when you have the McAfee ScriptScan protection enabled. I know this will come as a shock to many corporate organizations but it is true nevertheless. It is known to affect McAfee 8 and 8.5 but may well be happening with other versions and be the cause of so many people complaining about Discoverer and / or Portal performance.

Note: the above applies equally to Discoverer Viewer and Oracle Portal and probably many other Oracle tools

What appears to be happening is that McAfee is checking the data being returned from Discoverer before allowing it to be displayed on the screen. If you have McAfee and are using either IE6 or IE7 you may be complaining to your IT department about the slow running of Discoverer. This is not a Discoverer issue. We can prove this by running the same report in Firefox where it will run fast.

If you are observant, you can tell that something is amiss because the browser itself will indicate that it has finished processing (the green status bar at the bottom right will be removed or the Windows icon at the top right will stop wiggling around), yet the output still does not display and may not do so for 10 or more seconds.

As a background to all of this, IE uses a Windows component called Windows Scripting Host, or WSH for short, to execute JavaScript (and other things). My version of WSH was 5.6. The most recent version is 5.7, though Microsoft claim that there are no major changes in 5.7! However, one of the changes that they do mention is an improvement to JavaScript that should benefit pages with lots of AJAX-like features (like our Discoverer and Portal pages).

Here is the link to download WSH 5.7:

http://www.microsoft.com/downloads/details.aspx?FamilyID=47809025-D896-482E-A0D6-524E7E844D81&displaylang=en

I installed WSH 5.7, and while this did help IE7 it had no impact on IE6. We therefore seem to have the the following four options:

Do not use McAfee - probably not an option for many users if the corporate policy is to use McAfee
Disable the ScriptScan feature of McAfee across the enterprise - there may well be some resistance to this from your IT department
Do not use IE6 but use Firefox
Upgrade to IE7 and upgrade to WSH 5.7

I guess there is a fifth option which would be to get McAfee to include an option whereby you could specifiy which domains you trust data to come from and therefore bypass the scanning process.

Options 1, 3 and 4 are relatively simple to do although not simple to apply enterprise wide. I will therefore now concentrate on option 2 and show you how to disable the ScriptScan feature inside McAfee.

First of all, right-click on the McAfee icon in the taskbar. You will see the following pop-up
From the pop-up, select On-Access Scan Properties. The following dialog box will be displayed.
Click on the ScriptScan tab
Uncheck the box called Enable ScriptScan
Click the Apply or OK buttons

I found that disabling ScriptScan usually takes effect immediately and you can even stay within the browser. You should see an immediate improvement in Discoverer performance.

When I tried re-enabling ScriptScan, to prove that McAfee was indeed the culprit, we noticed that we had to close the browser, and therefore the Discoverer session, before scanning was implemented again.

I found another interesting article on Andy Dominey's Blog concering this issue. You may want to take a look (link)

Note: as previously mentioned, the above applies equally to Discoverer Viewer and Oracle Portal and probably many other Oracle tools

If you have a solution for this or know of another workaround please let me know (email)

Useful patch numbers and release notes for 10g and 11g

2008-04-24T21:03:00.048-05:00

Last Updated: October 26, 2010

This posting is a synopsis of the most useful patch numbers, certification notes and release information for Discoverer 10g Releases 2 and 3, plus the latest Discoverer 11g. At the bottom of the page you will find information concerning Discoverer certification with E-Business Suite.

Discoverer and IE Certification

Discoverer 11g will be the first version of Discoverer to be certified with the Microsoft IE 8 browser.

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

As for Microsoft IE6 and 7 certifications, 10.1.2.2 is certified for use only with IE6, while 10.1.2.3 is certified for use with both IE6 and IE7.

Install Discoverer Administrator or Desktop onto Vista
In order to install the base Discoverer 10.1.2.0.2 tools for Administrator and / or Desktop onto Microsoft Vista you need to launch the setup.exe from a different location.

6153263 - this is the patch containing a modified installer that will allow the Discoverer 10g tools (Administrator or Desktop) to install onto Vista. Click here for more information.

Upgrade to 10.1.2.3
This is a must-have upgrade and is the only version of Discoverer which is currently having cumulative patches released for it. This is a very stable release and will be the basis for all future releases. If you have already upgraded to 10.1.2.2 then you can apply 10.1.2.3 directly on top. There is no need to de-install 10.1.2.2 or any of its cumulative patches as 10.1.2.3 will manage all of this for you.

If you have not already applied 10.1.2.2 then the upgrade to 10.1.2.3 is a major upgrade and I advise your DBA and / or your administrator to carefully read the release notes before startng the upgrade.

10.1.2.3 vital note: If you have installed this patch make sure you also install CP2 (see below)

5983622 - this is the patch to upgrade Discoverer 10g to 10.1.2.3. It can be applied on top of any 10g version (e.g. - 10.1.2.0.0, 10.1.2.0.2/10.1.2.1 or 10.1.2.2)

This is a major upgrade which may well also require an upgrade to your infrastructure database (see Database 10.1.05 Upgrade notes below). If you are not using an infrastructure the upgrade to 10.1.2.3 is very easy and should take a competent administrator no more than an hour. The upgrade with an infrastructure is more complex and will take from a few hours to the best part of a day, depending upon the complexity and configuration of your application server setup.

After you have upgraded to 10.1.2.3, your Discoverer version will be 10.1.2.55.26

Cumulative Patches for Discoverer 11g
Oracle has released no cumulative patches for Discoverer 11g as of February 8, 2010. I expect to see these commence during the first half of 2010.

Cumulative Patches for Discoverer 10.1.2.3 (see platform notes and tags alongside individual patches)
Cumulative patches for 10.1.2.3 can only be applied on top of the base 10.1.2.3 and on top of each other. They will automatically de-install any previous cumulative patch before installing. Most cumulative patches are platform specific and usually require a password from support before you can download them.

CP8 - 9694503 - issues 5-OCT-2010 - works with platforms 1, 2, 3, 5 and 8
CP7 - 9112482 - issued 4-JUN-2010 - works with platforms 1, 2, 6 and 8
CP6 - 8746296 - issued 18-NOV-2009 - works with platforms 1, 2, 3, 4 and 6
CP5 - 8354043 - issued 12-AUG-2009 - works with platforms 1, 2 and 4
CP4 - 7595032 - issued 28-APR-2009 - works with platforms 1, 2, 3, 4 and 6 (see note below)
CP3 - 7319096 - issued 4-JAN-2009 - works with platforms 1, 2, 6 and 7 (do not install - read why)
CP2 - 7198716 - issued 3-OCT-2008 - works with platforms 1, 2, 3, 4, 6 and 7
CP1 - 7111842 - issued 7-AUG-2008 - works with platforms 1, 2, 3, 4, 6 and 7

Platform Notes:
The above cumulative patches can be applied to the following platforms:

Microsoft Windows (32-bit)
Linux x86
IBM AIX (5L) Based Systems (64-bit)
Sun Solaris SPARC 32-bit, can also be installed on Sun SPARC 64-bit
Sun Solaris x86
HP-UX PA-RISC (32-bit)
HP-UX Itanium
Oracle Solaris on SPARC 64-bit

IMPORTANT NOTE for CP4 and above
Please apply the JDBC patch p4398431_10105_GENERIC.zip for bug 4398431(release 10.1.0.5).This patch needs to be applied to all Oracle Homes, i.e. Infrastructure home as well as all related midtier homes.
Bug 4398431 - HANG WHEN RETRIEVING A CONNECTION FROM THE IMPLICIT CONNECTION CACHE

Then proceed with applying the CP4 or above patch. If you are upgrading from CP4 and above it is assumed that you have already applied 4398431 and you do not need to do it again.

Database 10.1.0.5 upgrade notes
When you install the full-blown application server 10.1.2.0.2 (10.1.2.48.18) out of the box you will get a pre-seeded 10.1.0.4 metadata repository. Before you can apply either the 10.1.2.2 or 10.1.2.3 upgrade this database must be upgraded to 10.1.0.5. This upgrade will take a competent DBA about half a day.

4505133 - this is the 10.1.0.5 database patch

Web Services for interfacing Discoverer with BI Publisher (10.1.2.2 only)
If you want to be able to interface Discoverer 10g with BI Publisher you will need to apply a web services patch to your Discoverer middle tier.

7028146 This is an updated web services patch and is compatible with cumulative patch CP7 for 10.1.2.2 and later- see below (6778560).
6622352 This is an updated web services patch and is compatible with 10.1.2.2 cumulative patches CP4, CP5 and CP6.
5648158 This is the original web services patch and can be applied on top of 10.1.2.2 (you see how important 10.1.2.2 is) along with any cumulative patch up to and including CP4

Web services note: If you have already installed the original web services patch (5648158) and wish to apply CP5 or higher, you will need to remove it before you can apply CP5 or CP6. You should then apply 6622352. If you are installing CP7 or CP8 you will need to remove 5648158 or 6622352, install CP7 or CP8 then install 7028146.

The web services patch is not required for Discoverer 10.1.2.3 as this level of the application server has the necessary web services components built in.

Upgrade to 10.1.2.2
This is no longer a must-have upgrade and if you have already upgraded to this release you need to now upgrade to 10.1.2.3. If you have not yet upgraded to 10.1.2.2 you should not do so and should proceed straight to 10.1.2.3.

Like 10.1.2.3, this is a major upgrade which will require an upgrade to your infrastructure database (see note earlier in this posting). If you are not using an infrastructure the upgrade to 10.1.2.2 is very easy and should take a competent administrator no more than an hour. The upgrade with an infrastructure is more complex and will take from a few hours to the best part of a day, depending upon the complexity and configuration of your application server setup.

4960210 Patch to upgrade Discoverer to 10.1.2.2.

After you have upgraded to 10.1.2.2, your Discoverer version will be 10.1.2.54.25

Cumulative Patches for Discoverer 10.1.2.2 (see platform notes and tags alongside individual patches)
Cumulative patches can only be applied on top of 10.1.2.2 and on top of each other. They will automatically de-install any previous cumulative patch before installing. Most cumulative patches are platform specific and most, certainly the more recent patches, do not usually require a password from Oracle Support before you can download them.

CP8 - 7306816 - issued 14-AUG-2008 - this is CP8 for platforms 2, 3, 4, 6, 7 and 8 (see * CP8 note below)
CP8 - 7111816 - issued 18-JUL-2008 - works with platforms 1 and 5 (see * CP8 note below)
CP7 - 6778560 - issued 11-MAY-2008 - works with platforms 1, 2, 3, 4, 6 and 7
CP6 - 6669003 - issued 12-MAR-2008 - works with platforms 1 to 7
CP5 - 6472361 - issued 21-DEC-2007 - works with platforms 1 to 7
CP4 - 6357481 - issued 07-NOV-2007 - works with platforms 1, 2, 3, 6 and 7
CP3 - 6155500 - issued 31-AUG-2007 - works with platforms 1 to 7
CP2 - 6129303 - issued 22-JUL-2007 - works with platforms 1, 2, 4 and 6
CP1 - 5943426 - issued 23-MAY-2007 - works with 1, 2 and 3 - obsolete - do not use

Platform Notes:
The above cumulative patches can be applied to the following platforms:

Microsoft Windows (32-bit)
Linux x86
IBM AIX (5L) Based Systems (64-bit)
Sun Solaris SPARC 32-bit, can also be installed on Sun SPARC 64-bit
Sun Solaris x86
HP-UX PA-RISC (32-bit)
HP-UX Itanium
Linux Itanium

* CP8 Note: Oracle originally issued CP8 for Windows and Unix on 18 July 2008. As they were working through the other platforms they realised that further bugs needed to be fixed. Therefore, on 15 August 2008 they issued a second CP8 patch. This patch is for for Linux, IBM AIX, HP-UX (PA RISC and Itanium), plus the Sun Solaris SPARC platforms.

You should use 7111816 if you are patching Windows, and 7306816 if using any of the other platforms.
CP8 is the last cumulative patch for 10.1.2.2

OPatch
Oracle recommends that you always make sure you have the most-recent version of OPatch installed before applying any patches. As of this posting the most-recent version of OPatch is 1.0.0.0.62. The following Oracle Support documents will help:

2617419 - installer for OPatch 1.0.0.0.58
6880880 - instructions and downloads for OPatch 1.0.0.0.62

OPatch notes: when looking to download OPatch 1.0.0.0.62 you will notice that there are three different downloads available. These are:

Release 10.1.0.0.0 - suitable for Oracle databases 9i and 10.1
Release 10.2.0.0.0 - suitable for Oracle database 10.2
Release 11.1.0.0.0 - suitable for Oracle database 11.1
Release 11.2.0.0.0 - suitable for Oracle database 11.2

most patches will install with 1.0.0.0.57, however in order to install CP8 on Linux you must have OPatch version 1.0.0.0.58 Click here for more details

MS IE7 Certification
Out of the box, Discoverer 10.1.2.0.2, even with the base upgrade to 10.1.2.2, is not certified for use with Microsoft IE7. The main issue is that when you are hyper-drilling between worksheets the parameter values are not passed properly.

5673463 - this allows Discoverer to be fully certified with IE7

MS IE7 Note: Part of this fix was released in CP4 but has been fully certified and included as part of CP6 or higher. If you are on a pre-CP4 version and want to use IE7 you need this patch.

Discoverer and E-Business Suite Certification
This section contains details about Discoverer's certification with Oracle E-Business Suite

As of November 2008, Discoverer 10.1.2.2 and all of its cumulative patches are certified for use against E-Business Suite 11i and 12i. For more information please go to Oracle Support and look at note 313418.1

Discoverer 10.1.2.3 is only certified if you apply CP1 or higher. So long as you have applied at least CP1 then it is certified for use against both 11i and 12i. For more information please go to Oracle Support (formerly MetaLink) and look at note 373634.1.

Note: The base 10.1.2.3 is not certified with E-Business Suite

This page is offered as a public service. If anyone has any information regarding patches, trouble with patches, or certifications please let me know

Do not upgrade to Discoverer 10.1.2.3

2008-04-24T20:44:00.006-05:00

October 2008 Important Update: Oracle released CP2 for 10.1.2.3 on October 3, 2008

I urge you not to stay on the non-updated base install of 10.1.2.3. You should apply CP2 or CP1 as soon as you can (link to patches)

I have recently been contacted by someone asking for help with Discoverer 10.1.2.3 - 10.1.2.55.26. I'm guessing most of you never even knew that it had been released. There was certainly no big splash about this anywhere that I have seen.

Anyway, it was released 0n 07-MAR-2008 as part of Application Server 10g patch set 3, which you can find on MetaLink as patch number 5983622. This is a major patch and can be applied on top of 10.1.2.0.0, 10.1.2.0.2 or 10.1.2.2. However, when it was first released it was very buggy which is why both Oracle and myself were recommendingyou should not apply it at that time.

Happily, you are safe installing 10.1.2.3 so long as you apply at least CP1.

However, if you are using E-Business Suite, Oracle is still not recommending customers not to upgrade to Discoverer 10.1.2.3 because:

10.1.2.3 is not yet certified with E-Business Suite 11i or 12. This work is currently in progress and the appropriate notes will be updated when the certification is complete.

Therefore, if you have already upgraded to 10.1.2.3 you need to reverse that and get back to a stable version of 10.1.2. At that point, if you have not already done so, you need to upgrade to 10.1.2.2 (patch number 4960210) and then apply the latest 10.1.2 cumulative patch. As of the date of this posting this is CP8 which was released during July and August 2008. You can find it on MetaLink as patch number 7306816 (if on Linux, Solaris or HP) or 7111816 if you are using Windows.

Optionally, especially if you want to interface Discoverer 10g with BI Publisher you will need to also apply the web services patch which for CP4 and above is 6622352.

Need to login to Discoverer twice

2008-02-11T22:10:00.001-06:00

Do you have to log into Discoverer twice in order to make it work? If so, read on because I have a possible solution for you.

When you launch either Discoverer Viewer or Plus, as shown above, and enter your credentials into the dialog box, does Discoverer respond with something like this?

Presumably what happens next is that you log in again using exactly the same credentials and now it works. Yes?

If so, the problem lies in the way that your server name has been defined inside the HTTPD.CONF file which you will find in the Apache configuration folder on your Application Server:

$ORACLE_HOME\Apache\Apache\Conf

You will need to edit HTTPD.CONF and search for a line that begins with ServerName. It will more than likely look something like this:

ServerName servername

For example:

ServerName asclaptop4

Notice how there is no domain name associated with the server. This is the problem, and is causing the system to incorrectly convert the URL inside OC4J.

Solution

There are two solutions; a temporary solution and a permanent solution. Both are outlined below:

Temporary Solution:

The temporary solution is to make sure that you only call Discoverer using a fully qualified domain name in the URL. You do that like this:

http://servername.domain.com:port/discoverer/viewer or

http://servername.domain.com:port/discoverer/plus

Permanent Solution:

The permanent solution is to change the ServerName so as to make it a fully qualified domain name, as shown in the example below, and then restart the application server. You now should be able to log in just once.

ServerName servername.domain.com

For example:

ServerName asclaptop4.learndiscoverer.com

By the way, using the command line, here is how to stop, start and check whether Discoverer is up and running:

To Start Discoverer, use this workflow:

Navigate to the $ORACLE_HOME\opmn\bin folder
Execute this command: opmnctl startall

To check whether Discoverer is up and running, use this workflow:

Navigate to the $ORACLE_HOME\opmn\bin folder
Execute this command: opmnctl status
The system should respond with something like this:

Note: It is perfectly ok for DSA, LogLoaderd and dcm-daemon to have a status of Down so long as all of the other components show Alive.

To stop all of the Discoverer components, use this workflow:

Navigate to the $ORACLE_HOME\opmn\bin folder
Execute this command: opmnctl stopall

Do you have another solution for this issue? If so, either drop me a line or post a comment to this posting.

SunGard Summit 2008

2008-01-30T07:34:00.000-06:00

I am delighted to be able to let you know that Armstrong-Smith Consulting will be attending the SunGard Summit 2008 (link) conference in Anaheim, April 13 to 16. At the conference we will once again have a 10 x 10 booth and will be able to discuss our Oracle Discoverer training and consultancy offerings with attendees.

Last year, we had over 500 people attend our booth. During the conference we also gave away an iPod and 4 copies of our Oracle Discoverer 10g Handbook (link).

For those of you who don't know, SunGard Higher Education (link) provides software, systems implementation and integration, strategic consulting, and technology management services to colleges and universities. Here at Armstrong-Smith Consulting we are delighted to have partnered with SunGard and to be a part of the SunGard Higher Education Collaborative.

Our partnership allows us to provide report building, training and other consultancy services for Oracle Discoverer on the SunGard ODS and EDW products. We can even help institutions comply with federal and state regulations to ensure that their private data is secured.

Are you a SunGard Higher Education customer?
Do you have the SunGard ODS or EDW?
Do you have Oracle Discoverer?

If you answered yes to the above three questions you need to speak with either your SunGard Higher Education support representative or email us at info@learndiscoverer.com and find out how we can help you make the most of your investment.

Would you like to see your BI articles on the web?

2008-01-28T22:28:00.000-06:00

Have you written any interesting articles on Discoverer, Oracle Warehouse Builder and / or just business intelligence in general but have no means of getting this information out into the BI community? If so you will be among hundreds of like-minded people who would love to see their articles in print. This is where I would like to come to your aid. I am offering to make available your articles and papers from my web site and via my blog totally free of charge.

To have documents posted you would need to send them to me, along with any commentary that you would like to see displayed. If you have enough material I could even create a special page just for you. I do however reserve the right to look through any documents you send me before publishing as I would not want anything that is offensive or a copy of someone elses work on my site. If I see anything that is of concern I would raise it with you and I will of course give you full credit for anything you send me that is published.

The first documents have already been submitted (link) and I would like to say thank you to Jayashree Satapathy and Krishna Mohan from Hyderabad, India for sending me two Discoverer Power Point presentations. The two papers that have been submitted are:

Discoverer 10g Administration - 84 slides that focus on administering Discoverer in an E-Business Suite environment. You don't see many papers on this topic and you may well find something you didn't already know
Discoverer 10g Desktop - 80 slides that focus on the Discoverer Desktop, a tool that does not get a lot of press these days. Towards the end of this paper you will find some very interesting slides that walk you through running a Discoverer worksheet from within E-Business Suite

Do you have a BI paper that you would like to see published? It doesn't have to be on Discoverer, you can pick any BI subject you like. This is a great opportunity to spread the wealth of information that is available about Discoverer, Warehouse Builder or just BI in general. Think it over and if you would like to get going feel free to send me (link) a document or two.

New white paper on Discoverer sizing

2008-01-28T22:14:00.000-06:00

Following years of research at dozens of clients I came to the conclusion that the sizing matrix published by Oracle is somewhat inaccurate, particularly in the amount of memory required to run Discoverer Plus in a real-world environment. For the past couple of years I have been recommending my clients not to follow the Oracle published matrix and have been advising them independently. Recent follow-ups have confirmed that those clients who have followed my advice see very little issues with their Discoverer servers needing additional RAM or CPU.

I have therefore decided to publish my findings as recommendations in a white paper. That white paper is now available for download (link) from my main web site.

If you have any experience with sizing of Discoverer that you would be willing to share please let me know. We can all better help the community at large if we share information.

Discoverer Cumulative Patches available

2007-12-07T08:40:00.002-06:00

Last updated: June 5, 2008 (link to useful patches)

If you didn't know already, let me be the first to give you the great news that Oracle has changed its patching policy for Discoverer. Gone are the days of sifting through myriads of individual patch numbers trying to figure out if there is a patch for the issue you are seeing. Instead, Oracle has introduced what are called Cumulative Patches.

If you have access to MetaLink you should take a look at note 466058.1. Here are the main points (mostly Oracle's text with some explanatory comment from me):

After getting numerous suggestions from customers, partners, and internal support engineers, Oracle Discoverer Development has implemented a cumulative patching model starting with Oracle BI Discoverer 10g (10.1.2.2). This is Discoverer version 10.1.2.54.25. The new model replaces the one-off patching model that was in practice with previous versions 10.1.2.0.2 – 10.1.2.1 (Discoverer 10.1.2.48.18).

These Discoverer cumulative patches can be installed on top of the full Oracle Application Server or the Oracle BI Discover standalone editions 10.1.2.2 (Discoverer 10.1.2.54.25), and higher versions.

The cumulative patching model applies to Discoverer Plus and Viewer (in other words the middle-tier), for both Discoverer relational as well as Discoverer for OLAP. Patching for Discoverer Administration and Desktop on Windows (the client-server components) will continue to use the previous one-off patching model, at least for the time being.

Here is some additional information about the cumulative patching model:

The Discoverer cumulative patch (otherwise called CP):
The Discoverer cumulative patches (CP) for Oracle BI Discover 10g (10.1.2.2) are what Oracle call merge patches. These combine multiple one-off bug fixes rolled up into a single patch. The good news is that these cumulative merge patches include bug fixes for both Discoverer Relational and Discoverer for OLAP. The excellent news is that each patch is cumulative, meaning, that CP7 will include all fixes up to and including CP6 plus any new bug fixes.
The Discoverer cumulative patch schedule:
Oracle plans to release the Discoverer cumulative patches approximately every five (5) weeks. According to Oracle, these dates are approximate and may change depending on the number of bugs encountered. In reality the average has been about 10 to 12 weeks. As of the time of the latest edit to this posting, Oracle has released 7 cumulative patches. The latest one is CP7 (patch number 6778560) and contains over 300 bug fixes on top of the base Discoverer 10.1.2.2 patchset. (link to useful patches)
The advantages of Discoverer cumulative patches:
Unlike the one-off patches, which are still availble by the way, each cumulative patch contains large number of bug fixes and undergoes Quality Assurance (QA) regression testing. Oracle state that this will greatly improve the stability of the patches and the product and will help customers take advantage of the latest available bug fixes. This also greatly reduces maintenance since you install only one cumulative patch and do not have to install and rollback multiple one-off patches

Open World is coming

2007-10-19T14:31:00.001-05:00

With only a couple of weeks to go before Open World I have been looking through the agenda and have selected the when, who, where and what of some of the Business Intelligence and Discoverer presentations that caught my eye. I'll be at most of these so say hello if you see me.

When: Monday 12th November 11:00am to Noon
Who: Michael Armstrong-Smith
Where: Franciscan Room D, Hilton
What: Build a Discoverer EUL in one hour
Yours truly will be giving a a quick, fast, live (yes live) demonstration on how to build a Discoverer End User Layer

When: Monday 12th November 12:30pm to 1:30pm
Who: Steven Feuerstein, Quest Software
Where: Hilton Continental Ballroom 5
What:Break your addiction to SQL
Steven is one of the best speakers on the Oracle circuit and has written many books on SQL (link). If you have never heard him before you should drop in. He also runs a cool blog (link)

When: Monday 12th November 3:15pm to 4:15pm
Who: Steven Feuerstein, Quest Software
Where: Hilton Yosemite Room B
What: Optimize PL/SQL with Collections

When: Tuesday 13th November 10:45am to 11:45am
Who: Mike Durran, Oracle
Where: Moscone West 3022 L3
What: What's coming next for BI Standard Edition and Discoverer
Along with Jeff Hohman and Mike Donahue (see below), Mike Durran is among my favorite presenters. Even if you don't like the subject, which you should seeing as you are reading this blog, drop in anyway because all of them are nice people. You'll always get straight answers from these gentlemen.

When: Tuesday 13th November 12:15pm to 1:15pm
Who: Mike Durran, Oracle
Where: Moscone West 3022 L3
What: How to migrate Discoverer to BI Enterprise Edition

When: Tuesday 13th November 3:15pm to 4:15pm
Who: Steven Chan, Oracle
Where: Moscone West 3022 L3
What: Using E-Business Suite with Application Server 10g
Steven runs a great blog (link) for everything to do with E-Business Suite. If you use it you could do a lot worse than drop in and take a look.

When: Tuesday 13th November 4:30pm to 5:30pm -
Who: Michael Armstrong-Smith
Where: Moscone West 2nd Floor Lobby
What: Oracle Press Author Meet and Greet
If you have a copy of my Discoverer 10g Handbook bring it along and I will sign it for you. If you don't have one yet, get one at the bookstore - you know you want one!

When: Wednesday 14th November 3:00pm to 4:oopm
Who: Simon Griffiths, British Telecom
Where: Moscone South 303
What: Partitioning experiences

When: Thursday 15th November 8:30am to 9:30am
Who: Jeff Hohman, Oracle
Where: Moscone West 3024 L3
What: Discoverer, Portal and VPD

Where: Thursday 15th November 10:00am to 11:00am
Who: Mike Donahue, Oracle
Where: Moscone West 3014 L3
What: BI Publisher with E-Business Suite

When: Thursday 15th November 11:30am to 12:30pm
Who: Mike Durran, Oracle
Where: Moscone West 3014 L3
What: Oracle BI Standard Edition Best Practices

When: Thursday 15th November 2:30pm to 3:30pm
Who: Debbie Arnold
Where: Moscone West 3024 L3
What: BI Publisher made easy

An EUL horror story

2007-08-22T22:39:00.000-05:00

EUL backups - now there's a novel concept. Let me tell you a story.

Just over a year ago I consulted to a large organization and created a new EUL with a couple of business areas. It took three weeks to complete. On the Friday when I was leaving I migrated the whole lot from Dev to Prod and left.

About 11am on Monday morning I received a call that went something like this: Michael, hello, how are you? Ummm - Discoverer deleted itself from production. Don't you find that odd? Why does it do that?

Well you can't kid a kidder. Because I know the tool so well I said - it cannot just delete itself - either the owner or someone who knows the EUL owner's password must have logged in and told Discoverer to delete the EUL.

No admissions of guilt were offered - naturally. I mean - would you own up to having deleted your company's production reporting system on the same day that it went live? Perhaps booking a one-way ticket to Australia (sorry oz - but I am English) would be in order.

No problem - I said - go to your Prod backup and restore your EUL schema. You'll soon be back up and running.

Ah - they said - here's the second problem - we forgot to put the EUL schema into the backup routine.

Hmm - I said - in that case all you have to do is go to your Dev instance, export the EUL and then import it into Prod.

Ah - they said - here's the third problem - we refreshed Dev from Prod on Sunday afternoon and it also does not contain an EUL.

Oops - I said - when do you want me to come back?

Let us think about it - they said - and we'll call you back.

They did call me back and I informed them that before I left on Friday I had taken a backup myself of the EUL in Dev and told them where it was on their server.

Phew - they said - thank you, thank you. Oh and by the way the DBA admitted to having dropped the EUL schema on Friday evening as he thought it was a user messing about!

I recount this story as a warning to everyone else - please make sure your EUL schema is included in your backups. A good administrator should also take periodic exports of the EUL - just in case!

Anyone else got a similar horror story - hopefully with a happy ending?

By the way - I'm in Boston this week and I can't find a decent English cup of tea anywhere. In years gone by we would have gone to war over that. Oh - we did - and we lost. Methinks I'd better keep a low profile.

Paper accepted at Open World 2007

2007-08-20T20:39:00.000-05:00

I am delighted to be able to let you know that I have had a Discoverer paper accepted for Oracle Open World 2007. I will be presenting my famous Build an EUL in one hour paper. Perhaps you have already seen it? Perhaps you have heard of it and would like to see for yourself?

Either way, this will be a live demonstration, using the Discoverer 10.1.2 Administration tool of how to create a fully working End User Layer inside one hour. You will be shown all of the steps involved in creating a fully working Oracle Discoverer EUL. If you have a copy of the Oracle Discoverer 10g Handbook, this presentation will take you through all of the steps required to build the tutorial database.

I will start by selecting the schema owner and tables required. Using the Load Wizard, you will be walked through all of the steps involved in loading the meta data definitions from the source database. Sticking close to the wizard's defaults, you will see how Discoverer automatically builds joins and lists of values.

After I have the base folders loaded, now the fun stuff begins. You will now be shown how to add some new items, in particular we will add new columns for calculating standard margin and mark up, as well as for profit and profit percentage. We do this so that the end users do not need to build these for themselves.

Next, you will be shown how to build complex folders out of the simple folders that we have just created. We will create fiscal date folders, linking these back to the sales order folder. After creating these new folders, you will be shown how to create fiscal date hierarchies so that users can drill from day, to fiscal month, fiscal quarter and so on.

After you have been shown how to build complex data folders, you will be shown how to build a geographic rollup. This rollup will allow the users to drill from City to District to Region.
After building the complex folders for fiscal date and geography, we will have created a psudo star schema with the our sales folder at the center.

The presentation will conclude with a walk through of some of the most often used features of Discoverer's Administrator edition. As I have said many times, you are only limited by your own imagination when it comes to working with Discoverer.

If you're going to be in San Francisco in November come and join me and say hello.

Using ALL in a parameter

2007-07-25T17:19:00.000-05:00

From time to time, I get asked and I see questions in the OTN forum regarding how to create a condition / parameter that will allow a user to select all items from a list of values without actually checking off every item in the list.

Here's an example drawn from my own database.

As you can see I am starting out with a Crosstab workbook that displays the Cost and Selling Price for my Products by Channel and Product Line, with the Product Line being a page item.

Next, I started out by creating a new parametized condition. As usual, from the Values drop-down list I selected New Parameter. Below is my completed New Parameter dialog box. Of note you should see that I have told the user that they can enter ALL for all values, and I have even set the default to be ALL. Even though that value does not appear in my list of values, it doesn't matter. Discoverer will allow me to continue.

After clicking the OK button to close the New Parameter dialog box, back in the New Condition box I clicked the Advanced button to the side of the condition and from the enhanced list I selected OR so that I can add some Boolean logic to my condition.

The first thing I did was to click the drop-down alongside the Item. From the list I then selected New Calculation. In the calculation I typed in the following: UPPER(:ProdLine). I did this so that my users do not have to type ALL (in uppercase) but rather they can type all if they wish, or even All.

Finally, as you can see below, I completed my condition my typing in ALL into the Values area.

My final condition is therefore this:

Line Name = :ProdLine
OR
=UPPER(:ProdLine) = 'ALL'

Notice how Discoverer added the = character in front of my calculation. This is Discoverer's way of letting you know that what follows is to interpreted as a calculation and not a literal value.

That's all there is to it. With the condition finished, whenever my users are prompted to select one or more product lines, they can type in ALL and Discoverer will return all values.

Memories of ODTUG 2007

2007-06-25T20:22:00.000-05:00

Last week I was in Daytona Beach, Florida, attending the ODTUG Kaleidoscope 2007 conference, and what a great conference it was. Not only did I meet up with many old friends, among, in alphabetical order, Mike Ault, Mike Durran from Oracle, Kent Graziano, Mark Rittman, and Dan Vlamis; but I made contact with several new people who are destined to become regulars on my emailing list; presented a couple of interesting papers; but I got a chance to attend some great presentations.

I particularly enjoyed Mark rittman's paper on BI EE for Discoverer users, Jeff Hofman's paper on integrating Discoverer with SSO and Portal, and Mike Donahue's paper on BI Publisher. One paper that I had to miss, because it was being given at the same time as I was presenting, was by Mike Durran who was presenting Oracle's roadmap for Discoverer. Mike himself had published Oracle's Discoverer Statement of Direction on OTN (link). If you haven't had a chance to take a look, I fully recommend doing so as it clearly spells out that Discoverer is here to stay.

All in all, I counted almost 10 papers either directly focused on Discoverer on with Discoverer content. This makes a sharp increase over previous years and shows how popular Discoverer has and is becoming. If you or your company are sitting on the fence, already have Discoverer, but haven't made full use of it yet, then now is the time to do so. Discoverer is not going away and is firmly an integral piece of Oracle's business intelligence offerings.

By the way, my two papers from ODTUG will be available for download from by website (link) before the end of this week.

All about ODTUG

2007-05-21T22:11:00.000-05:00

The Oracle Development Tools User Group (ODTUG) is having its annual conference next month in Daytona Beach, Florida. If you have never been, perhaps this might change your mind:

If you haven’t already heard about the ODTUG Kaleidoscope Conference, June 18 through 21 in Daytona Beach Florida, I recommend that you check it out. I will be presenting at it and am looking forward to mingling with other Oracle technologists in an atmosphere that is both conducive to learning and relaxing. Long-heralded as the Oracle user group for developers and by developers, the Oracle Development Tools User Group (ODTUG) established Kaleidoscope as its flagship event with sessions covering all the practical technical information you need to excel in your job.

Why attend ODTUG Kaleidoscope? Because it's the only conference of its kind to give attendees the opportunity to get hands-on training with the tools, learn smart techniques, and talk with the experts that make Oracle tick. Kaleidoscope features:

More than 125 Technical Sessions with handouts—real topics, with real information, for real developers. No hired presenters. No fancy footwork. Just genuine colleagues sharing practical information.

Two full days of preconference Hands-on Training.

Oracle product updates.

A full-day Oracle Fusion Symposium.

An Oracle PL/SQL Test-A-Thon, developed by Steven Feuerstein and packed with great prizes. The Test-A-Thon is sponsored by Quest Software and O’Reilly Books.

A one-of-a-kind Install Fest, where you will learn from the "gurus" how to install and configure Linux to run Oracle 10g software.

APEXposed, an ODTUG exclusive "Seriously Practical" training event on Application Express.

Unprecedented opportunity for networking with colleagues and experts at organized functions, on-site SIG Meetings, and more.

It's affordable. It's technical. It's in Daytona Beach. And it's what you need to survive in today's fast-paced Oracle environment. When it comes to supporting Oracle developers in the trenches, the most targeted conference available is ODTUG Kaleidoscope,

For more information about ODTUG Kaleidoscope activities, location, exhibitors, or sessions, please visit http://www.odtugkaleidoscope.com/ or call 910-452-7444.

Hope to see you there.

P.S. Click here for information about the papers I am presenting.

My sessions at ODTUG

2007-05-21T22:04:00.000-05:00

For everyone who has been asking, here are the dates and times for the two presentations that I am giving at next month's ODTUG conference in Daytona Beach, Florida.

Securing Private Confidential Information is scheduled for Wednesday, June 20, 2007 from 4:00-5:00 in Coquina B

Automated Scheduling and E-mailing from Within Discoverer—and More is scheduled for Thursday, June 21, 2007 from 11:00-12:00 in Coquina G

Are you going to ODTUG? If so, look me up.

By the way, here's the low down on the two papers:

Automated Scheduling and E-mailing from Within Discoverer:

Ever wanted to know how to schedule a Discoverer workbook and automatically e-mail it? Perhaps you are one of the hundreds of people who have asked this very question on the OTN forums. If so, your search is over. You'll be amazed at the simplicity of the approach and be ready to take it back to your office and try it for yourself. You will also learn how to set up Discoverer lists of values that comprise both an ID and a Description at the same time. This live demonstration of both Discoverer Administrator and Plus will show you how to create parameters that will accept an ID or a Description, yet still return the description to the user.

Securing Private Confidential Information:

Recent high-profile cases have highlighted the need for data such as social security numbers, dates of birth, and ethnicity to be protected. Have you considered securing your private and confidential information but decided it was too difficult? Using profiles and a VPD, you will see how to set up both row-based and column-based security. Using Discoverer, you will learn how to set up a mechanism to allow your support staff to connect to the database using the same profile as an end user, without the need to know the end user's password. Thus, you can set up a secure environment yet at the same time enable your support staff to help your users without the security breach of a disclosed password.

Thoughts on OBI EE for Discoverer Users

2007-05-21T21:42:00.000-05:00

There's a very interesting article of Mark Rittman's blog entitled Thoughts on OBI EE for Discoverer Users. I won't bore you with the ins and outs but, and to quote from Mark:

if we’re looking at a potential upgrade for Discoverer users, it would be useful to take a moment to think about what’s good, and what’s not so good, about Oracle Discoverer

Mark does a very good job of discussing the pros and cons of Discoverer and at first glance the article can look a little anti-Discoverer. However, read on and you will see that Mark has been equally tough on the new OBI EE. Thus, in my opinion, I think the article is well worth reading.

Totals and blank lines

2007-04-23T23:57:00.000-05:00

The following is an interesting conundrum that I have come across recently and is something that you might be interested in knowing about. It's concerned with what happens when you combine sub-totals and additional rows between group sorted items.

Example:

In the following worksheet, as shown in illustration 1, you can see that I have a group sort on the Customer with a sub-total on the Profit. A not uncommon scenario I hope you will agree.

Illustration 1: A group sort with the sub-total displayed correctly

Next, I right-clicked in the Customer and from the pop-up menu selected Format Table. Discoverer opened up the Format Data dialog box. This box has three tabs:

Format - on this tab you set up the standard formatting characteristics such as font, size, background color, foreground color, alignment and so on

Text - on this tab you can set additional characteristics such as uppercase, lowercase or capitalization

Breaks - on this tab, which is only enabled if you are formatting a Group Sort, you can control what happens when group sorted items change. As you can see in illustration 2, I have enabled a line width of 2 in red, and the insertion of one blank row between each group sorted item.

Illustration 2: The Format Data dialog box and the Breaks tab

After I clicked the OK button in the above box, Discoverer redrew my screen, but drew it incorrectly as shown below in illustration 3. As you can see, the totals still calculate correctly. But take a close look at what happened on the totals line for each sub-total other than the first one. As you can see, the word NULL has been inserted against each of the other items. In addition, take a very close look at row 9 and in particular at the cell value containing the Profit. As you will see, Discoverer has placed the Sum label here instead of alongside the $23,939.00 on the previous row.

It therefore appears that the insertion of an additional line for the group sort has caused the label to move down by that number of rows also. This means that Discoverer does not know what to do with the cell values on the original sub-total and so it inserted a NULL value. Formatting the table to change the display of NULL values to something other than NULL does indeed change the wording.

Illustration 3: A group sort with the sub-total displayed incorrectly

Therefore, please be very careful when using additional lines between group sorted items when there are sub-totals. The following illustration shows the same worksheet displayed in Viewer. As you can see below in illustration 4, Viewer does not appear to have this issue. I can therefore only assume it is something to do with the Java.

Illustration 4: The same group sort, as seen in Viewer, with the sub-total displayed correctly

The future for Discoverer

2007-04-13T00:38:00.000-05:00

From time to time people approach me and ask me what do I think will happen to Discoverer now that Oracle have Siebel and Hyperion (including Brio). The pessimists in the world think that Oracle plan to drop Discoverer. I don't believe this for one minute.

But why don't I believe this? Well for a start Oracle have new releases of Discoverer planned for this year (10.1.2.3), and next (11g). However, for fun, let's assume that Oracle do plan to drop Discoverer. They won't announce this in the same year that they release 11g because that would just make folks not even bother to use the latest version. So the earliest I would see such an announcement would have be in 2008. However, as all Oracle products carry a 5 year full support agreement from whenever the drop is announced this would mean that Discoverer would remain a fully supported, patchable, fixable product until at least 2013. That's a lifetime in terms of software. That's 6 years from now. Oracle will have bought SAP by then. Maybe even the San Francisco Giants will have managed to win a game against the Padres!

However, the real reason I don't believe Oracle will drop Discoverer is because of some very favorable comments coming out of Oracle.

Firstly, Oracle has recently revamped its Standard Edition page on oracle.com (link) which makes for very interesting reading.

Secondly, follow the link for the data sheet (link) and you will get to a PDF document that outlines just where Discoverer fits into the Fusion picture. With acknowledgements to Oracle, here are some interesting snippits from that document:

You've already chosen Oracle database for your business, now make it shine by complementing it with the business intelligence suite that is most tightly integrated with it - Oracle BI Standard Edition.

Oracle BI Discoverer is [tightly] integrated with other components of Oracle Fusion Middleware.

Discoverer reports are easily embedded within Oracle Portal using a simple wizard, making role-based dashboard creation a simple exercise.

Oracle Discoverer is also unique in that it recognizes and respects the Oracle E-Business Suite security model.

Today few of us buy best of breed kit parts and hand craft our own cars; most of us simply go to an established, proven manufacturer to obtain a complete solution, backed by a warranty with a single support organization should anything happen. Software should be like this - and this is where Oracle BI SE delivers. Everything - from ETL (with Oracle Warehouse Builder), to ad-hoc query (with Oracle BI Discoverer), to dashboards (with Oracle Portal), to pixel-perfect reporting (with Oracle XML Publisher), to custom BI development (with Oracle BI Beans) - is available as an integrated set of components.

The entire middleware solution can be effectively clustered with low cost hardware. Combine this deployment flexibility with an attractive pricing model for BI and middleware components, and you will be hard pressed to find a more affordable fit for your growing BI needs.

There - I could not have said it better. I think this clearly shows Oracle's intentions.

Looking for a great Discoverer position

2007-04-11T18:19:00.000-05:00

If you are an experienced Discoverer developer and are looking for a great position and opportunity in Washington, DC, then we may have the job for you. Click here for more details.

Workbook Migration Document

2007-04-09T22:53:00.000-05:00

By popular request I have made my Workbook Migration white paper available for free download on my website. You will find it at this link.

It now joins these other white papers available for download:

Discoverer libraries - a concept
Setting up an Apps mode EUL
Multiple Oracle Homes

InfoCaptor

2007-03-29T22:20:00.000-05:00

I am delighted to be able to announce that Armstrong-Smith Consulting has partnered (link) with InfoCaptor to bring you access to a superb dashboard tool that can revolutionize the way you see your Discoverer metadata. I have been using it in-house for a while now and can endorse this tool's ability to make your Discoverer metadata available to a wider audience in a format that you'll love.

With InfoCaptor you have the ability to generate CSV, Excel, HTML and PDF output of your data. You will love this tool - and it's affordable too. For an HTML example of the output from our own database, scroll down or click here.

To purchase or to find out more information, click here.

What can you do with InfoCaptor?

Drop your SQL query in the editor, then quickly create tables and charts
Link up related portlets (tables, charts) using drills
Define parameters and wire up all of your portlets
Generate documentation for your database in PDF, HTML, Excel
Generate Lineage documentation for your Data Warehouse and BI environment
Create briefing books from your dashboard contents, suitable for distributing and presentations
Create quick Dashboard prototypes, no server installation needed

Example Output:

Discoverer 3.1 licenses

2007-03-29T07:01:00.000-05:00

Whether Oracle chooses to accept it or not there are lots of organizations who are still using Discoverer 3.1 and, at least for now, have no intention of upgrading. These organizations all bought legal copies of the software and have done nothing wrong. However, I was amazed to hear that Oracle is refusing to take new revenue from those organizations in an attempt to force them to upgrade.

I was contacted today by one such organization who wanted to purchase additional Desktop licenses for 3.1. When they contacted Oracle they were told that because Oracle no longer sells 3.1 they cannot purchase those additional licenses. They're not asking for new software, they're not asking for support, all they want is to add extra users to their system. Remember that Desktop is priced at $1,000 per named user. This makes me wonder how much revenue Oracle has turned away in the past couple of years since 3.1 went out of support.

Anyway, Oracle's loss of revenue is not the issue in this posting. My dilemma is the advice that I should give to the organization that contacted me? Seeing as Oracle seem to be washing their hands of 3.1, does this give the organization the green light to go ahead and keep adding the extra users until they are ready to upgrade? Do I tell them that they must hurt their own business by only using the licenses they have already purchased? Do I tell them that they must upgrade? Seeing as Desktop has hardly changed since 3.1 and the cost of the license is identical I don't see any reason to force them to upgrade when they are not using Plus. What do you think?

Discoverer 10g Handbook Review

2007-03-28T21:43:00.000-05:00

The December edition of the ODTUG (link) technical journal published a review of our Discoverer 10g Handbook.

Written by Karen Cannell from the Integra Technology Consulting Corporation a copy of this review can be read on our website (link), or if you are a full member of ODTUG you can download a copy of the review by following this link.

Freeing up disk space on XP

2007-03-27T22:37:00.000-05:00

If you're like me and have a laptop with XP you'll know that you don't have a huge hard disk. Thus, keeping that disk space down to the minimum is essential.

Windows XP’s System Restore is a great way to recoverer your system after you’ve made a configuration change or installed new software that your computer doesn’t quite see eye to eye with.

Did you know that every 24 hours, or when you make a change to your system, XP will create a new restore point? Did you also know that these stored restore points can use up to 12% of your hard disk space?

Therefore, should you find that you have a need to free up some of that valuable disk space, what can you do? Well one thing you can do is to delete all but the most recent restore point - providing of course that you are happy with the current setup. To do this you need to use Microsoft's Disk Cleanup utility.

Use the following workflow:

From the Start button, navigate to Programs Accessories System Tools Disk Cleanup
On the Disk Cleanup tab, check or uncheck the files to delete, then click the OK button. I usually allow the system to clear out Downloaded Program Files, Temporary Internet Files, Microsoft Error Reporting Temporary Files, Recycle Bin and Temporary Offline Files
Click the More Options tab
In the System Restore area, click the Clean up button
You will be asked to confirm that you want to delete all but the most recent Restore Point. If you are sure you want to do so, click the Yes button

Memories of Summit 2007

2007-03-22T18:35:00.000-05:00

For the past week, my wife and president of Armstrong-Smith Consulting, Darlene, and I, along with 3 other members of our highly experiended team, Dale Jones, Byron Smith and Stephanie Smith, have been in Las Vegas attending the SunGard Summit 2007 (link) conference. At the conference we had a 10 x 10 booth and discussed our Oracle Discoverer training and consultancy offerings with hundreds of attendees. All in all we had over 500 people attend our booth. During the conference we also gave away an iPod and 4 copies of our Oracle Discoverer 10g Handbook (link).

On Tuesday between 11:30 am and 12:30pm in the South Seas ballroom of the Mandalay Bay Convention Center (link) I presented a paper called A look at the powerful features of Discoverer 10g. This was well received and I am so pleased that so many people came to see me. During the conference I was also able to put a face to many of the SunGard staff whom we have been conversing with during recent months. Don't you think the world is just full of charming people?

For those of you who don't know, SunGard Higher Education (link) provides software, systems implementation and integration, strategic consulting, and technology management services to colleges and universities. Here at Armstrong-Smith Consulting we are delighted to have partnered with SunGard and to be a part of the SunGard Higher Education Collaborative (link). Our partnership allows us to provide report building, training and other consultancy services for Oracle Discoverer on the SunGard ODS and EDW products. We can even help institutions comply with federal and state regulations to ensure that their private data is secured.

Are you a SunGard Higher Education customer?

Do you have the SunGard ODS or EDW?

Do you have Oracle Discoverer?

If you answered yes to the above three questions you need to speak with either your SunGard Higher Education support representative or email us at info@learndiscoverer.com and find out how we can help you make the most of your investment.

Discoverer and IE7

2007-03-17T10:09:00.000-05:00

It seems Oracle have released a patch (5758865) , available on MetaLink, with the fix for IE7. This is a one-off patch and requires a password which means you will have to raise a service request and tell Oracle you are having trouble drilling in Viewer when using IE7. This patch is applicable whether you are using relational or OLAP modeling.

The downside of this patch is that it can only be applied on top of 10.1.2.2. You can get the 10.1.2.2 patch (4960210) also on MetaLink. Be warned though that 10.1.2.2 is not a small patch. It is 1.6 GB in size and is a full upgrade of the 10.1.2.0.2 application server.

Has any body applied the 10.1.2.2 patch yet? I'd be interested in hearing how it went and how long it took. I ask because the installation instructions, when downloaded in Word, are a staggering 189 pages.