Cumulative Patch 4 released for OracleAS Discoverer 10.1.2.55.26

The new cumulative patch for OracleAS Discoverer 10.1.2.55.26 has been just released. You can connect to http://metalink.oracle.com and download it, the id is 7595032.

Opatch is used to install Discoverer Cumulative Patches. The patch readme has all the install/rollback instructions.

I'd like to remind that OracleAS Discoverer 10.1.2.55.26 is a release version obtained by installing the Oracle Application Server PLACEHOLDER patch: 5983622.

Create or Manipulate directly the SQL produced by Discoverer User Edition

In Discoverer User Edition (Desktop, Plus, Viewer) it is not possible to alter directly the SQL produced. The query is dynamically created from Discoverer, depending on items, joins, condition, calculation ... selected during the workbook creation.

The only chance I see here is to create a custom folder in Discoverer Administrator and then create a workbook based on it:

"Custom folders are folders based on a SQL statement which could include SET operators (e.g. UNION, CONNECT BY, MINUS, INTERSECT) or a synonym that you type directly into a dialog.

By defining a custom folder, you can quickly create a folder that represents a complicated result set. When you save the custom folder, Discoverer Administrator creates items using the 'Select' part of the SQL statement you have entered.

In Discoverer Plus, there is no distinction between a custom folder and a simple folder. A Discoverer end user can use a custom folder to build queries in the same way as any other type of folder."

Extracted from :
Oracle® Business Intelligence Discoverer Administration Guide
10g Release 2 (10.1.2.1)
Chapt. 6 Creating and maintaining folders
-- What are custom folders?
(http://download.oracle.com/docs/html/B13916_04/folders.htm#sthref318)

How to change CSV Delimiter in Discoverer

Discoverer does not have the feature to change the delimiter when you export CSV format from it. This has been rejected by Development with the enhancement request :
Bug 3179364 ENH EXPORT CSV FORMAT SHOULD BE ABLE TO SPECIFY THE SEPARATOR
So, Discoverer will always use the comma as delimiter when you export to .CSV format.

Now, to complete the picture, the delimiter is defined on Microsoft Operating System in the Control Panel >> Regional Settings => Numbers tab => List Separator and this is taken for example from Excel, so if you save from excel in .csv the delimiter will be the one defined in the List Separator. In any case, this is not a standard, so the point that it is working this way on Excel does not means that it should works the same on all other softwares, like Oracle Discoverer, this is not a bug in functionality. Generally .csv delimiter is always associated with comma :

http://tools.ietf.org/html/rfc4180
http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm

How to Maintain Alignment for Collapsed Items through Sessions ?

Some questions arrived on why if items which are part of an hierarchy are collapsed, they loose the format definition like alignment ?

Well, this is expected behavior. Indeed, every time an item is removed from the layout and added back in a different session it's treated as completely new item and doesn't matter whether it is part of an hierarchy or not. Indeed, this is because the format layout of items is defaulted in the EUL, and in Discoverer Desktop one can maintain control of the format only on the session not on the default properties like alignment.

So, to make alignment persistent on Discoverer Desktop, the default properties for that item should be changed in Discoverer Administrator :

1.) Open the business area on Discoverer Administrator ;
2.) Expand the folder where the item reside ;
3.) Right Click on that item and select Properties ;
4.) On properties dialog box, select the wanted alignment

Aggregation: DECODE & CASE calculations returns different values between Discoverer Plus and Discoverer Desktop 10.1.2

(This post does not want to discuss how aggregation works, it assume that you already knows aggregation behavior, but it gives you solution to most known scenarios)

From Discoverer 9.0.2 and above i.e Discoverer 9.0.4 and Discoverer 10.1.2 has enhanced roll-up and calculation capabilities a feature called Enhanced Data Aggregation where when connected to a 9.0.1.4 or higher database, the Discoverer server will rely on the CUBE, ROLLUP and GROUPING SET database functionality to compute roll-up values. This new capability correctly handles many of the restrictions described in the provided note, so that a roll-up value can be computed where the 4i server would have to resort to using the "N.A" string.

Additionally, enhanced Data Aggregation is present only in the Discoverer Middle-tier(Plus and Viewer) server -- Not Discoverer Desktop. You will see aggregations in Plus and Viewer that show up as "N/A" in Desktop.

By the way, we can define:

Linear Calculation
A calculation for which the aggregate value is same as the value obtained by summing up the individual values is a linear calculation.

Locally computable Calculation
Discoverer can locally compute all primitive arithmetic operations (+, -, *, / etc), a subset of aggregates (Sum, Sum_Squares, Count, Max, Min, Average and Variance), some string operators, and Decode. Discoverer can locally compute most calculations that can be broken down into linear parts and that involve the above set of operators.
Calculations such as Sum (Profit) / Count (Profit) can be locally computed as it can be broken down into two Linear Parts Sum (Profit) and Count (Profit) combined by the division operator. Calculations such as Sum (Profit) / Count_Distinct (Profit) cannot be locally computed as one of the parts (Count_distinct (Profit)) is not Linear.

