Applies to Pega Platform versions 7.2.1 through present.

This document explains what user-defined functions (UDFs) are. The sections on troubleshooting UDF issues pertain primarily to Oracle.  If you are using another Pega Platform release or database other than Oracle, find the sections that explain user-defined functions (UDFs) in your deployment guide, either the installation guide or the update guide.

Verifying UDF installation

Validating invalid UDFs

Forcing Oracle to revalidate UDFs

​ResolveUDF SQL script

Resolving UDF errors

Before opening a Support Request: Must Gather information

Verifying UDF installation

To verify if the UDFs are installed and valid, run the following query, replacing RULES with your schema name.

While there are three (3) UDFs, there are 15+ Java classes that provide the implementation of the functions. This query checks for Java objects owned by the rules schema.

The schema name must to be in all uppercase letters.

select dbms_java.longname(Object_Name), status from dba_objects where owner= 'RULES' and object_type like 'JAVA%';

com/pega/pegarules/data/udf/directstreamreader/BasicEnvironmentAdapter VALID

com/pega/pegarules/data/udf/directstreamreader/CachedDirectStream VALID

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReader VALID

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase$Version6 VALID

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase$Version7 VALID

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase VALID

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2LUW INVALID

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2ZOS INVALID

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderPostgreSQL INVALID

com/pega/pegarules/data/udf/directstreamreader/FixedCacheMap VALID

com/pega/pegarules/data/udf/directstreamreader/UnsupportedFeatureException VALID

com/pega/pegarules/data/internal/clipboard/directstream/BadReferenceException VALID

com/pega/pegarules/data/internal/clipboard/directstream/BadStreamDataException VALID

com/pega/pegarules/data/internal/clipboard/directstream/ByteArrayUtil VALID

com/pega/pegarules/data/internal/clipboard/directstream/DirectStream VALID

com/pega/pegarules/data/internal/clipboard/directstream/DirectStreamEnvironmentAdapter VALID

com/pega/pegarules/data/internal/clipboard/directstream/DirectStreamV7 VALID

com/pega/pegarules/data/internal/clipboard/directstream/InflaterV7 VALID

com/pega/pegarules/data/internal/clipboard/directstream/PropertyReferenceUtilities VALID

com/pega/pegarules/data/internal/clipboard/directstream/ReferenceString VALID

From this query you can see that there are twenty (20) Java classes installed in the rules schema in this environment. Seventeen (17) are valid; three (3) are not valid. The three Java classes that are invalid, indicated by their names, are not applicable to an Oracle installation and can be ignored.

However, if your query returns different results, you might need to take action:

• If your query does not return 15+ Java classes (the number varies depending on the Pega Platform version), then you need to follow the instructions for Installing UDFs after Pega installation or upgrade
• If the UDFs are installed but more than the three (3) Java classes for other databases are invalid, then you need to follow the procedure, Validating invalid UDFs.

Validating invalid UDFs

Follow this procedure to validate the invalid Java classes detected during Verifying UDF installation.

Retrieve a list of installed, invalid UDFs

Test Oracle auto-validation on first-time use

Retrieve a list of installed, invalid UDFs

1. Connect as the schema user because the command you run in the next step queries against user_objects.
2. Run the following command to retrieve a list of UDFs that are installed but not valid.
   Select Dbms_Java.Longname(Object_Name) From User_Objects Where Status!='VALID' AND object_type LIKE 'JAVA%';

Examine the results, which may vary.
This example result set shows all of the Pega Platform UDFs in the RULES schema as invalid.

com/pega/pegarules/data/internal/clipboard/directstream/ReferenceString

com/pega/pegarules/data/internal/clipboard/directstream/PropertyReferenceUtilities

com/pega/pegarules/data/internal/clipboard/directstream/InflaterV7

com/pega/pegarules/data/internal/clipboard/directstream/DirectStreamV7

com/pega/pegarules/data/internal/clipboard/directstream/DirectStreamEnvironmentAdapter

com/pega/pegarules/data/internal/clipboard/directstream/DirectStream

com/pega/pegarules/data/internal/clipboard/directstream/ByteArrayUtil

com/pega/pegarules/data/internal/clipboard/directstream/BadStreamDataException

com/pega/pegarules/data/internal/clipboard/directstream/BadReferenceException

com/pega/pegarules/data/udf/directstreamreader/UnsupportedFeatureException

com/pega/pegarules/data/udf/directstreamreader/FixedCacheMap

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderPostgreSQL

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2ZOS

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2LUW

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase$Version7

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase$Version6

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReader

com/pega/pegarules/data/udf/directstreamreader/CachedDirectStream

com/pega/pegarules/data/udf/directstreamreader/BasicEnvironmentAdapter

Test Oracle auto-validation on first-time use

Sometimes, when the Java classes are installed, they may be in an invalid state. The first time that you use them, Oracle validates them, and there is nothing more that you need to do.
Follow these steps to use the UDFs for the first time to see if Oracle automatically validates them. These queries can be used on any database to test the UDF functions.

Test queries

1. Replace vXYrules with the name of your rules schema and run this command:
   
   SELECT vXYrules.pr_read_from_stream('pzInsKey', pzInsKey, pzPVStream) as "StringValue", vXYrules.pr_read_int_from_stream('pzRuleSetVersionMajor', pzInsKey, pzPVStream) as "IntegerValue", vXYrules.pr_read_decimal_from_stream('pzRuleSetVersionMinor', pzInsKey, pzPVStream) as "DecimalValue" from vXYrules.pr4_rule where pxInsID like '%CANNOT%';
