Summary

 After a new installation configured with an Oracle database is complete, a database user with the Database Administrator (DBA) role starts Process Commander and encounters the following error message in the PegaRULES log:

2009-10-19 13:36:01,964 [PegaRULES-MasterAgent] [ STANDARD] [ ] ( engine.context.AgentRuleUtils) ERROR - Error saving Data-Agent-Queue instance for: Pega-ProCom on node c027064b67c1bc18c823244784ebb835 com.pega.pegarules.pub.database.DatabaseException: Database-Saver-UpdateInsertfail

This error is caused by the SQL problem:

Problem #1, SQLState 42000, Error code 904: java.sql.SQLException: ORA-00904: "THEME": invalid identifier

Multiple users are assigned the DBA role. Some users in the DBA role do not have all the privileges they need. Missing privileges for DBA users cause the system to report that the tablespace quota is exceeded. In addition, developers encounter the 904 SQLSTATE error when they try to save rules to the database after upgrading from Process Commander Version 4.x to Version 5.x:

Save failed. Problem writing an instance to the database: Code: 904 SQLSTATE: 42000 Message: ORA-00904: “PYPOSITION”: Invalid Identifier. Obj-Save is trying to write to a non-existent column.

To prevent the ORA-00904 error, follow the suggested approaches for DBA users and for developers trying to save rules after upgrading from Version 4.x to Version 5.x.

Explanation

When a single Oracle database has more than one user with DBA privileges, the first DBA starts the database engine for the database instance that supports Process Commander. A second DBA starts the database engine and encounters the error. For example, User A has the DBA role with Connect and Resource privileges for Database 1 and is using Database 1 successfully. New User B also has the DBA role with Connect and Resource privileges. Upon starting the database engine for Process Commander, User B encounters the error message.

Developers trying to save rules to the database can encounter the ORA-00904 error when upgrade and installation scripts contain references to database columns that no longer exist. This is apt to occur when upgrading from Process Commander Version 4.x to Version 5.x because the Installation and Upgrade scripts for Process Commander Version 5.x contain references to database columns of Version 4.x. This known issue causes the ORA-00904 error when developers try to save any rule (for example, Rule-Obj-Activity) to the pr4_rule table.

Suggested Approach

For users in the DBA role, complete these steps:

1. Remove the DBA role for User B.
2. In the user account for User B ensure that this user is assigned the privileges Connect and Resource as well as the Unlimited Tablespace privilege.

   Assigning the Unlimited Tablespace privilege to all users in the DBA role prevents DBAs from encountering the ORA-00904: "THEME": invalid identifier.

3. Reassign the DBA role to User B.

For developers working with upgrade and installation scripts that contain references to database columns that no longer exist, complete these steps:

1. Delete the database columns that do not belong in the newly installed system
2. Remove the DBA role.

Alternative: If a single database needs to use more than one PegaRULES database schema, specify the schema name for each database table reference. For the procedure, refer to the Troubleshooting topic cited below.

Related Topics

Troubleshooting: "Obj-Save is trying to write to a non-existent column"

For more information, refer to the Installation Guide for your application server and version of Process Commander:

Index: Process Commander V5 Documentation