SA-744

Summary

When using a Report Definition to query the BLOB (pzPVStream column), the results appear corrupt with partial data and/or characters missing from the values.

Error Messages

There are no error messages shown.

Steps to Reproduce

1. Create a Report Definition which selects one or more properties that are not columns in your database table.
2. Run the report and review the results.

Root Cause

The issue is with the Database Function (pr_read_from_stream) used to call the DirectStreamReader Java Class.

Resolution

In order to resolve this issue, drop all the pr_read_from_stream DB Functions, re-load the UDF entries, create the DB Functions and then compile the Java Classes. This is done in a series of command-line and sqlplus steps to restore the functions/classes to a usable state.

1. Create a file called user-deployudf.properties as a text file.

2. Add the following lines to the properties file, replacing the parameters (ServerName_or_IP, port, OracleSID, PegaUser and DATAWORK) with your values:
              oracledate.jdbc.class=oracle.jdbc.OracleDriver
              dev.oracledate.url=jdbc:oracle:thin:@ServerName_or_IP:port:oracleSID
              dev.oracledate.username=PegaUser
              dev.oracledate.password=PegaUser
              dev.oracledate.schema=DATAWORK

3. Save the file in a directory, along with the prreadstream.jar file being loaded.

4. Drop the functions that are causing the udf directstreamreader errors.  Connect to your database via sqlplus (Oracle) and execute the drop commands.      Replace the schema qualified name "DATAWORK" with the same name/value specified in step 2 for "dev.oracledate.schema=".
               SQL> drop function DATAWORK.pr_read_from_stream;
               SQL> drop function DATAWORK.pr_read_decimal_from_stream;
               SQL> drop function DATAWORK.pr_read_int_from_stream;

5. Load the classes.  This is done by using a Java Call from a command prompt and specifying seven parameters for the Java call.
                  java          Program execution file used to process jar files.
                  -cp           Class search path of directories and zip/jar files.
                  D:\PRPC716_GA\scripts\prdeploy.jar          Followed by a quoted string specifying the prdeploy.jar file which is used to load the files .
                  D:\lib\ojdbc6.jar         You must also specify the location of where the jdbc driver is for the database. Separated by a semi-colon (;).
                  com.pega.pegarules.install.DatabaseLibraryLoader           The name of the class in the prdeploy.jar file being called.
                  --config="D:\PRPC716_GA\archives\udf\user-deployudf.properties"            The location and name of the properties file created in step 1 & 2
                  --profile=dev.oracledate             Name of the profile in the config file being called.  Used to identify the database type
                  "D:\PRPC716_GA\archives\udf\prreadstream.jar"            Name of the jar file being processed and imported into the system.

To put all the parameters together, open a command prompt and execute the below.  Note that D:\PRPC716_GA is the folder containing the distribution image and \scripts is the folder containing the the prdeploy.jar file.
D:\PRPC716_GA\scripts> java -cp "D:\PRPC716_GA\scripts\prdeploy.jar;D:\lib\ojdbc6.jar" com.pega.pegarules.install.DatabaseLibraryLoader --config="D:\PRPC716_GA\archives\udf\user-deployudf.properties" --profile=dev.oracledate "D:\PRPC716_GA\archives\udf\prreadstream.jar"

6. Rebuild / Create the three functions removed in step 4.  Replace the schema qualified name "DATAWORK" with the same name/value specified in step 2 for "dev.oracledate.schema=".
  SQL> CREATE FUNCTION DATAWORK.pr_read_from_stream
(property in varchar2, insKey in varchar2,
   storage_stream in blob)
  return varchar2 DETERMINISTIC
as
--
-- $Id:
--
-- Copyright (c) 2011  Pegasystems Inc.
-- All rights reserved.
--
-- This  software  has  been  provided pursuant  to  a  License
-- Agreement  containing  restrictions on  its  use.   The  software
-- contains  valuable  trade secrets and proprietary information  of
-- Pegasystems Inc and is protected by  federal   copyright law.  It
-- may  not be copied,  modified,  translated or distributed in  any
-- form or medium,  disclosed to third parties or used in any manner
-- not provided for in  said  License Agreement except with  written
-- authorization from Pegasystems Inc.
--

