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

Alternate database option in list view is not working

SA-41725

Summary



The alternate database option in List View / Report Definition does not work when the schema name used by the alternate database is different to the main Pega Data schema.

Error Messages



Log file error examples:

ERROR xxxxxxxx|xx.x.x.xxxx [email protected] - Not returning connection 3 for database "pega721rep" to the pool as it previously encountered the following error
User ID: [email protected]
Last SQL: SELECT "PC0"."PYID" AS "pyID" , "PC0"."PYSTATUSWORK" AS "pyStatusWork" , "PC0"."PZINSKEY" AS "pzInsKey" FROM PEGA721.pc_SR_Sandbox_Work "PC0" WHERE "PC0"."PXOBJCLASS" IN (? , ? , ? , ? , ? , ? , ? , ? , ? )
java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
.

.
.

ERROR xxxxxxxx|xx.x.x.xxxx [email protected] - An error occurred on executing the query for the report definition - There was a problem getting a list: code: 942 SQLState: 42000 Message: ORA-00942: table or view does not exist

DatabaseException caused by prior exception: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

| SQL Code: 942 | SQL State: 42000





On the screen:



Steps to Reproduce

  1. Configure a reports database as documented in the help, where the schema name is not the same as the main Pega Data schema. (For example a separate schema in the same physical database).
  2. Create a List View or Report Definition and check the use alternate database option.
  3. Run the report


Root Cause



The system assumes that the schema name of the alternate / reporting database will be the same as the main Pega Data schema, and the SQL queries generated for the report will prefix the table name with this schema name rather than using the one defined in the database rule for the alternate database.

For example, the query run for the report is as below. The alternate database schema is "PEGA721REP", but the query uses PEGA721 as the table prefix.


SELECT "PC0"."PYID" AS "pyID" , "PC0"."PYSTATUSWORK" AS "pyStatusWork" , "PC0"."PZINSKEY" AS "pzInsKey" FROM PEGA721.pc_SR_Sandbox_Work "PC0" WHERE "PC0"."PXOBJCLASS" IN (? , ? , ? , ? , ? , ? , ? , ? , ? )

Resolution

  • List Views are deprecated in favour of Report Definitions. If using a List View, first update to a Report Definition.
  • Apply HFix-35521 Alternate DB option in list view fails after Pega 7 upgrade (Pega 7.2.1)
Once installed the following prconfig setting can be used to specify the default schema name for the alternate / reporting database. A restart will be required for this setting to take effect.

database/databases/<DB Name>/defaultSchema
value: <Schema Name>


Examples:

prconfig.xml


<env name="database/databases/<Database Name>/defaultSchema" value="<Schema Name>"/>

Dynamic System Setting

Setting Purpose: prconfig/database/databases/<Database Name>/defaultSchema/default
Value: <Schema Name>
Suggest Edit

Published August 26, 2017 - Updated October 8, 2020

Did you find this content helpful? Yes No

Have a question? Get answers now.

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

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