Non-Aggregable Calculation
A pre Oracle 9i database allows data to be fetched at only one level of aggregation. From this data discoverer has to compute values at higher levels of aggregation to support things like totals, outline values etc. This is possible only if the calculation is either Linear or Locally computable.
If a calculation is neither linear nor locally computable it will be marked as non-aggregable. The value specified in the option “Show non aggregable values as “ is displayed for such cells.

Case / Decode
Totals on aggregate calculations involving a case expression might yield non-intuitive results. This is because the item values involved in the condition part of the case expression will become NULL when they are rolled up on that item.



In most of cases the solution is to add in the PC Client where Discoverer Desktop is installed regedit entries, like:

o Add two DWORD values named: AggregationBehavior & AllowAggregationOverRepeatedValues = 1 under the key: HKEY_CURRENT_USER\Software\ORACLE\Discoverer 10\Application\ and set both to 1;

Then, Discoverer Desktop should be closed and re-launched.

How To Specify The Owner Of Workbooks On The Viewer Url Parameters ?

On Discoverer Viewer/Plus 9.0.4 it was possible to specify the Owner and Workbook Name on the same url parameter :

wb=.

In 10.1.2 this URL parameter is not getting the Owner anymore, just the workbook name.

This is because, the Discoverer Viewer is not checking whether owner name is prefixed to the workbook.

The fix has introduced a new URL parameter "wbkowner" to specify the Owner's name explicitly.

Hence if the owner needs to be specified then instead of prefixing the owner's name to workbook name the user needs to append new URL parameter "wbkowner=Owner's name".
Since, the "." a is valid character for workbook name, the Discoverer Viewer 10.1.2 wouldn't be able to differentiate whether the string of characters before "." in workbook name is owner's name string or substring of workbookname.

The fix is available on top from 10.1.2.3 + Cumulative Patch 2 onwards, where the parameter "wbkowner" can be used on the URL for Discoverer Viewer.

Discoverer Plus users should wait for internal bug to be fixed.

How to Change the Alignment of Page Item in HTML Exported Report Arabic Workbooks From Left To Right

Incredible grow in IT area has been observed in Arabic countries. That's why I've decided to post this info:

Exporting Workbooks from Discoverer Viewer with Arabic Locale in "Hyper-Text Markup
Language)archived)(*.zip)" format, does not align Page Items with the rest of the report. Indeed,
the body of the report (datapoints) are aligned to the right, page items instead are shown on the
left. How to change alignment for Page Items ?

Open the workbook with Discoverer Plus, highlight the Page Items then right click and select
"Format Heading". When dialog window shows, select right alignment icon and save the workbook.

Download Disco5i.jar File with HTTP Protocol While Discoverer Plus is Configured to Work with HTTPS

Recently I've been requested, due to a configuration restrictions with firewalls, whether it is possible to use Discoverer Plus with SSL (https) and yet have the disco5i.jar download occur through http protocol ? The answer is yes, let me show you how :

Pre 10.1.2.2 + CP7 (and 10.1.2.1.0)

• Modify the $ORACLE_HOME/discoverer/config/configuration.xml file.
  
• Change the 'disco_archive' parameter (for relational) to point to the disco5i.jarjar file via an
  HTTPS URL and 'd4o_double' (for Discoverer for OLAP applet)
  
  Example:
  
  
  FROM:
plugin_setup="https://machine.us.oracle.com:443/jpi/j2re.exe" version="1.4.2" versionie="1,4,2,mn"
type="application/x-java-applet"
plugin_page="http://java.sun.com/products/archive/j2se/1.4.2_04/index.html"
disco_archive="disco5i.jarjar" d4o_archive="d4o_double.jarjar"/>
  
  
  
  TO:

plugin_setup="https://machine.us.oracle.com:443/jpi/j2re.exe" version="1.4.2" versionie="1,4,2,mn"
type="application/x-java-applet"
plugin_page="http://java.sun.com/products/archive/j2se/1.4.2_04/index.html"
disco_archive="http://machine.domain/disco/plus_files/disco5i.jarjar"
d4o_archive="http://machine.domain/disco/plus_files/d4o_double.jarjar"/>
  
  

For environments that include iAS/Discoverer 10.1.2.2 with Cumulative Patch 7 or 8 (CP7 or CP8) and/or iAS/Discoverer 10.1.2.3

• Modify the $ORACLE_HOME/discoverer/config/configuration.xml file jar parameters as shown:
  
  
  
  disco_archive=" http://machine.domain/discoverer_disco/plus_files/disco5i.jarjar"
d4o_archive=" http://machine.domain/discoverer_disco/plus_files/d4o_double.jarjar"
  
  
  
