Ask Michael in 2009

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

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

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

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

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

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

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

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

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

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

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

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:

1. Installing the Admin software
2. Setting up Discoverer to work with E-Business Suite
3. Patching Discoverer Admin to the correct level
4. Creating an E-Business Suite End User Layer
5. Setting up E-Business Suite privileges

Cumulative Patch 8 addendum

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

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:

• Useful Patch Numbers
• DO not upgrade to Discoverer 10.1.2.3

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

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:

1. Microsoft Windows 32-bit - using 7111816
2. Sun Solaris SPARC 32-bit (which is also good for 64-bit) - using 7306816
3. Linux x86 - using 7306816
4. 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

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

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

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:

1. Log in to SQL Plus as the owner of the EUL
2. Type this @c:\oracle\bitoolshome_1\discoverer\util\eulstdel.sql and press Enter
3. 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!
4. 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

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

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:

1. If Discoverer Plus is running, exit from Plus
2. 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
3. Navigate to $ORACLE_HOME\discoverer\util
   In Windows: ORACLE_HOME will usually be C:\Oracle\BIHome1
   
4. Edit the file called pref.txt – it may just be called pref if you are doing this inside Windows Explorer
5. 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.
   
   
6. Save the file
7. 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)
8. Navigate back to $ORACLE_HOME\opmn\bin
9. 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

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

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

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

Discoverer 10.1.2.3 update

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

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

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:

1. Do not use McAfee - probably not an option for many users if the corporate policy is to use McAfee
2. Disable the ScriptScan feature of McAfee across the enterprise - there may well be some resistance to this from your IT department
3. Do not use IE6 but use Firefox
4. 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.

1. First of all, right-click on the McAfee icon in the taskbar. You will see the following pop-up
   
2. From the pop-up, select On-Access Scan Properties. The following dialog box will be displayed.
   
3. Click on the ScriptScan tab
4. Uncheck the box called Enable ScriptScan
5. 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

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:

1. Microsoft Windows (32-bit)
2. Linux x86
3. IBM AIX (5L) Based Systems (64-bit)
4. Sun Solaris SPARC 32-bit, can also be installed on Sun SPARC 64-bit
5. Sun Solaris x86
6. HP-UX PA-RISC (32-bit)
7. HP-UX Itanium
8. 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:

1. Microsoft Windows (32-bit)
2. Linux x86
3. IBM AIX (5L) Based Systems (64-bit)
4. Sun Solaris SPARC 32-bit, can also be installed on Sun SPARC 64-bit
5. Sun Solaris x86
6. HP-UX PA-RISC (32-bit)
7. HP-UX Itanium
8. 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

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

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:

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

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

1. Navigate to the $ORACLE_HOME\opmn\bin folder
2. Execute this command: opmnctl status
3. 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:

1. Navigate to the $ORACLE_HOME\opmn\bin folder
2. 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

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.