Tuesday, June 10, 2008

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.


Anonymous said...

Why is it that these patches such as 6778560 are password protected on Metalink?

Michael said...

I believe Oracle is attempting to ensure that folks pay for the appropriate licenses. As you know, there is no charge to download any Oracle base software from OTN.

However, you must have a MetaLink account and access to a CSI number to even see a patch. When you submit a service request for a password Oracle checks whether you have purchased the relevant support for the product you are trying to patch.

Personally I think this is a reasonable way for Oracle to protect its investment.

Microsoft do a similar thing because they don't let you download ugrades unless they can verify that you have a legitimate license.