Reporter Oracle 6.10.103 or superior fails with could not extract ResultSet ORA-02289: sequence does not exist

Products

• CA Automic Dollar Universe

Issue/Introduction

After upgrading Reporter to 6.10.103 or superior with an Oracle Database, the Report generations and the Extractions do not work anymore.

The following errors appear in the server.log:

[INFO ] 17 Mar 2023 09:59:32 [pool-2-thread-3] com.orsyp.reppub.server.reporter.etl.ExtractionSourceETL - [17 Mar 2023 09:59:32] Extraction registered to ExecutionManager in RUNNING status.
[ERROR] 17 Mar 2023 09:59:32 [pool-2-thread-3] com.orsyp.reppub.server.manager.db.rep.RepExtSourceStatusPMImpl - null
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
        at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:195) ~[spring-orm.jar:5.3.21]
...
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
        at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63) ~[hibernate-core.jar:5.6.9.Final]
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37) ~[hibernate-core.jar:5.6.9.Final]
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) ~[hibernate-core.jar:5.6.9.Final]
...
Caused by: java.sql.SQLSyntaxErrorException: ORA-02289: sequence does not exist
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630) ~[ojdbc8.jar:21.3.0.0.0] 

Environment

Release : 6.10.103 or superior

Component: Reporter Server

Database: Oracle only

Cause

Instructions for upgrading Reporter to 6.10.103 with Oracle as Database not applied as indicated here

Resolution

After upgrading to the hotfix 6.10.103, you must execute the alter_db_oracle_v61051_to_v610103 script available at the following location to enable the Reporter feature with Oracle Database:

<Reporter Server>\dbscripts\oracle\alter_db_oracle_v61051_to_v610103

The script execution is mandatory only when you upgrade to the hotfix 6.10.103 release or superior.