• This change will force the disco5i.jarjar to downloaded using the HTTP protocol.

Discoverer Plus Generate "java.io.FileNotFoundException:disco5i.jar" after Cumulative Patch 7 (6778560) installation

Discoverer Plus 10.1.2.54.25 generate the following exception in Java Console, and cannot be loaded :

java.io.FileNotFoundException: File not found:
https://servername.domain/discoverer_disco/plus_files/disco5i.jar
at sun.plugin.proto .jdk12.http.HttpURLConnection.getInputStream(Unknown Source)
at oracle.jre.protocol.jar.HttpUtils.followRedirects(Unknown Source)
at oracle.jre.protocol.jar.JarCache$CachedJarLoader.download(Unknown Source)
at oracle.jre.protocol.jar.JarCache$CachedJarLoader.load(Unknown Source)
at oracle.jre.protocol.jar.JarCache.get(Unknown Source)
at oracle.jre.protocol.jar.CachedJarURLConnection.connect(Unknown Source)
at oracle.jre.protocol.jar.CachedJarURLConnection.getJarFile(Unknown Source)
at sun.misc.URLClassPath$JarLoader.getJarFile(Unknown Source)
at sun.misc.URLClassPath$JarLoader.(Unknown Source)
at sun.misc.URLClassPath$2.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.misc.URLClassPath.getLoader(Unknown Source)
at sun.misc.URLClassPath.getLoader(Unknown Source)
at sun.misc.URLClassPath.getResource(Unknown Source)
at sun.misc.URLClassPath.getResource(Unknown Source)
at sun.applet.AppletPanel.loadJarFiles(Unknown Source)
at sun.plugin.AppletViewer.loadJarFiles(Unknown Source)
at sun.applet.AppletPanel.runLoader(Unknown Source)
at sun.applet.AppletPanel.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
WARNING: Unable to cache
https://appsppesdev.oracleoutsourcing.com/discoverer_disco/plus_files/disco5i.jar
Could not load progress dialog image:
https://appsppesdev.oracleoutsourcing.com/discoverer_disco/plus_files/plugin/images/download.jpg
load: class oracle.disco.DiscoApplet not found.
java.lang.ClassNotFoundException: java.io.IOException: open HTTP connection failed.
at sun.applet.AppletClassLoader.getBytes(Unknown Source)
at sun.applet.AppletClassLoader.access$100(Unknown Source)
at sun.applet.AppletClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.applet.AppletClassLoader.findClass(Unknown Source)
at sun.plugin.security.PluginClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.applet.AppletClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.applet.AppletClassLoader.loadCode(Unknown Source)
at sun.applet.AppletPanel.createApplet(Unknown Source)
at sun.plugin.AppletViewer.createApplet(Unknown Source)
at sun.applet.AppletPanel.runLoader(Unknown Source)
at sun.applet.AppletPanel.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

The error_log files, shows errors like :

[error] [client 140.85.190.243] [ecid: 82133192743,1] File does not exist: $OH/Apache/Apache/htdocs/disco/discoverer_disco/plus_files/disco5i.jar

Changes

The problem appeared after the installation of the Cumulative Patch 7 on a Complex / Load Balanced environment, with BigIP and security rules.

Cause

Due to the

Bug 5948646 NEED TO CHANGE THE CONTEXTROOT FOR DISCOVERER PLUS&VIEWER

the $OH/discoverer/config/disco.conf has been updated from :

# Virtual path for plus jar and related files
AliasMatch ^/disco/plus_files/(..*)
"$OH/discoverer/plus_files/$1"

to :

# Virtual path for plus jar and related files
AliasMatch ^/discoverer_disco/plus_files/(..*)
"$OH/discoverer/plus_files/$1"

Solution

Change the BigIP security rules for the Virtual Path from /disco/ to /discoverer_disco/

Granting Discoverer EUL Administrative Privileges Via The EULAPI Command-Line To SYSADMIN Fails With ORA-00942

You are granting Administrative privileges to the SYSADMIN user using a similar Discoverer EULAPI command as below:

eulapi -connect eul_us/eul_us@db -grant_privilege -user SYSADMIN
-privilege administration -privilege all_admin_privs -log grantEULAdminPriv.log

When running the command, it fails with ORA-00942: table or view does not exist.

Changes

The following E-Business Suite schemas/users have been renamed:

For Example:

from "APPS" to "APPS_AOL"
from APPLSYSPUB to "AOLPUB"
from APPLSYS to "AOL"

Cause

Several of the standard E-Business Suite users/schemas have been renamed in the database.

Solution

The solution used will depend upon if the schema names were changed before or after the EUL was created.

First, test your connection by specifying the -apps_fndnam parameter using the new "APPS" schema in the EULAPI command-line:

For example:

eulapi -connect eul_us/eul_us@db -apps_fndnam APPS_AOL -grant_privilege -user SYSADMIN -privilege administration -privilege all_admin_privs -log grantEULAdminPriv.log

