SA-20133

Summary

When attempting to read from an unexposed column, the error is received: ORA-29541: class [SCHEMANAME].com/pega/pegarules/data/udf/directstreamreader/DirectStreamReader could not be resolved

Error Messages

Caused by: java.sql.SQLException: ORA-29541: class [SCHEMANAME].com/pega/pegarules/data/udf/directstreamreader/DirectStreamReader could not be resolved

Steps to Reproduce

1. Setup a Pega Environment
2. Backup the schemas (RULES and DATA)
3. Create a new database for a new environment
4. Import the backed-up schemas to the new database

Root Cause

The UDF functions are not resolving properly.  This can occur if problems occured during installation, or if an environment was created using database vendor tools to migrate the environment.

Resolution

In Pega 7, there is a "generateudf" utility script in your installation media "/scripts" folder to regenerate the UDF functions.
Please see /scripts/generateudf.bat for a Windows OS, and /scripts/generateudf.sh for a Unix-based OS. This script calls an ANT script with parameters appropriate to install and compile the java classes into the database and create the necessary functions in the database.

Before beginning, confirm whether these functions are partially installed or not, as the steps below are for a fresh compilation of the UDFs. If they are partially installed, DROP them.

DROP FUNCTION YOURSCHEMA.pr_read_from_stream;
DROP FUNCTION YOURSCHEMA.pr_read_decimal_from_stream;
DROP FUNCTION YOURSCHEMA.pr_read_int_from_stream;

Note: If you have a split schema system, run these statements against both data schema and rules schema. If the functions are already not present, just move on to step 1 below.

To use the generateudf script:

1. Confirm that the setupDatabase.properties are set correctly (database connection URL, username, password, and schema name(s).) This properties' file is also in the /scripts directory. This utility expects to be run with DBA privileges, as it must GRANT execution privileges to the schemas who must run the functions. While a schema owner may be able to create the function, they cannot GRANT permissions to themselves.

2. Run the generateudf.bat (or .sh, as appropriate) with the flags "--action install --dbType YourDatabaseType"
Valid dbTypes are "mssql" for SQL Server, "udb" for DB/2 UDB, "oracledate" for Oracle, "postgres" for PostgreSQL

For example, when running the script on a windows machine, installing the UDFs onto an Oracle database the command would be:
C:\PegaInstallationMedia\scripts> generateudf.bat --action install --dbType oracledate

Please be advised that this script will be running a java instance on the machine running the script. Ensure that you have java installed and a sufficient amount of free RAM for it to run.