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

Unable to reindex Work

SA-66539

Summary



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.



Error Messages



[.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.


Root Cause



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.



Resolution



Make the following change to the operating environment:
  1. Add the missing permission 'execute on dbms_lob' to the appropriate user.
  2. Run the reindex.

Published April 3, 2019 - Updated December 2, 2021

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.

Did you find this content helpful?

Want to help us improve this content?

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
Contact us