Data Purging fails with ORA-02292: integrity constraint (AVUSER.FK_AFX_REQUEST_CHANGE_ITEM_ID) violated - child record found in RSA Identity Governance & Lifecycle
18 hours ago
Originally Published: 2019-05-28
Article Number
000042090
Applies To
RSA Product Set: Identity Governance & Lifecycle
RSA Version/Condition: 7.1.1 P01
Issue

The RSA Identity Governance & Lifecycle Data Purging utility fails with the following errors:

ORA-01722: invalid number
 
ORA-02292: integrity constraint (AVUSER.FK_AFX_REQUEST_CHANGE_ITEM_ID) violated - child record found


The errors can be seen in the aveksaServer.log: 

05/12/2019 09:45:32.177 INFO (ThreadPool-9) [com.aveksa.server.xfw.ExecutionRequest] Notify execution request for ExecutionContext[ID=157653 Type=DataPurging Status=New NumTasks=1 tasks=ExecutionTask[TaskID=384389 RunID=157653 Source=1 Type=DataPurging Status=New]]
05/12/2019 09:45:32.195 INFO (Thread-37 (ActiveMQ-client-global-threads-583397458)) [com.aveksa.server.xfw.ExecutionServiceProvider] Notified execution request for ExecutionContext[ID=157653 Type=DataPurging Status=New NumTasks=1 tasks=ExecutionTask[TaskID=384389 RunID=157653 Source=1 Type=DataPurging Status=New]]
05/12/2019 09:45:33.289 INFO (Exec Task Consumer#1 - Main) [com.aveksa.server.xfw.TaskExecutor] Setting thread Thread[Exec Task Consumer#1 - Main,5,Execution Task] on 93801115 method=Execute
05/12/2019 09:48:52.157 INFO (Exec Task Consumer#1 - Main) [com.aveksa.server.db.persistence.PersistenceServiceProvider] executeCallableStatement giving up after hitting SQLException: ORA-01722: invalid number
ORA-06512: at "AVUSER.DATA_PRUNING_PKG", line 130
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1073
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1175
ORA-02292: integrity constraint (AVUSER.FK_AFX_REQUEST_CHANGE_ITEM_ID) violated - child record found
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1148
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1028
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1028
ORA-06512: at "AVUSER.DATA_PRUNING_PKG", line 127
ORA-06512: at "AVUSER.DATA_PRUNING_PKG", line 93
ORA-06512: at line 1
05/12/2019 09:48:52.158 WARN (Exec Task Consumer#1 - Main) [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 1722, SQLState: 42000
05/12/2019 09:48:52.160 ERROR (Exec Task Consumer#1 - Main) [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] ORA-01722: invalid number
ORA-06512: at "AVUSER.DATA_PRUNING_PKG", line 130
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1073
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1175
ORA-02292: integrity constraint (AVUSER.FK_AFX_REQUEST_CHANGE_ITEM_ID) violated - child record found
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1148
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1028
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1028
ORA-06512: at "AVUSER.DATA_PRUNING_PKG", line 127
ORA-06512: at "AVUSER.DATA_PRUNING_PKG", line 93
ORA-06512: at line 1
05/12/2019 09:48:52.185 ERROR (Exec Task Consumer#1 - Main) [com.aveksa.server.xfw.TaskExecutor] Failed method=Execute ExecutionTask[TaskID=384389 RunID=157653 Source=1 Type=DataPurging Status=InProgress] com.aveksa.server.xfw.ExecutionException: com.aveksa.server.db.PersistenceException: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
ORA-06512: at "AVUSER.DATA_PRUNING_PKG", line 130
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1073
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1175
ORA-02292: integrity constraint (AVUSER.FK_AFX_REQUEST_CHANGE_ITEM_ID) violated - child record found
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1148
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1028
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1028
ORA-06512: at "AVUSER.DATA_PRUNING_PKG", line 127
ORA-06512: at "AVUSER.DATA_PRUNING_PKG", line 93
ORA-06512: at line 1
at com.aveksa.server.xfw.DataPruningTaskExecutor.executeTask(DataPruningTaskExecutor.java:58)
at com.aveksa.server.xfw.TaskExecutor.execute(TaskExecutor.java:99)
at com.aveksa.server.xfw.ExecutionTaskQueue$Worker.run(ExecutionTaskQueue.java:116)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.aveksa.server.db.PersistenceException: java.sql.SQLSyntaxErrorException:
ORA-01722: invalid number
ORA-06512: at "AVUSER.DATA_PRUNING_PKG", line 130
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1073
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1175
ORA-02292: integrity constraint (AVUSER.FK_AFX_REQUEST_CHANGE_ITEM_ID) violated - child record found
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1148
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1028
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1028
ORA-06512: at "AVUSER.DATA_PRUNING_PKG", line 127
ORA-06512: at "AVUSER.DATA_PRUNING_PKG", line 93
ORA-06512: at line 1
at com.aveksa.server.db.persistence.PersistenceServiceProvider.runStoredProcedure(PersistenceServiceProvider.java:1548)
at com.aveksa.server.db.persistence.PersistenceServiceProvider.runStoredProcedure(PersistenceServiceProvider.java:1480)
at com.aveksa.server.db.PersistenceManager.runStoredProcedure(PersistenceManager.java:245)
at com.aveksa.server.xfw.DataPruningTaskExecutor.executeTask(DataPruningTaskExecutor.java:56)
... 3 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
ORA-06512: at "AVUSER.DATA_PRUNING_PKG", line 130
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1073
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1175
ORA-02292: integrity constraint (AVUSER.FK_AFX_REQUEST_CHANGE_ITEM_ID) violated - child record found
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1148
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1028
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1028
ORA-06512: at "AVUSER.DATA_PRUNING_PKG", line 127
ORA-06512: at "AVUSER.DATA_PRUNING_PKG", line 93
ORA-06512: at line 1 at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:223)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:56)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:907)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)
at oracle.jdbc.driver.T4CCallableStatement.executeInternal(T4CCallableStatement.java:1300)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3887)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4230)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1079)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:442)
at com.aveksa.server.db.persistence.work.PersistenceServiceBaseWork.executeCallableStatement(PersistenceServiceBaseWork.java:36)
at com.aveksa.server.db.persistence.work.StoredProcedureCallWork.execute(StoredProcedureCallWork.java:80)
at org.hibernate.jdbc.WorkExecutor.executeWork(WorkExecutor.java:37)
at org.hibernate.internal.SessionImpl$3.accept(SessionImpl.java:2134)
at org.hibernate.internal.SessionImpl$3.accept(SessionImpl.java:2131)
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.coordinateWork(JdbcCoordinatorImpl.java:332)
at org.hibernate.internal.SessionImpl.doWork(SessionImpl.java:2153)
at org.hibernate.internal.SessionImpl.doWork(SessionImpl.java:2138)
at com.aveksa.server.db.persistence.PersistenceServiceProvider.runStoredProcedure(PersistenceServiceProvider.java:1526)
... 6 more
Caused by: Error : 1722, Position : 0, Sql = BEGIN Data_Pruning_Pkg.Main(:1 ,:2 ,:3 ); END;, OriginalSql = {call Data_Pruning_Pkg.Main(?,?,?)}, Error Msg = ORA-01722: invalid number
ORA-06512: at "AVUSER.DATA_PRUNING_PKG", line 130
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1073
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1175
ORA-02292: integrity constraint (AVUSER.FK_AFX_REQUEST_CHANGE_ITEM_ID) violated - child record found
RA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1148
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1028
ORA-06512: at "AVUSER.ARCHIVEPURGE_PKG", line 1028
ORA-06512: at "AVUSER.DATA_PRUNING_PKG", line 127
ORA-06512: at "AVUSER.DATA_PRUNING_PKG", line 93
ORA-06512: at line 1 at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
... 30 more
05/12/2019 09:48:52.197 INFO (Exec Task Consumer#1 - Main) [com.aveksa.server.xfw.TaskExecutor] UNSetting thread on 93801115 method=Execute
05/12/2019 09:48:52.250 INFO (Exec Context Runner#157653 DataPurging) [com.aveksa.server.core.DBEventUtilities] Published 0 DB events for run 157653
05/12/2019 09:48:52.281 INFO (Exec Context Runner#157653 DataPurging) [com.aveksa.server.xfw.ExecutionContext] Successful method=Execute 157653, DataPurging
 


If your foreign key constraint error is AVUSER.FK_T_PCS_EXN_HY_PC_ID_T_PCS_ID, AVUSER.FK_T_IDCAV_T_IDCA_ID, or AVUSER.FK_T_IDCAH_IDCAID_T_IDCA_ID, please see article 000037618 - Data purging fails with ORA-02292: integrity constraint (AVUSER.FK_xxxxxx violated - child record found) in RSA Identity Governance & Lifecycle. The symptom is the same, but the problem cause is different.

Please refer to 000030327 - Artifacts to gather in RSA Identity Governance & Lifecycle to find the location of the log files for your specific deployment.

Cause
The migration script missed one of the internal RSA Identity Governance & Lifecycle change request tables.
Resolution
This issue is resolved in the following RSA Identity Governance & Lifecycle patches:
RSA Identity Governance & Lifecycle 7.1.0 P08
RSA Identity Governance & Lifecycle 7.1.1 P02

Related Articles

Trending Articles

Don't see what you're looking for?