If the above solution does not work or you receive the error: user 'SYSADMIN' not found, then you may need to re-create the EUL with the new schema names.

For example:

1. Delete the existing EUL
   
   
   Note: This is assuming the EUL is newly created and contains no metadata (Business Areas).
   
   
   
   
   eulapi -connect eul_us/eul_us@db -delete_eul -connect <**********> -delete_eul

   
   
2. Create a new applications mode EUL using the new schema name, apps_aol in this example:
   
   
   eulapi -CREATE_EUL -APPS_MODE -CONNECT system/pwd@db -USER EUL_US -PASSWORD EUL_US
-DEFAULT_TABLESPACE discoverer
-TEMPORARY_TABLESPACE TMP_123
-EUL_LANGUAGE US
-APPS_GRANT_DETAILS apps_aol/fnd
-log EULcreation.log

   
   
   
3. Connect using the the new schema name, apps_aol in this example:
   
   
   eulapi -connect eul_us/eul_us@dbetfi8i -apps_fndnam APPS_AOL -grant_privilege -user SYSADMIN -privilege administration -privilege all_admin_privs -log grantEULAdminPriv.log

Exception "oracle.bali.ewt.spinBox.NumericSpinBoxModel.setValue" on Discoverer Plus 10.1.2

On top of Discoverer Plus 10.1.2, some actions (like right click on Worksheet Name =>
Properties ; Tools => Options ) cannot be performed because of the following exception is returned in the java console :

Error: java.lang.IllegalArgumentException java.lang.IllegalArgumentException
at oracle.bali.ewt.spinBox.NumericSpinBoxModel.setValue(Unknown Source)
at oracle.bali.ewt.spinBox.NumericSpinBox.setIntValue(Unknown Source)
at oracle.disco.options.QueryPanel._setInitialValues(Unknown Source)
at oracle.disco.options.QueryPanel.(Unknown Source)
at oracle.disco.options.OptionsPanel._initMainPanel(Unknown Source)
at oracle.disco.options.OptionsPanel.(Unknown Source)
at oracle.disco.options.OptionsUI.runDiscoWorker(Unknown Source)
at oracle.disco.worker.DiscoWorker.runWorker(Unknown Source)
at oracle.bali.ewt.worker.AbstractWorker.run(Unknown Source)
at oracle.bali.ewt.worker.AbstractWorkerQueue._runWorker(Unknown Source)
at oracle.bali.ewt.worker.AbstractWorkerQueue.access$100(Unknown Source)
at oracle.bali.ewt.worker.AbstractWorkerQueue$WorkerContext._run(Unknown Source)
at oracle.bali.ewt.worker.AbstractWorkerQueue$WorkerContext._runMaybeBlocked(Unknown Source)
at oracle.bali.ewt.worker.AbstractWorkerQueue$WorkerContext.run(Unknown Source)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

-- Steps To Reproduce:
1.) Open Discoverer Plus and Create new Workbook ;
2.) Try to right click on worksheet name => Properties
3.) Exception displayed in the Java Console

Cause

The preference "RowFetchLimit" has been set to "0" in the pref.txt file

Although this parameter is of no sense, since the preference "RowFetchLimitEnabled" is disabled
(=0) in the pref.txt file, we cannot set preferences to not allowed values.

This can occurs with any preference value in the pref.txt set outside the range, which can be retrieved from the pref.txt comments.

Solution

1.) Stop Application Server (opmnctl stopall) ;
2.) [Only for Unix Platform] Set the environment for discoverer ( $ . ./discwb.sh DOT SPACE DOT SLASH discwb.sh) ;
3.) Edit pref.txt and set RowFetchLimit = 1 - then save the file ;
4.) Apply the new preference ( Unix: $ applypreferences.sh ; Windows : c:\> applypreferences.bat) ;
5.) Restart the Application Server (opmnctl startall).

Is it possible to add a bitmap to a Workbook in Discoverer Desktop and then let this bitmap visible on Discoverer Plus ?

Currently this feature is available in Desktop only, and not implemented yet in PLUS version So
when saving a bitmap in Desktop it will not appear in PLUS.
There is an Enhancement Request 3996567 for this issue. (BITMAP IMAGES LOGO IN PLUS/VIEWER)
This feature is approved for future release by discoverer product management but at the time of writing this document there is no a fix date .

However, a logo could be added in PLUS to the applet window for all users regardless of the
workbooks, this could be done through the Enterprise manager
Application Control :
Middle tier >> Discoverer >> Discoverer Plus >> Logo >> File then browse
to the desired logo file

for Discoverer viewer, you can also add a logo to the main browser window for all users and
regardless of any workbook:
this could be done through the Enterprise manager Application Control :
Middle tier >> Discoverer >> Discoverer Viewer >> customization >> select
Layout then press GO >> Logo >> File then browse to the desired logo file

