|
Oracle® Developer Suite Release Notes
10g (9.0.4) for Windows, Linux, Solaris, and HP-UX Part No. B10668-05 |
|
|
|
|
Oracle® Developer Suite Release Notes
10g (9.0.4) for Windows, Linux, Solaris, and HP-UX Part No. B10668-05 |
|
|
|
|
This chapter discusses issues and workarounds for Oracle Discoverer Administrator (Discoverer Administrator). The chapter includes the following topics:
This section describes general issues and their workarounds for Oracle Discoverer Administrator.
If you install Oracle Discoverer Administrator 9.0.4 on the same computer as Discoverer Administrator 9.0.2, the following error message will be displayed if you attempt to start Discoverer Administrator 9.0.2 from the Windows Start menu:
Cannot find a required file : dcmresus.msb.
The message is displayed because of a problem loading certain libraries on a computer with multiple Oracle homes. The same message will also be displayed if you attempt to start Discoverer Administrator 9.0.2 by double-clicking on dis51adm.exe in Windows Explorer.
To work around this problem, do one of the following:
Workaround 1: Run Discoverer Administrator 9.0.2 from the Windows command line, as follows:
From the Windows Start menu, choose Command Prompt to display the Command Prompt window.
Enter the following at the command prompt:
902_oracle_home\discoverer902\bin\dis51adm.exe
where 902_oracle_home is the directory in which Discoverer Administrator 9.0.2 was installed.
Workaround 2: Run Discoverer Administrator 9.0.2 from a batch file, as follows:
Open a new file in a text editor (for example, Notepad).
Enter the following in the new text file:
dis51adm.exe
Save the file in the directory 902_oracle_home\discoverer902\bin, and give the file a .bat suffix (for example, startdiscoadmin902.bat).
To start Discoverer Administrator 9.0.2, you can do either or both of the following:
Double click the new .bat file in Windows Explorer.
Replace the existing shortcut to Discoverer Administrator 9.0.2 in the Programs menu with a shortcut to the new .bat file.
|
Note: The above workarounds assume that you have already used the Oracle Home Selector (typically required for any computer with multiple Oracle homes). |
If you are accessing data from a non-Oracle data source using Discoverer Administrator (for example to create a business area) and ORA-3113 errors are displayed, do one of the following:
The Oracle Discoverer Administrator Administration Guide recommends setting the JOB_QUEUE_INTERVAL parameter to 600. However, 600 is only suitable if you are not using Discoverer with Oracle Applications. If you are using Discoverer with Oracle Applications, keep the JOB_QUEUE_INTERVAL parameter set to the default value of 90.
The BATCHUSR.SQL script (located in oracle_home\discoverer\sql) creates a schema in which to store the results of scheduled workbooks (referred to as the batch repository user in earlier versions of Discoverer).
The BATCHUSR.SQL script (located in oracle_home\discoverer\sql) has changed between Discoverer 9.0.2 and 9.0.4.
The updated BATCHUSR.SQL script provides two benefits:
The script tightens the database security surrounding the creation and management of the scheduled workbook results schema. The script simply removes certain privileges that were found to be unnecessary.
The script increases the size limit for the SQL statement used by scheduled workbooks from 32KB to 64KB. The increased size limit reflects the increased limit supported by the Oracle database. The increase specifically benefits users who receive the error message "Exceeded Maximum query size" when scheduling a workbook.
The above updates are non-destructive and do not affect any currently scheduled workbooks.
Oracle recommends that all customers using an EUL created with Discoverer 9.0.2 apply these fixes. Simply connect to the database as a database administrator (for example, SYSTEM) and run the BATCHUSR.SQL script.
No action is required for customers using an EUL created with Discoverer 9.0.4, because you will use the updated BATCHUSR.SQL script when you create the scheduled workbook results schema.
This section describes useful information for Oracle Discoverer Administrator.
If you are upgrading from Oracle Discoverer 3.1, the Oracle Discoverer Administrator Administration Guide documents the requirement to upgrade the EUL to Discoverer 4.1 as an intermediate step.
For the purposes of this upgrade, if you are not already in possession of Oracle Discoverer Administration Edition 4.1, it will be made available on the Oracle Technology Network (http://otn.oracle.com).
Do NOT manually edit the End User Layer (EUL) tables or their contents (for example by using SQL*Plus), unless specifically directed to do so by Oracle Support or Development. Manual editing of the EUL tables is not supported and any manual changes can result in a corrupt EUL.
Previous versions of Oracle Discoverer shipped with the files HWOnline.htm and Memo.doc, which were used to demonstrate "drill out" functionality. To reduce disk space requirements and installation time, these files and instructions for their use will be made available from the Oracle Technology Network (http://otn.oracle.com).
When using some of the commands provided by the Oracle Discoverer EUL Command Line for Java, you might want to explicitly set certain Discoverer registry variables, as shown below:
| Command | Discoverer Registry Variable |
|---|---|
| -export |
|
| refresh_object |
|
| -import |
|
| -connect -apps_user |
|
| -load |
|
| all commands |
|
On Windows platforms, Discoverer registry variables are stored in the Windows Registry. On Linux, Solaris, and HP-UX platforms, Discoverer registry variables are stored in the .reg_key.dc file.
To set these Discoverer registry variables, do one of the following:
For Windows only: Specify values for registry variables as required by editing the Windows Registry using regedit or regedt32.
For Windows, Linux, Solaris, and HP-UX: Specify values for registry variables as required by entering the following at a command prompt:
dis51pr -setadminpref registry_variable_name value
For more information about Discoverer registry variables (including descriptions, default values and allowable values), see:
Oracle Discoverer Administrator Administration Guide
Oracle Application Server Discoverer Configuration Guide
The following Discoverer registry variables are available, in addition to those documented in the Oracle Discoverer Administrator Administration Guide and the Oracle Application Server Discoverer Configuration Guide:
| Category | Discoverer registry variable | Description | Default | Valid Values |
|---|---|---|---|---|
| Database | DefaultPreserve DisplayProperty ForRefresh | When refreshing, specifies whether an updated item description is discarded and replaced with the original description, or whether the updated description is retained | 0 |
|
| Database | EnableTriggers | Specifies whether database triggers are disabled.
For example, Discoverer attempts to make a database call for every new column found during a refresh. If this value is set to 0, Discoverer will not make the database call. |
0 |
|
| Database | ExportJoinFrom Master | When exporting a business area, specifies whether joins from the master folder are also exported. | 0 |
|
| Database | SetNULLItem HeadingOnBulk Load | When performing a bulk load, specifies whether item headings are set to the same value as item display names, or set to null. | 0 |
|
To run scheduled workbooks, the scheduled workbook results schema requires SELECT privilege on the synonym v$parameter. SELECT privilege on the synonym v$parameter is gained by having SELECT privilege on the underlying view v_$parameter.
However, the v_$parameter view does not have public access and is owned by the SYS user. Therefore, to grant SELECT privilege on v_$parameter (and, by extension, on the v$parameter synonym) to the scheduled workbook results schema, you must log in as the SYS user and explicitly grant the privilege.
For more information about the scheduled workbook results schema, see the section "How to specify the owner of the tables containing scheduled workbooks results" in chapter 7 of the Oracle Discoverer Administrator Administration Guide.
How you grant the SELECT privilege on v_$parameter will depend on the version of the Oracle database that you are using, as follows:
If you using an Oracle9i database:
Start SQL*Plus (if it is not already running) and connect as the SYS user (or as a user to which the SYSDBA privilege has been granted).
For example, if SQL*Plus is already running, you might type the following at the command prompt:
SQL> CONNECT SYS/sys_pw@database AS SYSDBA;
where sys_pw is the password of the SYS user. If you are unable to login as the SYS user or are unsure about the SYS password, see your database administrator.
Type the following at the command prompt:
SQL> grant SELECT on v_$parameter to user;
where user is the name of the scheduled workbook results schema.
If you are using a version of the Oracle database earlier than Oracle9i:
On the database server computer, start the DBA facility and login as the SYS user (for example, on a Windows computer, choose Run... from the Windows Start menu and enter SVRMGRL).
In the DBA facility, enter connect internal.
Enter the following at the command prompt:
SQL> grant SELECT on v_$parameter to user;
where user is the name of the scheduled workbook results schema.
To verify that the scheduled workbook results schema has been granted SELECT privilege on v_$parameter:
Start SQL*Plus (if it is not already running) and connect as the scheduled workbook results schema.
For example, if SQL*Plus is already running, you might type the following at the command prompt:
SQL> CONNECT user/user_pw@database;
where user is the user name of the scheduled workbook results schema and user_pw is the password of the scheduled workbook results schema.
Type the following at the command prompt:
SQL> select count (*) from v$parameter;
If SELECT privilege on the v_$parameter view has been successfully granted to the scheduled workbook results schema, SQL*Plus displays the number of rows from the v$parameter synonym.
If SELECT privilege on the v_$parameter view has not been successfully granted to the scheduled workbook results schema, SQL*Plus displays the message:
ORA - 01031 Insufficient privileges
To run the Oracle Discoverer EUL Command Line for Java on Linux, Solaris, or HP-UX, a number of environment variables must be set correctly. You can either set the environment variables explicitly using the discwb.sh script or implicitly using the eulapi script, as described below:
to explicitly set the environment variables before issuing Discoverer EUL Command Line for Java commands, enter the following at the command line prompt from which you will be issuing those commands:
oracle_home_path/discoverer/discwb.sh
Subsequently, you can enter Discoverer EUL Command Line for Java commands as follows:
java -jar oracle_home_path/discoverer/lib/eulbuilder.jar -command_list
For example, to refresh a folder called "Sales1", you might enter the following:
java -jar oracle_home_path/discoverer/lib/eulbuilder.jar -connect jchan/12345@my_database -refresh_folder Sales1
to implicitly set the environment variables when you run Discoverer EUL Command Line for Java commands, use the eulapi script in the directory oracle_home_path/bin to run commands. For example, to refresh a folder called "Sales1", you might enter the following:
oracle_home_path/bin/eulapi -connect jchan/12345@my_database -refresh_folder Sales1
Hint: To avoid entering oracle_home_path/bin each time you run the eulapi script, run discwb.sh first.
Oracle Discoverer's support for Transparent Application Failover (TAF) on a Real Application Cluster (RAC) computer requires a failover mode of "select", as shown in the example tnsnames.ora entry below:
NAME =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=tcp)
(HOST=servername)
(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME= NAME)
(FAILOVER_MODE=
(TYPE=select)
(METHOD=basic)
(RETRIES=4)
(DELAY=15))))
Oracle Corporation recommends that you should initially set RETRIES and DELAY to the values given in the example above (that is, 4 and 15 respectively). However, you may need to increase these values for your own computer.
In certain rare scenarios, end users may see an error message if failover occurs. When they acknowledge the error message, their sessions will continue as normal. A fix for these scenarios is planned for release as a patch to the 9.0.4 release.
The Oracle Warehouse Builder (OWB) Discoverer bridge is documented in "Warehouse Builder Bridges: Transfer Parameters and Considerations" in Chapter 22 "Integrating Warehouse Builder Metadata with Other BI Products" of the Oracle Warehouse Builder User's Guide (10g Release 1 (10.1), Part No. B12146-01).
Before you can use the Lineage workbook you must have first generated your Discoverer EUL using OWB and Discoverer Bridge. Discoverer Bridge will generate a Discoverer import file (.eex) that contains references to the folder and item lineage held within OWB. You can then load this .eex file into your Discoverer EUL. Please note that the Lineage workbook will only work with a business area generated in this way.
Login to Oracle Discoverer Administrator as the Discoverer EUL schema owner.
Import the lineage.eex file.
Login to SQL*Plus as the Discoverer EUL schema owner.
For example:
> SQLPLUS myeul/myeul@myconnection
Run the lineage.sql file.
For example:
SQL> start d:\<ORACLE_HOME>\discoverer\lineage.sql
This script will ask you to input and verify the following:
the OWB URL Machine Name.Domain Name:Port
For example, http://myserver.us.mycompany.com:7777
the OWB Virtual path to mod_plsql (the default is pls)
For example, pls
the OWB Database Access Descriptor DAD (the default is portal30)
For example, portal30
the OWB Browser schema
For example, myowbinstall
the OWB Host name (that is, the name of the machine that runs the OWB repository)
For example, myserver
Note that the name must be exactly the same as used in the OWB browser registration.
the OWB SID (that is, the database instance name)
For example, ASdB
the OWB Schema where the OWB repository resides
For example, owbrep
Login to OracleAS Discoverer Plus, OracleAS Discoverer Viewer, or Oracle Discoverer Desktop as the EUL schema owner and open the Lineage workbook.
If you export and re-import the EUL, you will need to re-run lineage.sql.
If you have previously imported EUL5.eex, you will already have the Lineage workbook saved in the database. If this is the case, all you need to do is run lineage.sql.
Discoverer Administrator includes a workbook dump utility (d51wkdmp.exe) that enables you to inspect the EUL elements used by a workbook without having to open the workbook in Discoverer Desktop. This utility is particularly useful if you are unable to open a workbook (for example, because of missing joins).
To analyze a workbook, run the d51wkdmp.exe file from a command prompt window. The d51wkdmp.exe file is located in the %ORACLE_HOME%\bin directory.
The syntax for using the workbook dump utility is:
d51wkdmp <Workbook_Name> <Output_File> <DB|FS> <Connect_String> <Eul_Schema> -f
The parameters are described in more detail in the table below:
| Parameter | Mandatory or Optional | Description |
|---|---|---|
| <Workbook_Name> | Mandatory | Name of the workbook to be examined (including the path, if the workbook is stored in the filesystem). |
| <Output_File> | Mandatory | The file to write the output to. |
| <DB|FS> | Mandatory | DB = opens the workbook from the database
FS = opens the workbook from the filesystem |
| <Connect_String> | Mandatory | A username, password and database SID combination e.g. user/passwd@database |
| <Eul_Schema> | Mandatory | The name of the database user that owns the EUL tables. |
| -f | Optional | Use this parameter to output further details about EUL objects that the workbook uses, including whether the elements can be found in the specified EUL. |
Note the following:
If a connect string or workbook name includes spaces, enclose the connect string or workbook name in double quotation marks.
Specify the parameters in the order shown above.
You can only analyze a workbook stored in the database if the workbook is owned by the username you specify in the connect string.
The case of the eul_schema parameter must be correct. In other words, if the EUL is owned by user SDC, you must enter "SDC". If you enter "sdc", the following error message is displayed:
"Connect Error - EUL schema is not accessible. Default or specified schema containing EUL tables is inaccessible"
The examples below use the Video Tutorial Workbook that is shipped as part of the Discoverer tutorial to illustrate the output of the workbook dump utility.
Example 1: The following command outputs the content of the Video Tutorial Workbook to the video.txt file. In this example, the -f option is not used.
d51wkdmp "Video Tutorial Workbook" video.txt DB disco/disco@orcl.world disco
The content of the video.txt file is as follows:
///////////////////////////////////////////////////////////////////////////////
Sheet Number 1
///////////////////////////////////////////////////////////////////////////////
Sheet Name = Tabular Layout
Sheet Unique Name = {8690F66A-B9C8-11D1-ADB2-0080C7CDEA89}
Query(s) used =
Query 1
Items :-
EUL Item - Video Analysis Information.Calendar Year
EUL Item - Video Analysis Information.Department
EUL Item - Video Analysis Information.Region
Calculation - Profit SUM
Sort On EUL Item - Video Analysis Information.Region
Filters :-
EUL Filter - Video Analysis Information.Department is Video Rental or Video Sale
///////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////
Sheet Number 2
///////////////////////////////////////////////////////////////////////////////
Sheet Name = Crosstab Layout
Sheet Unique Name = {8690F66B-B9C8-11D1-ADB2-0080C7CDEA89}
Query(s) used =
Query 2
Items :-
EUL Item - Video Analysis Information.Calendar Year
EUL Item - Video Analysis Information.Department
EUL Item - Video Analysis Information.Region
Calculation - Profit SUM
Filters :-
EUL Filter - Video Analysis Information.Department is Video Rental or Video Sale
///////////////////////////////////////////////////////////////////////////////
Example 2: The following command outputs the content of the Video Tutorial Workbook to the video.txt file. In this example, the -f option is used.
d51wkdmp "Video Tutorial Workbook" video.txt DB disco/disco@orcl.world disco -f
The content of the video.txt file is as follows:
EUL Item Reference
IoId = 16
Id = 100177
Identifier = REGION
Name = Region
Folder Identifier = DC_VIDEO_ANALYSIS_INFORMATION
Folder Name = Video Analysis Information
*** Found in EUL by Identifier ***
EUL Item Reference
IoId = 24
Id = 100153
Identifier = DEPARTMENT
Name = Department
Folder Identifier = DC_VIDEO_ANALYSIS_INFORMATION
Folder Name = Video Analysis Information
*** Found in EUL by Identifier ***
EUL Item Reference
IoId = 32
Id = 100175
Identifier = PROFIT
Name = Profit
Folder Identifier = DC_VIDEO_ANALYSIS_INFORMATION
Folder Name = Video Analysis Information
*** Found in EUL by Identifier ***
EUL Private Item
Id = -105
Name = Profit SUM
Identifier = 1
Desc = Total profit for one store, one product in one day
DataType = 2
Placement = 1
Hidden = 0
IsACalc = 0
IOFormula = [1,1]([6,32]) DisplayFormula = SUM(Profit)
EUL Item Reference
IoId = 41
Id = 100139
Identifier = CALENDAR_YEAR
Name = Calendar Year
Folder Identifier = DC_VIDEO_ANALYSIS_INFORMATION
Folder Name = Video Analysis Information
*** Found in EUL by Identifier ***
EUL Filter Reference
Id = 100218
Identifier = DEPARTMENT_IS_VIDEO_RENTAL_OR_VIDEO_SALE
Name = Department is Video Rental or Video Sale
Folder Identifier = DC_VIDEO_ANALYSIS_INFORMATION
Folder Name = Video Analysis Information
*** Found in EUL by Identifier ***
EUL Sort Item Reference
Item = EUL Item - Video Analysis Information.Region
Identifier = 14
Direction = 1
Query Request QR1
Distinct = 1
Axis Item Usage - Name = EUL Item - Video Analysis Information.Calendar Year
Axis Item Usage - Name = EUL Item - Video Analysis Information.Department
Axis Item Usage - Name = EUL Item - Video Analysis Information.Region
Measure Item Usage - Name = Calculation - Profit SUM
Sort Item Usage - Name = Sort On EUL Item - Video Analysis Information.Region
Filter Usage - Name = EUL Filter - Video Analysis Information.Department is Video Rental or
Video Sale
Identifier = 58
///////////////////////////////////////////////////////////////////////////////
Sheet Number 1
///////////////////////////////////////////////////////////////////////////////
Sheet Name = Tabular Layout
Sheet Unique Name = {8690F66A-B9C8-11D1-ADB2-0080C7CDEA89}
Sheet Identifier = 7
Query(s) used =
Query 1
Items :-
EUL Item - Video Analysis Information.Calendar Year
EUL Item - Video Analysis Information.Department
EUL Item - Video Analysis Information.Region
Calculation - Profit SUM
Sort On EUL Item - Video Analysis Information.Region
Filters :-
EUL Filter - Video Analysis Information.Department is Video Rental or Video Sale
///////////////////////////////////////////////////////////////////////////////
Query Request QR2
Distinct = 1
Axis Item Usage - Name = EUL Item - Video Analysis Information.Calendar Year
Axis Item Usage - Name = EUL Item - Video Analysis Information.Department
Axis Item Usage - Name = EUL Item - Video Analysis Information.Region
Measure Item Usage - Name = Calculation - Profit SUM
Filter Usage - Name = EUL Filter - Video Analysis Information.Department is Video Rental or
Video Sale
Identifier = 95
///////////////////////////////////////////////////////////////////////////////
Sheet Number 2
///////////////////////////////////////////////////////////////////////////////
Sheet Name = Crosstab Layout
Sheet Unique Name = {8690F66B-B9C8-11D1-ADB2-0080C7CDEA89}
Sheet Identifier = 12
Query(s) used =
Query 2
Items :-
EUL Item - Video Analysis Information.Calendar Year
EUL Item - Video Analysis Information.Department
EUL Item - Video Analysis Information.Region
Calculation - Profit SUM
Filters :-
EUL Filter - Video Analysis Information.Department is Video Rental or Video Sale
///////////////////////////////////////////////////////////////////////////////
This section describes known errors in the documentation.
The "Notes" section in the Item Class Wizard: Choose Attributes dialog description on page 24-78 of the Oracle Discoverer Administrator Administration Guide should contain an extra bullet point as shown below:
A one-to-one relationship must exist between the item used for the list of values and the item used for the sort order
The example provided for the /export (EUL Objects) command on page 21-20 of the Oracle Discoverer Administrator Administration Guide should read as follows:
To export two business areas named "Test BA" and "Final BA", residing in an EUL named "eul_sales", into a file named export.eex, and write to a log file named export.log, enter:
dis51adm.exe /connect me/mypassword /export export.eex /business_area "Test BA" /business_area "Final BA" /all /eul eul_sales /log export.log
The examples in sections 1.7 and 1.9 of the Oracle Discoverer EUL Command Line for Java User's Guide are missing the "-jar" part of the command line syntax. When running the command line for Java, use the following syntax:
java –jar eulbuilder.jar -command list
The section "About scheduled workbooks and compatibility between the current version of Discoverer and previous releases" on page 7-15 of the Oracle Discoverer Administrator Administration Guide contains the following statement:
In this case you must install the new version of the batch PL/SQL package (EUL5_BATCH_USER) using the SQL script batchusr.sql.
The above statement should read:
In this case you must install the new version of the batch PL/SQL package (EUL5_BATCH_REPOSITORY) using the SQL script batchusr.sql.
All references in the Oracle Discoverer Administrator Administration Guide and the Oracle Discoverer Administrator Tutorial to files located in the <ORACLE_HOME>\discoverer\util directory should instead specify the <ORACLE_HOME>\discoverer\sql directory.For example, the path <ORACLE_HOME>\discoverer\util\eul5_id.sql should read <ORACLE_HOME>\discoverer\sql\eul5_id.sql.
