Blitz Report™ Installation Guide
- 5.1 Menu and request group entry via script
- 5.2 Menu entry
- 5.3 Request group entry
- 5.4 Monitor concurrent program
- 5.5 Concurrent manager workshift
- 5.6 Profile options
- 5.7 License key
- 6.1 ORA-12154: TNS:could not resolve the connect identifier specified
- 6.2 Error: CUSTOM_SCHEMA could not be derived for custom application XX
- 6.3 Concurrent Manager stops processing requests when the first Blitz Report is started
- 6.4 Blitz Reports ends with ORA-01843: not a valid month when using date parameters
- 6.5 ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
This document guides you through the steps required to install Blitz Report for Oracle E-Business Suite R12 or 11i.
Blitz Report installs the following objects on your Oracle E-Business Suite server:
- Database tables, sequences, synonyms, indexes and views
- Database packages
- User interface form
- Application setups such as concurrent programs, executables, value set, profile options, lookups, business event subscriptions, form function, messages, mime types
All installed objects are prefixed with XXEN.
The basic installation steps are:
- Run the installation shell script on the application node using the applmgr
- Either run a shell script to assign Blitz Report to menus and request groups automatically or perform these application setup steps manually.
- Perform additional manual application setup steps from the ‘System Administrator’ responsibility.
Blitz Report runs on Oracle E-Business Suite versions R12 and 11i and requires database version 11g or above.
In order to keep Blitz Report separate from Oracle EBS standard code, we recommend installing it into a custom application database schema.
You can either install Blitz Report into an already existing custom application or create a new one, in case you would like to keep Blitz Report separate from your existing custom code.
If you are unfamiliar with this process, please follow Oracle’s documentation on how to create a new custom application (e.g. note 1577707.1). It is suggested that you use application short code of ‘XXEN’ and application description ‘Enginatics Custom Application’.
If you decide for an installation into an existing application however, all Blitz Report installation steps can be done safely while the application is in use.
Note for installation into EBS R12.2: The procedure described in this document should be performed on the run file system.
Please note that the target database schema for the Blitz Report installation also requires sufficient privileges to create Oracle text (ctxsys.context) indexes e.g. by having the database role RESOURCE granted.
To make use of Blitz Report’s DBA reports, ensure the APPS user has the SELECT ANY DICTIONARY privilege granted.
grant select any dictionary to apps
Granting the SELECT_CATALOG_ROLE role is not sufficient. To use dictionary tables in PLSQL code, the user must have the direct grant on the base object, not through a role.
select dsp.* from dba_sys_privs dsp where dsp.grantee='APPS' and dsp.privilege='SELECT ANY DICTIONARY'
1. Login to the Oracle application server node as the Oracle application user e.g. applmgr and ensure that the application environment context is sourced.
2. Copy or ftp the file ‘blitz_report_*.zip’ to a location of your choice on the EBS application server node.
3. Unzip it and navigate to the folder ‘blitz_report’, located under the directory from where it is unzipped. Add execution permissions for shell scripts and perform the installation by running install.sh:
unzip blitz_report_*.zip cd blitz_report_* chmod u+x *.sh install.sh
During execution, the script will ask for the following parameters:
|APPS_PWD||Password for the APPS database user||apps|
|CUSTOM_APP||Short name of your custom application (see prerequisites)||XXEN|
|CUSTOM_TABLESPACE||Tablespace name of your custom application tables||APPS_TS_TX_DATA|
|CUSTOM_INDEXSPACE||Tablespace name of your custom application indexes||APPS_TS_TX_IDX|
After entering the APPS password and custom application short name, the script derives the remaining parameters automatically and you can confirm them by just pressing Enter or alternatively entering different values as required.
Note: To run the installation non-interactively, you also can set these parameters before script execution, e.g. using the following:
export APPS_PWD=apps export CUSTOM_APP=XXEN export CUSTOM_TABLESPACE=APPS_TS_TX_DATA export CUSTOM_INDEXSPACE=APPS_TS_TX_IDX
4. Review install_*.log and contact our support team if you find any errors.
To reverse an installation and to remove Blitz Report completely from your system, e.g. in case you would not like to use it anymore, you can execute the included script:
select * from dba_objects do where do.object_name like 'XXEN%'
which should return zero records after successful uninstallation.
As Blitz Report makes use of global temporary tables, a common issue during uninstallation is Error ‘ORA-14452: attempt to create, alter or drop an index on temporary table already in use’, which occurs in case there are users logged on to the Blitz Report form while running the uninstallation script.
You can identify these blocking user sessions by the SQL shown in the upgrade section below.
1. Upgrading Blitz Report to the latest release can be done while the E-Business Suite application is in use. To avoid object locks however, you should ensure that users do not run or use Blitz Report during the upgrade.
Use following query to verify which users are currently using Blitz Report and notify them to close their forms or disconnect their sessions via the command in the kill_session_text column.
select distinct xxen_util.user_name(gs.module, gs.action, gs.client_identifier) user_name, xxen_util.module_type(gs.module, gs.action) module_type, xxen_util.module_name(gs.module, gs.program) module_name, gs.module, gl.sid, 'alter system kill session '''||gs.sid||','||gs.serial#||',@'||gl.inst_id||''' immediate;' kill_session_text from dba_objects do, gv$lock gl, gv$session gs where do.object_name like 'XXEN%' and gl.id1=do.object_id and gl.inst_id=gs.inst_id and gl.sid=gs.sid order by user_name
2. Deploy the latest code installation pack using the install.sh script in the same way as a fresh installation (performing installation steps 1-4).
Ignore any ‘ORA-00955: name is already used by an existing object’ error messages.
3. Restart the workflow service components. This step is required for Blitz Report’s email sending feature, which makes use of business events for concurrent program and request completion.
System Administrator→ Concurrent→ Manager→ AdministerRestart following workflow service components:
- Workflow Agent Listener Service
- Workflow Mailer Service
- Workflow Document Web Services Service
5. Application setup
In order to make Blitz Report available to the users, you need to add the Blitz Report function to forms menus and the Blitz Report program to request groups.
This setup can either be performed automatically by a shell script, which would add Blitz Report to all active users’ menus and request groups, or, in case you would like to restrict access to Blitz Report to certain responsibilities only, continue here to perform this setup manually.
5.1 Menu and request group entry via script
To make Blitz Report available to all active users automatically, execute script:
During script execution, you will be prompted to choose between adding the Blitz Report function to the top or at the end of each menu.
Note: The script will only add Blitz Report to menus and request groups of responsibilities that have at least one form function assigned. It will not add Blitz Report to pure HTML/OAF menus or their corresponding request groups.
After script application, continue with additional manual setup steps here.
5.2 Menu entry
If you would like to restrict access to Blitz Report to specific menus, assign the Blitz Report function manually, as shown below.
System Administrator→ Application→ Menu
5.3 Request group entry
To enable users to schedule reports as a background concurrent process (e.g. for outbound interfaces or long running data extractions scheduled during the night), the concurrent program ‘Blitz Report’ needs to be added to the appropriate request groups, for example ‘Receivables All’.
5.4 Monitor concurrent program
The Blitz Report Monitor concurrent program performs the following tasks:
- Cancels Blitz Report concurrent requests that exceed given time limits
- Marks as complete any terminated or error report runs
- Purges outdated records from Blitz Report’s internal logging tables
- Purges the SQL history of deleted reports
Assign this program to a request group and schedule it according to your business needs.
Note: If your business uses the Blitz Report time limit feature, then it is recommended to have a short schedule, e.g. every 5 minutes, as reports exceeding given time limits will continue to run until the monitor program ‘wakes up’ to stop them.
If the time limit feature is not used, a longer schedule can be setup, e.g. daily. This prevents unnecessary monitoring, but still purges unnecessary data (b and c above).
System Administrator→ Security→ Responsibility→ Request
Query request group ‘System Administrator Reports’ and add the concurrent program ‘Blitz Report Monitor’.
System Administrator→ View→ Requests→ Submit a New Request→ Single Request
Schedule concurrent ‘Blitz Report Monitor’ to run, for example, every 5 minutes.
5.5 Concurrent manager workshift
For an optimal user experience, set concurrent managers sleep time to a low value e.g. 4 seconds.
System Administrator→ Concurrent→ Manager→ Define→ Work Shifts
5.6 Profile options
System Administrator→ Profile→ System
The profile option ‘Blitz Report Access Level’ should be set to ‘Developer’ for those users who have the SQL skills to create Blitz Reports.
Additional profile options can be set to configure Blitz Report functionality according to your business and user requirements. Please review the Blitz Report User Guide, chapter ‘Profile options’ for more details.
5.7 License key
Blitz Report Setup Window→ Tools→ License Key
Enter your company name and the license key that you obtained through our website www.enginatics.com.
Note: If you are using the free version of Blitz Report without a license key, you can still use Blitz Report’s full functionality for storing and maintaining SQLs, but Blitz Report will generate an output for the 30 most recently created reports only (custom reports take precedence over Enginatics reports).
6. Known Issues
If the forms compilation step fails with this errormessage, then please follow note 431324.1 to correct the Oracle forms compilation script frmcmp_batch.sh as noted.
If you encounter this errormessage during installation, please validate if you have setup the link between your custom application and the Oracle database username.
Alert Manager→ System→ Installations
6.3 Concurrent Manager stops processing requests when the first Blitz Report is started
This happens if a new application is created for Blitz Report through adsplice, but the concurrent managers are not restarted after running autoconfig.
Creating a new application through adsplice requires autoconfig and a conc manager restart, to enable processing concurrents of the newly created application..
6.4 Blitz Reports ends with ORA-01843: not a valid month when using date parameters
When changing the ICX: Date format mask to something different than DD-MON-RR and using date parameters, Blitz Report ends with ORA-01843: not a valid month.
6.5 ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
If you see this error in the installation log:
ERROR at line 1: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine ORA-20000: Oracle Text error: DRG-11422: linguistic initialization failed DRG-11446: supplied knowledge base file /u01/app/oracle/product/188.8.131.52/dbhome_1/ctx/data/enlx/droldUS.dat not installed ORA-06512: at "CTXSYS.DRUE", line 160 ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366
please follow Oracle note 557239.1 to install the missing Text Knowledge Base files.
If you don’t want to go through Oracle’s whole installation procedure, you can also deploy just the missing files manually following these steps:
1. Navigate to Oracle’s Database 11g download page www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html, click on the ‘See all’ link and download the ‘Oracle Database 11g Release 2 Examples’ file.
2. Unzip the file
3. Goto to the location for the CTX files and unzip filegroup1.jar
cd examples/stage/Components/oracle.ctx.companion/184.108.40.206.0/1/DataFiles unzip filegroup1.jar
4. navigate to ctx/data/enlx and copy all files to $ORACLE_HOME/ctx/data/enlx
cd ctx/data/enlx cp * $ORACLE_HOME/ctx/data/enlx
5. Due to the missing files and the domain index being invalid during Blitz Report installation, the load of Blitz Report templates also ends in error ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE. To correct these and reload the Blitz Report templates, rerun the Blitz Report installation script again.