Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

Alter PR_INDEX_CIRCUMSTANCE_DEF errors when adding Circumstance

SA-9415

Summary



The System Administrator configures the underlying database so that the PegaRULES user does not have the authority to run DDL SQL statements (CREATE, ALTER, DROP, etc...)

For most PRPC related operations this works  perfectly fine as this is generating DML SQL statements (INSERT, UPDATE, DELETE, etc...)
However, the Developers had observed errors when they attempted to create a Circumstanced rule based on a Property.

Error Messages



Presented to the user: -
 
    Can't create Circumstance Index because the following column can't be created: prop11.
    alter table pr_index_circumstance_def add (prop11 VARCHAR2(32))


In the logs: -
 
com.pega.pegarules.pub.database.DatabaseException: Database-General    Problem executing SQL to update database schema    1031    42000    ORA-01031: insufficient privileges

From: (H751F75781ED88F4589ED786FA231395F:10.10.10.10) 
    SQL: alter table pr_index_circumstance_def add  (prop11 VARCHAR2(32) )

Caused by SQL Problems.
Problem #1, SQLState 42000, Error code 1031: java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges

    at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:167)
    at com.pega.pegarules.data.internal.access.DatabaseImpl.executeDBSchemaDml(DatabaseImpl.java:6843)
    at com.pega.pegarules.data.internal.access.DatabaseImpl.executeDBAddIndexColumns(DatabaseImpl.java:6579)
    at com.pega.pegarules.session.internal.mgmt.Executable.executeDBAddIndexColumns(Executable.java:7229)
    at com.pegarules.generated.activity.ra_action_savecircumstanceindex_2b421901b7abe0e8f471e24364a7615e.step4_circum0(ra_action_savecircumstanceindex_2b421901b7abe0e8f471e24364a7615e.java:777)
    at com.pegarules.generated.activity.ra_action_savecircumstanceindex_2b421901b7abe0e8f471e24364a7615e.perform(ra_action_savecircumstanceindex_2b421901b7abe0e8f471e24364a7615e.java:133)
    at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3020)


Steps to Reproduce



1. Create an new property.
2. Open an existing Section.
3. Click on the Save As button.
4. In the Save As dialog, expand the Circumstance group.
5. Choose the property created in step 1.
6. Enter an arbitrary Value.
7. Click on the Save As button.

Root Cause



When creating a Circumstanced rule this automatically builds and issue the SQL to add a new column to the PR_INDEX_CIRCUMSTANCE_DEF table based on the selected Property.

Resolution



While there is no option to prevent PRPC from trying to amend the PR_INDEX_CIRCUMSTANCE table, you must configure your PRPC instance to use an AdminPegaRULES data source.

https://pdn.pega.com/deployment/how-to-provide-create-table-and-alter-table-database-privileges-in-an-admin-database-account

This allows you to configure the standard PegaRULES datasource with with a database user that only has privileges to run DML SQL commands. Automatically switches to a more privileged AdminPegaRULES database user when performing operations that require DDL SQL activities.
 

Tags:

Pega Platform Pega Platform 6.2 SP2 Data Integration

Published June 12, 2015 - Updated October 8, 2020

Was this useful?

0% found this useful

Have a question? Get answers now.

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

Visit the Collaboration Center

Did you find this content helpful?

Yes
No

Want to help us improve?
Suggest an edit
Report a Pega Community bug

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