2. Replace vXYdata with the name of your data schema and run this command:
   
   SELECT vXYdata.pr_read_from_stream('pzInsKey', pzInsKey, pzPVStream) as "StringValue", vXYdata.pr_read_int_from_stream('pzRuleSetVersionMajor', pzInsKey, pzPVStream) as "IntegerValue", vXYdata.pr_read_decimal_from_stream('pzRuleSetVersionMinor', pzInsKey, pzPVStream) as "DecimalValue" from vXYdata.pr_data_admin;
3. Examine the results of each query to look for an error.
   In any case, you are not interested in the results of the query but in whether the query throws an error.

• If the query fails with an ORA-29541 error, proceed to Forcing Oracle to Revalidate UDFs.
• If the query is successful, then run the command in Step 2 of Retrieve a list of installed, invalid UDFs again to see if the Java classes are now in a valid state.
• If the only invalid Java classes are the ones associated with non-Oracle databases, then you are finished with validating invalid Java classes used by the UDFs. See the Example.
• If the query results are successful, but the Java classes still show as invalid, proceed to Forcing Oracle to Revalidate UDFs.

Example

SQL> connect vXYdata/vXYdata

Connected.

SQL> Select Dbms_Java.Longname(Object_Name) From User_Objects Where Status!='VAL

ID' AND object_type LIKE 'JAVA%';

DBMS_JAVA.LONGNAME(OBJECT_NAME)

--------------------------------------------------------------------------------

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderPostgreSQL

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2ZOS

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2LUW

SQL> connect vXYrules/vXYrules

Connected.

SQL> Select Dbms_Java.Longname(Object_Name) From User_Objects Where Status!='VAL

ID' AND object_type LIKE 'JAVA%';

DBMS_JAVA.LONGNAME(OBJECT_NAME)

--------------------------------------------------------------------------------

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderPostgreSQL

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2ZOS

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2LUW

Forcing Oracle to revalidate UDFs

To force Oracle to validate invalid Java classes, run the following command for each Java class that is in an invalid state.
As before, replace vXYRULES in this example command with the name of your schema.

​ALTER JAVA CLASS

"com/pega/pegarules/data/internal/clipboard/directstream/ByteArrayUtil"

RESOLVER ((*vXYRULES)(* public))

RESOLVE;

The ResolveUDF.sql script has the commands to resolve all of the Java classes in Pega Platform.
In earlier versions of Pega Platform, not all of these Java classes are applicable.

Consider the following choices for working with the ResolveUDF.sql script:

• Run the script, replacing vXYRULES with your schema name: The UDFs that are not applicable simply return an error.
• Run the script for the UDFs that show as invalid in your environment.

After you run the RESOLVER command for each UDF, you can rerun the following command to make sure that all UDFs are now valid:

Select Dbms_Java.Longname(Object_Name) From User_Objects Where Status!='VALID' AND object_type LIKE 'JAVA%';

If you have tried to resolve all of the Java classes listed above and there are some classes that still do not resolve, you should follow the instructions to drop the UDFs and reinstall them. See Installing UDFs after Pega installation or upgrade.

ResolveUDF SQL script

ALTER JAVA CLASS "com/pega/pegarules/data/internal/clipboard/directstream/ByteArrayUtil"

RESOLVER ((*vXYRULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/internal/clipboard/directstream/BadReferenceException"

RESOLVER ((*vXYRULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/internal/clipboard/directstream/BadStreamDataException"

RESOLVER ((*vXYRULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/internal/clipboard/directstream/DirectStream"

RESOLVER ((*vXYRULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/internal/clipboard/directstream/DirectStreamEnvironmentAdapter"

RESOLVER ((*vXYRULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/internal/clipboard/directstream/InflaterV7"

RESOLVER ((*vXYRULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/internal/clipboard/directstream/PropertyReferenceUtilities"

RESOLVER ((*vXYRULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/udf/directstreamreader/BasicEnvironmentAdapter"

RESOLVER ((*vXYRULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/udf/directstreamreader/CachedDirectStream"

RESOLVER ((*vXYRULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase"

RESOLVER ((*vXYRULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/udf/directstreamreader/DirectStreamReader"

RESOLVER ((*vXYRULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/udf/directstreamreader/FixedCacheMap"

RESOLVER ((*vXYRULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/udf/directstreamreader/UnsupportedFeatureException"

RESOLVER ((*vXYRULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/internal/clipboard/directstream/DirectStreamV7"

RESOLVER ((*vXYRULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase$Version7"

RESOLVER ((*vXYRULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase"

RESOLVER ((*vXYRULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/udf/directstreamreader/DirectStreamReader"

RESOLVER ((*vXYRULES)(* public))

RESOLVE;

Resolving UDF errors

If the UDFs are not installed or have been in some way corrupted, you see the issue in the following ways and can resolve it using the appropriate procedure.

Before opening a Support Request: Must Gather information

If you have followed the instructions to drop and reinstall the UDFs and are still seeing an issue, you need to create a Support Request (SR).

Before submitting your SR, gather the following artifacts to attach to it:

• The file setupDatabase.properties from the run, located in the directory <Pega 7.x Media>/scripts
• The log from executing the generateudf script, which is the file <Pega 7.x Media>/scripts/logs/PRPC_DatabaseLibraryLoader-*.log
• Screenshots of the output from the queries you have run in the section Test queries.

Related Content

When to use — and when not to use — unoptimized properties in reports

Missing columns in Explore Data

Optimize a property from the user interface