for more information, please review :
Oracle® Business Intelligence Discoverer Configuration Guide
10g Release 2 (10.1.2.1) for Microsoft Windows and Solaris Operating System (SPARC)
ch: 9 Customizing OracleBI Discoverer
9.2.3 How to change the default Discoverer Viewer layout
http://download-uk.oracle.com/docs/html/B13918_03/customize.htm#BABFHDJI

ERROR_LOG FILE GETTING ERROR MESSAGES "FILE DOES NOT EXIST" for Discoverer files

This applies to Oracle Discoverer - Version: 4.1 to 10.1.2
This problem can occur on any platform.

Discoverer Plus and Viewer are working as expected , but numerous Discoverer related messages are present in the Apache error_log. The Discoverer server seems to flood the error _log with multiple "file does not exist" messages.

These, or similar, errors are observed in the log file when running Discoverer Plus and Viewer 4i :

File does not exist: %iAS_HOME%/Apache/Apache/htdocs/discwb4/applet/sun/java2d/SunGraphicsEnvironment.class
...
File does not exist:
%iAS_HOME%/Apache/Apache/htdocs/discwb4/applet/oracle/disco/DiscoAppletBeanInfo.class
...
File does not exist:
%iAS_HOME%/Apache/Apache/htdocs/discwb4/applet/java/awt/KeyboardFocusManager.class
...
File does not exist:
%iAS_HOME%/Apache/Apache/htdocs/discwb4/applet/dummyToClearIE404Error
...
File does not exist:
%iAS_HOME%/Apache/Apache/htdocs/discwb4/applet/oracle/disco/model/
corbaserver/ServerStatsImplBeanInfo.class
...

Discoverer 10g can show the following errors :

File does not exist: %iAS_HOME%/discoverer/plus_files/com/ms/lang/RegKeyException.class
File does not exist: %iAS_HOME%/discoverer/plus_files/oracle/disco/resource/ParamInfoBundle_it_IT.class
File does not exist: %iAS_HOME%/discoverer/plus_files/oracle/disco/resource/ParamInfoBundle_it_IT.properties
File does not exist: %iAS_HOME%/discoverer/plus_files/java/awt/KeyboardFocusManager.class
File does not exist: %iAS_HOME%/discoverer/plus_files/oracle/dss/dataView/resource/DataviewBundle_it_IT.class
File does not exist: %iAS_HOME%/discoverer/plus_files/oracle/dss/dataView/resource/DataviewBundle_it_IT.properties
File does not exist: %iAS_HOME%/discoverer/plus_files/oracle/dss/gridView/resource/GridViewBundle_it_IT.class
File does not exist: %iAS_HOME%/discoverer/plus_files/oracle/dss/gridView/resource/GridViewBundle_it_IT.properties
File does not exist: %iAS_HOME%/discoverer/plus_files/oracle/dss/crosstab/resource/CrosstabBundle_it_IT.class
File does not exist: %iAS_HOME%/discoverer/plus_files/oracle/dss/crosstab/resource/CrosstabBundle_it_IT.properties
File does not exist: %iAS_HOME%/discoverer/plus_files/oracle/dss/util/resource/UtilBundle_it_IT.class
File does not exist: %iAS_HOME%/discoverer/plus_files/oracle/dss/util/resource/UtilBundle_it_IT.properties
File does not exist: %iAS_HOME%/discoverer/plus_files/oracle/dss/table/resource/TableBundle_it_IT.class
File does not exist: %iAS_HOME%/discoverer/plus_files/oracle/dss/table/resource/TableBundle_it_IT.properties
File does not exist: %iAS_HOME%/discoverer/plus_files/oracle/ewt/util/LocaleUtils.class
File does not exist: %iAS_HOME%/discoverer/plus_files/oracle/dss/graph/resource/GraphBundle_it_IT.class
File does not exist: %iAS_HOME%/discoverer/plus_files/oracle/dss/graph/resource/GraphBundle_it_IT.properties

Cause

The problem is related to a JDK, that looks in the wrong directories for the above Discoverer files. It does not affect the operation of the Discoverer server.

Solution

The messages in the error_log can be disregarded since they do not affect Discoverer availability.

The messages can also be suppressed by changing the Apache warning level.

Complete the following steps to suppress the logging of warning messages:

1. Stop the Oracle Discoverer and HTTP Server services.

2. Delete or rename the existing Apache error_log and the access_log files.

3. Open httpd.conf file in the Apache\Conf directory.

4. Set the LogLevel to crit. By default this should be set to warn. This will prevent
the warning messages from getting logged into the error_log file.

LogLevel crit

5. Also add the following lines

SetEnvIf Request_URI "\.class$" dontlog
CustomLog logs/access_log common env=!dontlog

- instead of

CustomLog logs/access_log common

6. Save the httpd.conf file

