000027957 - ORA-00942: table or view does not exist when defining a custom report query in RSA Identity Governance & Lifecycle

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support on Jul 8, 2019
Version 5Show Document
  • View in full screen mode

Article Content

Article Number000027957
Applies ToRSA Product Set: Identity Governance & Lifecycle
RSA Version/Condition: 7.x
IssueWhen building a custom report in RSA Identity Governance & Lifecycle (Reports > Tabular > Create Report > Query tab), the following error occurs when testing the SQL statement in the FROM clause:

com.aveksa.server.runtime.ServerException:
com.aveksa.server.runtime.ServerException:
java.sql.SQLSyntaxErrorException: ORA-00942:
          table or view does not exist


This occurs when using a public view (a view prefaced with PV_) and it can be seen in the AVUSER schema.

 
CauseThe RSA Identity Governance & Lifecycle Report module points to schema AVDWUSER and not AVUSER.
Resolution

AVDWUSER is the schema where all the public views referred to in the RSA Identity Governance and Lifecycle <version> Public Database Schema Reference Guide are stored. Click the link to find the documentation for your version.

Public views in AVUSER are defined as synonyms in AVDWUSER. To resolve this issue, rewrite your query to refer to the public view names in the public database schema.

For example, PV_ACCOUNT in AVUSER is ACCOUNT in AVDWUSER. Rewrite a query referring to this view as in the example below.

Change the query from:




(SELECT * FROM PV_ACCOUNT)

    to:


(SELECT * FROM ACCOUNT)



Note: Queries in report definitions must be surrounded by parentheses.

WorkaroundA workaround would be to preface the view name with the AVUSER schema name as in:
 
Change the query from:
 



(SELECT * FROM PV_ACCOUNT)

     to:


(SELECT * FROM AVUSER.PV_ACCOUNT)

Attachments

    Outcomes