SA-4980

Summary

User created data tables and tried to utilize Report-Definition for retrieving records and found that the wrong data is populated.

Error Messages

Not Applicable

Steps to Reproduce

1, Create a DataTable.
2, Enter some records.
3, Run Default report Definition created by Data table wizard.
4, Check the content of unexposed properties.

Resolution

This issue typically occurs because the BLOB-reader user defined functions in the customer database are corrupt. This usually happens when an Oracle PRPC database has been created by copying it from another one using native Oracle tools--instead of installing it using PRPC tools.

The problem is solved by manually re-installing the UDFs.


Steps to install UDF manually on a PRPC schema

Step 1: Take a backup of your existing DB
Use EXPDP command (in oracle) to take a full database backup.

Step 2: Cross check that java is enabled on your database.
To verify it, we can 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='<PRPC USER>' and privilege='CREATE PROCEDURE';               
--Replace <PRPC USER> with the real PRPC 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=PRPC
pega.jdbc.password=PRPC
 
 
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, UDF Generation">
<echo message="PegaRULES Process Commander UDF load complete."/>
</target>


Step 6: Edit install.bat to install only UDF
Then you can edit line 53 of install.bat to call this new target or just set ant_home and java_home and make the call from the command line.

call "%ANT_HOME%\bin\ant.bat" %ANT_PROPS% -f setupDatabase.xml installUDF
 
Step 7: Execute the install.bat
The build should complete successfully.



Step 8: Execute the below SQL:
select pr_read_from_stream('pylabel','DATA-ADMIN-WORKBASKET your_wb@your_host.COM',pzpvstream) from pr_data_admin where pzinskey='DATA-ADMIN-WORKBASKET your_wb@your_host';
This will make a call to a function, which makes use of the Java Objects: We are not interested in the return of this; it is simply to call the objects.
 
Step 9: Execute below to check invalid java objects
SELECT dbms_java.longname(object_name) FROM user_objects WHERE status!='VALID' AND  object_type LIKE 'JAVA%';
At this moment all the oracle related objects should be valid.