7. Restart the OracleHTTP Server and Discoverer services.

Now try accessing Discoverer Plus and see that the log files are not flooded with warning messages.

For additional information refer to http://httpd.apache.org/docs/logs.html


DISCLAIMER: Implementing this workaround, all the logs will not trace any "file does not exist" errors for ALL .class files. You need to be aware of all the ramifications of never seeing "file does not exist" if it involves a .class file. It could be a real error for other components, that may be prevented from be traced.

Database View and Custom Folders does not returns the same results in Discoverer

When running a workbook against a Database View with the same SQL as a Discoverer Custom Folder, the Workbook returns the expected results. If however, the same Workbook is built on top of a Discoverer Custom Folder, which is exactly a copy of the Database View, the value returned is different and it is not correct.

Changes

Here is an example of how to recreate the problem :

1. Create the Database View :
   
   CREATE VIEW DISCO_CALCULATION_VW AS SELECT 1950 AS annual_hours , 52 AS period_divisor , 37.5 AS normal_hours , 10000 AS fte_annual_salary FROM dual
   
   
2. Create a Discoverer Database Folder on this created View ;
   
3. Create a Discoverer Custom Folder with exactly the same code ;
4. On both the folder add the following Calculated Item :
   
   ( Normal Hours/Standard Hours )*Fte Annual Salary
   
   where
   Standard Hours = Annual Hours/Period Divisor
   
   
5. Create a Workbook on both the Folders using Discoverer Desktop. The results for the calculated items will be different.
   

Cause

For the Workbook built on the Discoverer Custom Folder the sql executed is

"SELECT 1950, 10000, 37.5, 52, 1950/52, ( 37.5/1950/52 )*10000 FROM DUAL DUAL"

This means that the order given to the calculation is not working logically as expected and as returned in the database view.

Solution

To make the behavior to be the same on both the folders, the order of the calculation evaluated can be forced by changing the calculation from

"( 37.5/1950/52 )*10000"

to

"( 37.5/(1950/52))*10000"

which in turns began a modification of the Calculation item Standard Hours from :

Annual Hours/Period Divisor

to

(Annual Hours/Period Divisor)

Cumulative Patch Certification with Applications

Numbers of questions coming for information about the certification of Cumulative Patches with Applications installation. First of all, it is important to clarify the types of Discoverer solutions to 'Apps certification' :

1. Custom Solution
   - Custom solution is built by customer and is not dependent on any 'Apps certification' ;
   - Custom solution is simply using the Discoverer native functionality to connect to EBS DB.
   
2. (2) BIS Solution
   - BIS is an Oracle pre-built and shipped with EBS solution.
   - This solution comprises of BAs, folders, workbooks, etc.
   - The 'Apps certification' means that BIS Dev team tests all BAs and workbooks with a new Discoverer version against EBS.
   

In any case customers using Discoverer 10.1.2.2 against EBS 11i are still fully supported and all new problems will be handled normally even reported to Development as bugs if needed. Concerning the Cumulative Patches, you can go for any Cum. Patch version higher than Cum.Patch 3 (6155500) (You may want to skip Cum.Patch 5 6472361, which I see is not fully stable).

AS Patch Set 10.1.2.3

Application Server/Developer Suite Patch Set 10.1.2.3.0 is production on Windows and Solaris.

Patch 5983622
PLACEHOLDER BUG FOR AS/DS 10.1.2.3 PATCHSET RELEASE
Release iAS 10.1.2.3
Product Oracle Application Server Products

This is used patching Admin/Desktop on Windows too.

Note Discoverer 10.1.2.3 is not certified with EBS as well as bug fixes into cp4, cp5 and cp6 are not fixed in 10.1.2.3. CP for 10.1.2.3 will include all the missing bug fixes on top of 10.1.2.3.

Connect to Discoverer Plus/Viewer is Very Slow in the Connection Phase

OracleAS Discoverer Plus 10.1.2 installation, is really slow in the connection phase, both from APPS links and Standalone connection. After the insert of the login details in the Connect Box it is showing for approx 20 minutes the following page :

Request Progress

Processing your request
The page you have requested is taking some time to process. Please wait...

The Cause is that the Connect descriptor resolve slowly as it did not contain a valid entry.

Having the following entry in the sqlnet.ora :

NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, EZCONNECT, ONAMES, HOSTNAME)

will delay the authentication, as it will first try to authenticate the connect descriptor through OID, where it does not exist.

A simple configuration gain is to change the way the client looks up the connect descriptor. This connect descriptor must be translated by Oracle Net into the network protocol location of the database (IP address and port number) and the name of the database service.

There are a variety of methods to store this information, such as in a TNSNAMES.ORA (TNSNAMES) file, Oracle Internet Directory (OID - LDAP), Oracle Names (ONAMES) or using the Host Naming Adapter.

