Data Purging fails with ORA-02292: integrity constraint (AVUSER.FK_AFX_REQUEST_CHANGE_ITEM_ID) violated - child record found in RSA Identity Governance & Lifecycle
Originally Published: 2019-05-28
Article Number
Applies To
RSA Version/Condition: 7.1.1 P01
Issue
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
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)
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
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
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)
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.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.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)
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
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
Resolution
RSA Identity Governance & Lifecycle 7.1.0 P08
RSA Identity Governance & Lifecycle 7.1.1 P02
RSA Identity Governance & Lifecycle 7.1.1 P02
Related Articles
Data purging fails with ORA-02292: integrity constraint (AVUSER.FK_xxxxxx violated - child record found) in RSA Identity G… Number of Views Merging databases together in ACE/Server 5.0 Number of Views Indirect Relationship Processing slower in Step 11/12 in RSA Governance & Lifecycle Number of Views Indirect Relationship processing slow or fail with ORA-01652 on TEMP table space using Identity Governance and Lifecycle Number of Views RSA Identity Governance and Lifecycle 7.2 Upgrade and Migration Guide Number of Views
Trending Articles
How to recover the Application and AFX after an unexpected database failure in RSA Identity Governance & Lifecycle Troubleshooting AFX Connector issues in RSA Identity Governance & Lifecycle RSA MFA Agent 2.3.6 for Microsoft Windows Installation and Administration Guide RSA Release Notes for RSA Authentication Manager 8.8 RSA Authentication Manager 8.9 Release Notes (January 2026)
Don't see what you're looking for?
