Support Article
Report Definition Error: missing right parenthesis
SA-83771
Summary
On executing a report definitoin, an invalid SQL is generated and executed. As a result, an error displays on the screen.
Error Messages
Error seen on screen when executing the report:
An error occurred on executing the query for the report definition - There was a problem getting a list: 907 SQLState: 42000
Message: ORA-00907: missing right parenthesis
DatabaseException caused by prior exception: java.sql.SQLSyntaxErrorException: ORA0090: missing right parenthesis | SQL Code: 907 | SQL State: 42000
Error extract from the PegaRULES log file:
[xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx][STANDARD][xxxxx:01.03][rd.queryexec.SqlQueryExecutor][1;31m[ERROR][xxxxxx-xxxx-xxx.xxxxxxx.xxxxxx.xx|xxxxxxxx.xxxxxxxxx.xxxxxx.xx- An error occured on executing the query for the report definition - There was a problem getting a list: code: 907 SQLState: 42000 Message: ORA-00907: missing right parenthesis
DatabaseException caused by prior exception: java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis
| SQL Code: 907 | SQL State: 42000
com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 907 SQLState: 42000 Message: ORA-00907: missing right parenthesis
DatabaseException caused by prior exception: java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis
| SQL Code: 907 | SQL State: 42000
at com.pega.pegarules.data.internal.access.ExceptionInformation.createAppropriateExceptionDueToDBFailure(ExceptionInformation.java:381) ~[prprivate.jar:?]
at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:360) ~[prprivate.jar:?]
.
.
.
Caused by: java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis
The error returned is different on other Databases.
For example, the following error occurs when reproducing the error on a system using a PostgreSQL:
An error occured on executing the query for the report definition - There was a problem getting a list: code: 0 SQLState: 42601 Message: ERROR: syntax error at or near "ALL" Position: 532 DatabaseException caused by prior exception: org.postgresql.util.PSQLException: ERROR: syntax error at or near "ALL" Position: 532 | SQL Code: 0 | SQL State: 42601
Steps to Reproduce
- Configure a report definition that uses Sub-reports.
- Use a prefix of 'ALL' for one of the Sub-reports.
- Execute the report.
Root Cause
The Sub-report configured on the Data Access tab of the Report Definition used a prefix of 'ALL' which is also a keyword in Oracle (UNION ALL). This caused the query generation logic to generate an invalid query.
Resolution
Perform the following local-change:
Change the Sub-report prefix such that it is different from the Oracle keyword, for example 'All1'.
Published November 26, 2019 - Updated December 2, 2021
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.