Summary

Error Messages

Steps to Reproduce

Root Cause

Resolution

1. Backup the existing database.
   
   Use the EXPDP command (in Oracle) to backup the database.
   
   Pre-requisite check if re-creating UDF:
   
   - Drop all the 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 the GUI installer or GenerateDDL script is run previously from this location, drop the functions, else the Install script will end with a Failed message. This however still created the Java classes and only fails on creating functions because they already exist.
   
   This is required only if the following directory is present and has relevant SQL statements:
   
   <PEGA_INSTALLSET>/schema/generated/oracledate/install/
   
   Drop all the UDFs related functions. To obtain the Drop script, run these two queries (as SYSTEM user on the database):
   
   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 result in Drop statements as:
   
   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";
    
2. Ensure that Java is enabled on the database.
   
   To verify this, execute the following SQL as SYSDBA.
   
   select comp_name, version, status from dba_registry where upper(comp_name) like '%JAVA%';
   
   A record for JVM must be returned.
   
   For example, on 11gR2, 'JServer JAVA Virtual Machine'
    
3. Create a Procedure database privilege.
   
   The database user used during the install must have the 'Create procedure' permission granted explicitly.
   
   To ensure that the user has the correct database privilege, execute the following SQL as SYSDBA.
   
   select * from dba_sys_privs where grantee='<PRPC USER>' and privilege='CREATE%PROCEDURE';     

   --Replace <PRPC USER> with the real Pega Schema owner.
   
   A record must be returned with the schema owner and the 'Create Procedure' privilege listed.

   Else, grant the Create Procedure database privilege to the Pega database schema owner.       

4. Map the database properties.
   
   In the scripts folder in the Pega media, configure the setupDatabase.properties file to map it to the Pega schema where the user wants to install the UDF.
   
   For 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    //replace with correct URL
   pega.jdbc.username=<username>
   pega.jdbc.password=<password>
   rules.schema.name= <rules_schema>
   data.schema.name= <data_schema>

5. Modify the setupDatabase.xml file.
   
   When permissions are setup correctly, to execute the setupUDF, add a new target to the setupDatabase.xml which performs the initialization, configuration, and UDF generation.

   In the setupDatabase.xml, add the below snippet:

   <target name="installUDF" depends="Initialization, Configuration, UDF Generation">
   <echo message="PegaRULES Process Commander UDF load complete."/>
   </target>

6. Edit the install.bat to install only the UDF.

   Use the below line of install.bat to invoke this new target.
   
   set  ANT_TARGET=install
   
   Alternatively, set ant_home and java_home to make the call from the Command Line.
   
   

   Replace install in the above line with installUDF.

   If the system is UNIX, replace install in the below line with installUDF in install.sh.
   
   "'dirname "$0"'/bin/ant" $ANT_PROPS -f ./setupDatabase.xml install -noclasspath 2>&1 | tee $logfile

   
    
7. Execute the install.bat (execute install.sh incase of a UNIX environment). The build should complete successfully.
    
8. Execute the below SQL:
   
   select pr_read_from_stream('pylabel','DATA-ADMIN-WORKBASKET [email protected]',pzpvstream) from pr_data_admin where pzinskey='DATA-ADMIN-WORKBASKET [email protected]';

   This makes a call to a function which uses Java Objects.

9. Execute the SQL to check for invalid Java objects.

   SELECT dbms_java.longname(object_name) FROM user_objects WHERE status!='VALID' AND  object_type LIKE 'JAVA%';

   All the Oracle related objects should be valid.