Skip to main content
LinkedIn
Copied!

Table of Contents

Troubleshooting user-defined functions

This content applies only to On-Premises Services environments.

This article 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, the upgrade 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.

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.

UDF errors, explanations, and actions to take

Symptom or Error

Explanation

Action to Take

ORA-29541 or
ORA-00904 errors
involving pr_read_from_stream

Cannot resolve the UDF function

Cannot find the UDF that is being queried

Verifying UDF installation

When the UDFs are used and the data shown in the report is almost correct, but has some minor issues like extra spaces or spelling errors

UDFs are found but, especially when the UDFs are installed in another schema and migrated to a new schema using database specific tools, they can become corrupted. If the UDFs are corrupted, you must reinstall them.

Installing UDFs after Pega installation or upgrade

ORA-04043 error

Rather than being unresolved Java classes, the necessary Java classes are not even in the database.

Installing UDFs after Pega installation or upgrade

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.

 

Did you find this content helpful?

100% found this useful


Related Content

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Ready to crush complexity?

Experience the benefits of Pega Community when you log in.

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us