--
-- Name: pr_read_from_stream
--
-- Description:
--
language java
name 'com.pega.pegarules.data.udf.directstreamreader.DirectStreamReader.get(java.lang.String, java.lang.String, java.sql.Blob) return java.lang.String';
/

SQL> CREATE FUNCTION DATAWORK.pr_read_decimal_from_stream
(property in varchar2, insKey in varchar2,
   storage_stream in blob)
  return number DETERMINISTIC
as
--
-- $Id:
--
-- Copyright (c) 2011  Pegasystems Inc.
-- All rights reserved.
--
-- This  software  has  been  provided pursuant  to  a  License
-- Agreement  containing  restrictions on  its  use.   The  software
-- contains  valuable  trade secrets and proprietary information  of
-- Pegasystems Inc and is protected by  federal   copyright law.  It
-- may  not be copied,  modified,  translated or distributed in  any
-- form or medium,  disclosed to third parties or used in any manner
-- not provided for in  said  License Agreement except with  written
-- authorization from Pegasystems Inc.
--

--
-- Name: pr_read_decimal_from_stream
--
-- Description:
--
language java
name 'com.pega.pegarules.data.udf.directstreamreader.DirectStreamReader.getDecimal(java.lang.String, java.lang.String, java.sql.Blob) return java.math.BigDecimal';
/

SQL> CREATE FUNCTION DATAWORK.pr_read_int_from_stream
(property in varchar2, insKey in varchar2,
   storage_stream in blob)
  return number DETERMINISTIC
as
--
-- $Id:
--
-- Copyright (c) 2011  Pegasystems Inc.
-- All rights reserved.
--
-- This  software  has  been  provided pursuant  to  a  License
-- Agreement  containing  restrictions on  its  use.   The  software
-- contains  valuable  trade secrets and proprietary information  of
-- Pegasystems Inc and is protected by  federal   copyright law.  It
-- may  not be copied,  modified,  translated or distributed in  any
-- form or medium,  disclosed to third parties or used in any manner
-- not provided for in  said  License Agreement except with  written
-- authorization from Pegasystems Inc.
--

--
-- Name: pr_read_int_from_stream
--
-- Description:
--
language java
name 'com.pega.pegarules.data.udf.directstreamreader.DirectStreamReader.getInt(java.lang.String, java.lang.String, java.sql.Blob) return int';
/

7. Recompile all the Java Classes for the schema in question (DATAWORK).  Replace the schema qualified name "DATAWORK" with the same name/value specified in step 2 for "dev.oracledate.schema=".  Recompiling "INVALID" objects can be done in a couple ways.
    a) A clean sweep approach would be to recompile all Java Classes to make sure everything can compile is VALID. 
            SQL> set pagesize 25000
            SQL> select 'alter java class '''||object_name||''' resolve;' from user_objects where object_type like '%JAVA%';
    b) One class at a time - Note the DB2LUW/DB2ZOS and PostgreSQL may or may not compile in Oracle. This can be ignored.
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2LUW" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/internal/clipboard/directstream/BadReferenceException" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/internal/clipboard/directstream/BadStreamDataException" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/internal/clipboard/directstream/ByteArrayUtil" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/internal/clipboard/directstream/DirectStream" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/internal/clipboard/directstream/DirectStreamEnvironmentAdapter" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/internal/clipboard/directstream/InflaterV7" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/internal/clipboard/directstream/PropertyReferenceUtilities" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/BasicEnvironmentAdapter" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/CachedDirectStream" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/DirectStreamReader" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2LUW" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2ZOS" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderPostgreSQL" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/FixedCacheMap" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/UnsupportedFeatureException" resolve;

8. Restart the PRPC Node(s) after deleting the PegaRULES_Extract_Marker.txt file from the explictTempDir folder on each node.

9. Re-test the report.