Symptom

This issue can arise from any of several causes -- such as work objects locked, or inability to sign on to the system.

Errors appear in the log showing that the system is failing due to a database exception on Obj-Save for one of the tables:

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.

Solution

Explanation

This error can occur when two different Process Commander database schemas are present in the same database, but the Data-Admin-DB-Table instances do not explicitly identify which schema to use.

Two or more schemas can be present in one database. The issue occurs when a database contains multiple schema, supporting multiple versions of Process Commander, where the corresponding tables have differing columns. 

For example, if you're running Version 4.2, and you install Version 5.1 into the same database instance, then the Version 5.1 schema has additional columns in some of the tables.   

Such differences in table columns can arise even without a major version change; it could occur with just a difference in minor versions, or even two different setups (“dev” and “test”) on one database, as long as the database schemas differ.  In addition, this situation may also occur when a database schema is moved from one system (“dev”) into another (“test”).  If the schema are named with one name, and they are moved into a system where the schema are named differently in the database, the same kinds of problems will occur.

When setting up a new Process Commander system, the DBA must create an user ID for Process Commander to use to access the database. The system uses that ID — which only sees one specific schema —for doing saves, so the correct schema will be used when data is stored.

However, a JDBC statement is used to retrieve column information from the database. This statement is invoked the first time the system needs to read from or write to each table in the database, to fetch the column information for that table. 

To identify the schema, this JDBC statement relies on the information entered into the Database Table instances (Data-Admin-DB-Table class). 

If the database contains two similar schemas in the database, but neither is uniquely identified in the Database Table instance, then the JDBC statement returns columns from all tables in the database with the requested table name (“pc_work”), no matter what schema they belong to. 

For example, if a database includes a Schema42 table named pc_work table with forty columns, and a Schema51 pc_work table with the same forty columns plus a new column (“.pyNewColumn”), then all of those columns are returned to the system for “pc_work” (unless the change below is done). 

After getting the column information, if the system then tries to write to this table using the (Schema42) schema that does not contain the .pyNewColumn, then the above error occurs. 

Even though the column doesn’t exist for this schema, the system thinks it should, because it found that column in a table of the same name.

Resolution

To have more than one Process Commander schema in a single database, identify each table in the database as belonging to a specific schema.   

Complete both of the following tasks:

1. Update the pegarules.xml file (V4.2) or prconfig.xml file (V5.X). 

The database/baseTable/schema setting holds the schema name for the base table for this installation. 

This setting is not filled in by the installation process. The DBA or the system administrator must add this setting to the file after the installation is complete.

2. Update the Database Table instance to identify the schema name.

In each Database Table form (instances of Data-Admin-DB-Table), you can enter a schema name. 

Enter the schema name for the installation in this database (“Dev42” and “Dev51”) into each Database Table instance.  

This identifies the tables as belonging to a particular schema, and prevents the system from reading incorrect columns and then displaying the error.