Unable to reindex Work
Pega 7.4 environment is installed with the Pega Foundation for Healthcare (PFHC), Customer Relationship Management (Pega CRM), Customer Relationship Management for HealthCare (CRMHC), Product Composer System for HealthCare (PCSH), and Pega Knowledge Management (KM) frameworks.
Unable to reindex Work classes.
[.PRPCWorkManager : 1] [ STANDARD] [ ] [ PegaRULES:07.10] (Search.Indexer.AbstractIndexer) WARN adm2l82 - Failed to index instance with key [YOUR_CLASS-WORK-INTERACTION I-181]
com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 904 SQLState: 42000 Message: ORA-00904: : invalid identifier
DatabaseException caused by prior exception: java.sql.SQLSyntaxErrorException: ORA-00904: : invalid identifier
Steps to Reproduce
Run the work index.
A defect or configuration issue in the operating environment.
According to the PegaRULES Alert log EXCEP message, below is the SQL which failed.
SELECT "pc1".pxLinkedRefTo AS "pxLinkedRefTo", "pc1".pxLinkedClassTo AS "pxLinkedClassTo", DBMS_LOB.GETLENGTH("pc2".pzPVStream) AS "pzAttachSize" FROM PEGA_SVP_DATA.pc_link_attachment "pc1" INNER JOIN PEGA_SVP_DATA.pc_data_workattach "pc2" ON ("pc1".pxLinkedRefTo = "pc2".pzInsKey ) WHERE "pc1".pxLinkedRefFrom = ?
When the SQL is run using the SQL Developer by connecting as the same user as Pega database, the reindexing failed on the below query:
DBMS_LOB.GETLENGTH("pc2".pzPVStream) AS "pzAttachSize"
This is an Oracle - ORA-00904::invalid identifier.
According to the table structure, the pPVStream column exists, however, the query fails with an invalid identifier. That is, the query failed due to a database permission issue.
Make the following change to the operating environment:
- Add the missing permission 'execute on dbms_lob' to the appropriate user.
- Run the reindex.
0% found this useful