The default connect descriptor lookup path is to first look in the local naming file TNSNAMES.ORA, then to try the well-known Oracle Names servers, and then finally to try the Host Naming Adapter. The parameter that influences this lookup order is NAMES.DIRECTORY_PATH parameter in the SQLNET.ORA file.

-- To implement the solution, Update the sqlnet.ora file in the Discoverer ORACLE_HOME from :

NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, EZCONNECT, ONAMES, HOSTNAME)

to

NAMES.DIRECTORY_PATH= (TNSNAMES)

if the connect descriptor is configured to be resolved via tnsnames.ora, otherwise order the list with the right connect descriptor which is used first.

Cumulative Patch for Oracle Discoverer 10.1.2.2

This will be implemented for iAS 10.1.2.2 (Discoverer 10.1.2.54.25) version going forward. This is only for Plus/Viewer. (D40 patches will also be using this cumulative model)
Discoverer Admin/Desktop 10.1.2.54.25 (part of iDS 10.1.2.2) will continue to use the current patching model. Previous versions, iAS 10.1.2.0.2 / 10.1.2.1 (Discoverer 10.1.2.48.18
or 10.1.2.50.05) will continue to use the current patching model.


First Cumulative Patch (for example, let's call it PATCH1) contains 70 bug fixes over 10.1.2.2. Or in other words, PATCH1 = 10.1.2.2 + 70 bug fixes If second Cumulative Patch (PATCH2) request MLR receives 30 bugs, PATCH2 will end up containing 70 + 30 = 100 bug fixes over 10.1.2.2 or, in other words, PATCH2 = 10.1.2.2 + PATCH1 + 30 bug fixes and so on for PATCH3 ... PATCH3 = 10.1.2.2 + PATCH2 + x bug fixes

Opatch is used to install Discoverer Cumulative Patches. The patch readme has all the install/rollback instructions.


Patch 5943426 - CUMULATIVE PATCH I: (AVAILABLE FOR SOLARIS, LINUX 32BIT, WINDOWS 32BIT, AIX 5L 64BIT)

Patch 6129303 - CUMULATIVE PATCH II: (AVAILABLE FOR SOLARIS, LINUX 32BIT, WINDOWS 32BIT, HPUX 64BIT)

Patch 6155500 - CUMULATIVE PATCH III: (AVAILABLE FOR LINUX/INTEL, SOLARIS-64, WINDOWS/NT, AIX 5L 64BIT, HPUX 64BIT, HP/IA64 (HP UX ITANIUM))

Patch 6357481 - CUMULATIVE PATCH 4 : (AVAILABLE FOR LINUX/INTEL, SOLARIS-32, WINDOWS/XP, AIX 5L 64BIT, HPUX 32BIT, HP/IA64 (HP UX ITANIUM))

Patch 6472361 - CUMULATIVE PATCH 5: (AVAILABLE FOR HP ITANIUM IA64, LINUX 32BIT, WINDOWS 32BIT, SOLARIS 64BIT (23), AIX 5L 64BIT)

Patch 6669003 - CUMULATIVE PATCH 6 : (AVAILABLE FOR WINDOWS, SOLARIS-32, LINUX/INTEL, IBM/AIX-5L, HP-32, HP/IA64 (ITANIUM))

Cumulative Patch 7 - ETA not available

Patch 67xxx60 - CUMULATIVE PATCH 7 : (AVAILABLE FOR LINUX 32BIT, AIX 5L 64BIT, SOLARIS-32, WINDOWS 32BIT)

Track Portal Page where Discoverer Portlet are stored

For customers having discoverer portlets it's an easy task to confirm whether they are executing correctly or not discoverer portlets by querying the PTM5_CACHE table on the Infrastructure. But it could be a nightmare if they have hundreads of these portlets and they want to open it through the Portal site. Indeed, how to find which is the Page where they are stored ? Following is a query that query Portal Page containing Discoverer Portlets :

Select Portal.WWPOB_Page$.Name
from Portal.WWPOB_Page$
where (Portal.WWPOB_Page$.ID ,Portal.WWPOB_Page$.SiteID) in
(Select Portal.WWV_Things.CornerID, Portal.WWV_Things.SiteID
from Portal.WWV_Things
where Portal.WWV_Things.Portlet_Inst_GuID in
(Select Portal.WWPOB_Portlet_Inst$.Portlet_Inst_GuID
from Portal.WWPOB_Portlet_Inst$
where Portal.WWPOB_Portlet_Inst$.Provider_ID =
and Portal.WWPOB_Portlet_Inst$.Portlet_ID = ));

You need to substitute the PORTLET_ID and PROVIDER_ID value with your own value. Those can be obtained with procedure explained above:

1. Login to Portal as the portal or orcladmin user;
2. Navigate to Administer -> Portlets;
3. Select the 'Display Portlet Repository' link;
4. Locate the provider containing your discoverer portlet (this may be under the Portlet Staging Area)
5. When the page with list of portlets (Discoverer Gauges, Discoverer Worksheet, Discoverer List of Worksheets) is displayed, click on the "Edit" link you see on the top right and you will get properties for this portlet (Portlet_id included) ;

To get filter data, pointing to specific workbook, you can query the Infrastructure DB by the following :

Select Distinct CacheTable.Cch_Url, Portal.WWPOB_Page$.Real_Name
from Portal.WWPOB_Page$, Discoverer5.Ptm5_Cache CacheTable
where (Portal.WWPOB_Page$.ID ,Portal.WWPOB_Page$.SiteID) in
(Select Portal.WWV_Things.CornerID, Portal.WWV_Things.SiteID
from Portal.WWV_Things
where Portal.WWV_Things.Portlet_Inst_GuID in
(Select Portal.WWPOB_Portlet_Inst$.Portlet_Inst_GuID
from Portal.WWPOB_Portlet_Inst$ Where Portal.WWPOB_Portlet_Inst$.Ref_Path in
(select Discoverer5.Ptm5_Instance.Ins_Id
from Discoverer5.Ptm5_Instance
where Discoverer5.Ptm5_Instance.Ins_Surrgt_Pk in
(select Discoverer5.Ptm5_Cache.Cch_Ins_Surrgt_Fk
from Discoverer5.Ptm5_Cache
where Discoverer5.Ptm5_Cache.Cch_Ins_Surrgt_Fk = CacheTable.Cch_Ins_Surrgt_Fk
and Discoverer5.Ptm5_Cache.Cch_Surrgt_Pk = CacheTable.Cch_Surrgt_Pk
and Discoverer5.Ptm5_Cache.Cch_Id = CacheTable.Cch_Id
and Discoverer5.Ptm5_Cache.Cch_Url = CacheTable.Cch_Url
and TO_CHAR(CCH_NEXT_UPDATE,'DD-MON-YY') != '02-JUN-01'))))
order by Portal.WWPOB_Page$.Real_Name;

In this case I took out the failing portlets with Update Date = '02-JUN-01'.

Aggregation : What Happens with Fan-Trap ?

I have received recently a claim from a customer regarding the aggregation behaviour of his workbook. In particular he was afraid to see an item on the Group By section clause of the Discoverer Query. At first look I immediately recognized that the architecture selected for his workbook was resulting in a fan-trap schema and so could generate ambiguous data, but due to his insistence I reviewed inside his workbook the reason of this. So, let's first define what is a fan-trap and its rules.

What are fan traps, and how does Discoverer handle them?

A fan trap is a group of joined database tables that might return unexpected results. The most common manifestation of a fan trap occurs when a master table is joined to two or more detail tables independently. Figure shows a simple fan trap schema





Although this construction is relationally correct, you are likely to return incorrect results if you use a straightforward SQL statement to aggregate data points. However, if you use Discoverer to aggregate the data points, Discoverer will never return incorrect results, provided fan trap detection is enabled. Every query that Discoverer generates is interrogated for potential fan traps. If a fan trap is detected, Discoverer can usually rewrite the query using inline views to ensure the aggregation is done at the correct level. Discoverer creates an inline view for each master-detail aggregation, and then combines the results of the outer query.

In some circumstances, Discoverer will detect a query that involves an unresolvable fan trap schema, as follows:

• if the detail folders use different keys from the master for the join
• if there is a direct join relationship between the detail folders (thereby creating an ambiguous circular relationship)
• if non-aggregated values are chosen from more than one of the detail folders if more than one detail folder has a separate join relationship to a different master folder
• if more than one detail folder has a separate join relationship to a different master folder

How the aggregation works in fan-traps ?

Discoverer interrogates the query, detects a fan trap, and rewrites the query to ensure the aggregation is done at the correct level. Discoverer rewrites the query using inline views, one for each master-detail aggregation, and then combines the results of the outer query.

Now, back to the customer issue, I verified the following schema in the Workbook (in green the folders which items are used in the Workbook) :


Each one of this folder, joins the other folders (or Master Folders) on a different keys, generating a fan-trap schema. So, if he creates a crosstab workbook with the following layout :







The aggregation will be made on

• Department.Item (Because it is Page Item and rollup at page item level)


• Customers.Item (Because it is an axis item and rollup at row level)


• Product Details.Item (Because it is an axis item and rollup at row level)

but will add to the Group-By expression also the Sales SUM.Item, because by looking at customer schema, this item is from Sales SUM folder, which is detail of Stores folder which is the master of Products folder, which is iself master of Product Details, for which items are in the workbook. So, basically Sales SUM Item is interpreted in this case as Master of involved items, and so added to the Group By clause, as the aggregation is made for each master-detail relationship.

The only solution here is to create a custom folder just for this workbook, otherwise one should re-architecture the joins so that workbook will not be built on a fan-trap schema.