Modernisez l'existant. Automatisez l'évolution.

Oracle Forms et Java

Reports With Attached PLX Causes Reports Server Hangs/Crashes Intermittently or REP-770 / REP-736 ..

Patrick Hamou 3 min de lecture

Applies to

Oracle Reports Developer - Version 10.1.2.0.2 to 11.1 [Release Oracle10g to 11g]
Information in this document applies to any platform.
Checked for relevance on "03-Mar-2013"

Symptoms

Intermittently, reports are hanging, they are queued up. If the end user re-executes them, sometimes they work again. It does not follow a specific pattern to be reproduced. Showjobs URL just displays in "current jobs" the status "Opening report". Other reports fail intermittently with error:

REP-770: One or more PL/SQL libraries have been modified since the report was saved.
The PL/SQL will be recompiled.

REP-0736: There exist uncompiled program unit(s).

REP-1247: Report contains uncompiled PL/SQL.
"Terminated with error: <br>REP-770: 'beforepform': Fatal PL/SQL error occurred. ORA-06508: PL/SQL: could not find program unit being called"
REP-770: There exist uncompiled program unit(s).

Resubmitting the same report with the same parameters will sometimes work. If not, it is necessary to stop and restart the reports server. 

It is just reproducible in the Production environment, where more reports are executed per minute.

Cause

Currently, Reports 10g /11g does not support the use of .PLX files as indicated in:

Reports Builder Help on Line. For example, extract from Reports 11g:

"PL/SQL conversion utility: Converting PL/SQL libraries attached to a report":
Note: This feature is included in the Report Compiler to create .PLX files for use with Forms. Currently, Reports does not support the use of .PLX files..."

All the reports that were failing intermittently were using Attached Libraries. The PLL's and PLX's were BOTH available in REPORTS_PATH environment variable.

If just the PLX is available, the reports should be always failing.

Solution

PLX are NOT supported in Oracle Reports. Use always the PLL files.

1. Look for the paths already setup in REPORTS_PATH.

a) The value assigned to REPORTS_PATH could be verified executing showenv URL:

http://host:port/reports/rwservlet/showenv?server=reports_server_name

b) Look for REPORTS_PATH  in the configuration files and setup. The environment variables are usually saved in the following files and places:

Unix 10gR2:

Reports 10gR2:

$ORACLE_HOME/bin/reports.sh 
or 
$ORACLE_HOME/opmn/conf/opmn.xml
Reports 11g:
$ORACLE_INSTANCE/config/reports/bin/reports.sh
or
$ORACLE_INSTANCE/config/OPMN/opmn/opmn.xml

Windows:

Specific for Reports 10gR2:
%ORACLE_HOME%\opmn\conf\opmn.xml

Specific for Reports 11g:
%ORACLE_INSTANCE%\bin\reports.bat
%ORACLE_INSTANCE%\config\OPMN\opmn\opmn.xml

For both releases:
Control Panel --> System --> Advanced --> Environment Variables
In regedit -> HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE -> <ORACLE_HOME>

2. Review if the reports that are failing intermittently are using Attached Libraries. If so, delete all the PLX files from the paths already setup in REPORTS_PATH environment variable.

3. Other testing is copying/pasting the code from the PLL to the RDF report. If it is not longer hanging in "opening report" status or displaying REP-770 intermittently, it confirms the issue is related to the attached libraries.

4. If REPORTS_PATH environment variable is modified, remember to re-start reports server in order the changes are applied. If in-process reports server is in use, it is mandatory to re-start OC4J_BI_Forms in 10gR2 and WLS_REPORTS in 11g, too.

Tous les articles