ORA-00942: table or view does not exist when defining a custom report query in RSA Identity Governance & Lifecycle
3 years ago
Originally Published: 2014-10-20
Article Number
000062695
Applies To
RSA Product Set: Identity Governance & Lifecycle
RSA Version/Condition: 7.x
Issue
When 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.

 

 

Cause
The 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.

Workaround
A 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)
Don't see what you're looking for?