Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

Reports Definition is not working in production

SA-30736

Summary



User reported that Report definition not working in production and failing with an error.


Error Messages



 An error occurred on executing the query for the report definition - There was a problem getting a list: code: 29532 SQLState: 99999 Message: ORA-29532: Java call terminated by uncaught Java exception: com.pega.pegarules.data.internal.clipboard.directstream.BadStreamDataException: Encountered invalid section ID "M" in storage stream

Steps to Reproduce



 Run the report definition.

Root Cause



An issue in the custom application code or rules.
This happens when the User Defined Functions (UDFs)s in the database are not up to date. 

Resolution



The issue is coming because of UDF is not installed or gets corrupted.

Perform the following local-change steps:



Step 1: Take a backup of the existing DB:
  1. Use EXPDP command (in oracle) to take a full database backup.
  2. Pre-requisite check if re-creating UDF.
  3. Drop all UDFs.
  4. Generate a drop script using (run as SYSTEM user):
  5. Select 'drop JAVA class PRPCDATA."' || object_name || '";'  from dba_objects where owner = 'PRPCDATA' and object_type = 'JAVA CLASS';
  6. Drop Functions (optional).

If GUI installer or GenerateDDL script has been run previously from this location, you need to drop the functions, else the install script will end with FAILED message. This however will still create the Java classes, it only fails on creating functions, because the already exist.
This is only needed if following directory is present and has relevant SQL statements: <PEGA_INSTALLSET>/schema/generated/oracledate/install/
Drop all the UDFs related functions. To get the Drop script, run these 2 queries (as SYSTEM user on DB):
select 'drop function PRPCDATA."' || object_name || '";'  from dba_objects where owner = 'PRPCDATA' and object_type = 'FUNCTION';
select 'drop function PRPCRULES."' || object_name || '";'  from dba_objects where owner = 'PRPCRULES' and object_type = 'FUNCTION';
 
The above queries will result in Drop statements like so:
drop JAVA class PRPCDATA."/fcf52cd4_DirectStreamEnvironm";
drop JAVA class PRPCDATA."/fad24711_DirectStreamReaderDB";
drop JAVA class PRPCDATA."/e9802c50_DirectStream";
drop JAVA class PRPCDATA."/af2ac841_InflaterV7";
drop JAVA class PRPCDATA."/ac7654fa_DirectStreamReaderPo";
drop JAVA class PRPCDATA."/9dbd2fb4_BadReferenceExceptio";
drop JAVA class PRPCDATA."/9b1d56c2_BasicEnvironmentAdap";
drop JAVA class PRPCDATA."/8f93fd2d_DirectStreamReaderBa";
drop JAVA class PRPCDATA."/8259c7b6_FixedCacheMap";
drop JAVA class PRPCDATA."/7c1e1a3d_PropertyReferenceUti";
drop JAVA class PRPCDATA."/75ff6599_CachedDirectStream";
drop JAVA class PRPCDATA."/644d6c0f_UnsupportedFeatureEx";
drop JAVA class PRPCDATA."/43e33eef_BadStreamDataExcepti";
drop JAVA class PRPCDATA."/32d1f03d_DirectStreamReader";
drop JAVA class PRPCDATA."/2b12b644_DirectStreamReaderDB";
drop JAVA class PRPCDATA."/20e5462f_ByteArrayUtil";
 
Optional statements:
 
drop function PRPCDATA."PR_READ_INT_FROM_STREAM";
drop function PRPCDATA."PR_READ_FROM_STREAM";
drop function PRPCDATA."PR_READ_DECIMAL_FROM_STREAM";
 
drop function PRPCRULES."PR_READ_INT_FROM_STREAM";
drop function PRPCRULES."PR_READ_FROM_STREAM";
drop function PRPCRULES."PR_READ_DECIMAL_FROM_STREAM";

 
Step 2: Cross check that java is enabled on your database.
To verify it, run this following sql as SYSDBA.
 

select comp_name, version, status from dba_registry where upper(comp_name) like '%JAVA%';
A record for JVM should be returned. For ex, on 11gR2, 'JServer JAVA Virtual Machine'


 
Step 3: Create Procedure DB privilege:

The database user used during the install must have the "create procedure" permission explicitly granted.
To verify we have proper DB privilege, run the following sql as SYSDBA.

select * from dba_sys_privs where grantee='<PRPCADMIN>' and privilege='CREATE ANY PROCEDURE';               
--Replace <PRPCADMIN> with the real PRPC ADMIN schema owner

A record should be returned, with the schema owner and the 'CREATE ANY PROCEDURE' privilege listed.
If not, we can grant Create ANY Procedure DB privilege to PRPC DB schema owner.

 
Step 4: Map your DB properties.

In the scripts folder in your PRPC media configure the setupDatabase.properties file to map it to the PRPC schema where you want to install UDF.
Example:
pega.jdbc.driver.jar=D:\Database_Drivers\ojdbc6.jar
pega.jdbc.driver.class=oracle.jdbc.OracleDriver
pega.database.type=oracledate
pega.jdbc.url=jdbc:oracle:thin:@localhost:1521/prpc
pega.jdbc.username=PRPCADMIN
pega.jdbc.password=PRPCADMIN
rules.schema.name=PRPCRULES
data.schema.name=PRPCDATA

 
 
 
Step 5: Modifying the setupDatabase.xml file: 

Once the permissions are setup correctly, to run the setupUDF piece the easiest thing we can do is to just add a new target to setupDatabase.xml which just does the initialization, configuration and udf generation.
In setupDatabase.xml (Add below snippet):
<target name="installUDF" depends="Initialization, Configuration, Apply UDFs">
<echo message="PegaRULES Process Commander UDF load complete."/>
</target>



Step 6: Edit install.sh to install only UDF:

Then you can edit line 140 of install.sh to call this new target or just set ant_home and java_home and make the call from the command line.
from
"`dirname "$0"`/bin/ant" $ANT_PROPS -f ./setupDatabase.xml install -noclasspath 2>&1 | tee $logfile
to
"`dirname "$0"`/bin/ant" $ANT_PROPS -f ./setupDatabase.xml installUDF -noclasspath 2>&1 | tee $logfile
 

 
 
Step 7: Execute the install.sh:

This should be executed from the INSTALLSET/scripts folder.
The build should complete successfully.
Check all dropped objects were created. If not, you’d may need to run GenerateDDL script if the functions are missing (see pre-req check above)

Step 8: Execute the below SQL for testing:

SELECT PRPCDATA.pr_read_from_stream('pzInsKey', pzInsKey, pzPVStream) as "StringValue", PRPCDATA.pr_read_int_from_stream('pzRuleSetVersionMajor', pzInsKey, pzPVStream) as "IntegerValue", PRPCDATA.pr_read_decimal_from_stream('pzRuleSetVersionMinor', pzInsKey, pzPVStream) as "DecimalValue" from prpcrules.pr4_rule where pxInsID like 'CANNOT%'; 
 

Sample output (7.1.5):
 
RULE-MESSAGE CANNOTBEBLANK #20131021T154455.159 GMT                                                7              10
RULE-MESSAGE CANNOTBEBLANK #20130919T015230.511 GMT                                                7              10
RULE-MESSAGE CANNOTBEBLANK #20131021T154757.011 GMT                                                7              10
RULE-MESSAGE CANNOTBENONALPHANUM #20130919T015230.561 GMT           7              10
RULE-MESSAGE CANNOTIDENTIFYTHREAD #20130918T235100.459 GMT                                7              10
RULE-MESSAGE CANNOTSORT #20130918T235100.524 GMT                                        7              10
 
This will make a call to a function, which makes use of the Java Objects: We are not interested in the result of this; it is simply to call the objects. Please note, if this SQL is run in same session where the objects were previously dropped, you’ll need to run it twice.



Step 9: Execute below to check invalid java objects:

SELECT dbms_java.longname(object_name) FROM all_objects WHERE status!='VALID' AND  object_type LIKE 'JAVA%';
At this moment most of the JAVA objects should be valid. Run as SYSTEM.
If some objects are invalid, that is fine as long as query in Step 8 succeeds.

 


This resolves the issue.
 
 

Published January 4, 2017 - Updated October 8, 2020

Was this useful?

0% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us