Support Article
Error while running Report Definition - PR_READ_FROM_STREAM_ERRO
SA-17006
Summary
User is seeing the below exception, while executing the report definition.
Error Messages
Error: There was a problem getting a list: code: 904 SQLState: 42000 Message: ORA-00904: "CARSAPP7"."PR_READ_FROM_STREAM": invalid identifier
Steps to Reproduce
Create a Report Definition and execute.
Root Cause
A defect in Pegasystems’ code or rules, from the error trace it seems that UDF has got corrupted.
Resolution
Steps to install UDF manually on Pega 7.1 Split schema:
Step 1: Take a backup of your existing DB
Use EXPDP command (in oracle) to take a full database backup.
Pre-requisite check if re-creating UDF
Drop all UDFs
Generate a drop script using (run as SYSTEM user):
select 'drop JAVA class PRPCDATA."' || object_name || '";' from dba_objects where owner = 'PRPCDATA' and object_type = 'JAVA CLASS';
Drop Functions (optional)
If GUI installer or GenerateDDL script has been run previously from this location, one must drop the functions, else the install script will end with FAILED message.
This however creates 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/
a) Drop all the UDFs related functions. To get the Drop script, run these 2 queries (as SYSTEM user on DB):
b) Select 'drop function PRPCDATA."' || object_name || '";' from dba_objects where owner = 'PRPCDATA' and object_type = 'FUNCTION';
c) 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 example, on 11g R2, '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 one must 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, one 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= ???????
pega.jdbc.password= ???????
rules.schema.name= ???????
data.schema.name= ???????
Step 5: Modifying the setupDatabase.xml file
Once the permissions are setup correctly, to run the setupUDF piece the easiest way 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 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:user is not interested in the result of this; it is simply to call the objects.
Note: If this SQL is run in same session where the objects were previously dropped, user must 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.
Published January 31, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.