My Oracle Support Banner

Error "java.sql.SQLSyntaxErrorException: ORA-01722: invalid number" While Running Certification Schedule Jobs (Doc ID 2641636.1)

Last updated on AUGUST 12, 2021

Applies to:

Identity Manager - Version 12.2.1.3.0 and later
Information in this document applies to any platform.

Goal

 While running Certification schedule jobs following error is being thrown frequently in OIM logs:

----------------------------------------------------------------------------------------------------------------------------------------

[2020-02-20T15:01:45.753-06:00] [oim_server1] [NOTIFICATION] [] [oracle.iam.identity.usermgmt.impl] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0]  NOPAGING:: Search Criteria: arg1 = (arg1 = (Status) NOT_EQUAL arg2 = (Deleted)) AND arg2 = (arg1 = (usr_key) EQUAL arg2 = (null))
[2020-02-20T15:01:45.756-06:00] [oim_server1] [NOTIFICATION] [] [oracle.iam.identity.usermgmt.impl] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0]  NOPAGING:: PAGING_LIMIT system property not found. Doing Nothing
[2020-02-20T15:01:45.756-06:00] [oim_server1] [NOTIFICATION] [] [oracle.iam.identity.usermgmt.impl] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0] Searching for users with the specified criteria.
[2020-02-20T15:01:45.757-06:00] [oim_server1] [TRACE:32] [] [oracle.iam.oimdataproviders.impl] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0] [SRC_CLASS: oracle.iam.oimdataproviders.impl.OIMDataProvider] [SRC_METHOD: getSearchFilter] Search Criteria : arg1 = (usr_status) NOT_EQUAL arg2 = (Deleted)
[2020-02-20T15:01:45.757-06:00] [oim_server1] [TRACE:32] [] [oracle.iam.oimdataproviders.impl] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0] [SRC_CLASS: oracle.iam.oimdataproviders.impl.OIMDataProvider] [SRC_METHOD: getSearchFilter] SQL Query : (usr.usr_status IS NULL OR UPPER(usr.usr_status) != (UPPER(?)))
[2020-02-20T15:01:45.757-06:00] [oim_server1] [TRACE:32] [] [oracle.iam.oimdataproviders.impl] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0] [SRC_CLASS: oracle.iam.oimdataproviders.impl.OIMDataProvider] [SRC_METHOD: getSearchFilter] Search Criteria : arg1 = (usr_key) EQUAL arg2 = (null)
[2020-02-20T15:01:45.757-06:00] [oim_server1] [TRACE:32] [] [oracle.iam.oimdataproviders.impl] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0] [SRC_CLASS: oracle.iam.oimdataproviders.impl.OIMDataProvider] [SRC_METHOD: getSearchFilter] SQL Query : usr.usr_key = ?
[2020-02-20T15:01:45.758-06:00] [oim_server1] [TRACE:32] [] [oracle.iam.oimdataproviders.impl] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0] [SRC_CLASS: oracle.iam.oimdataproviders.impl.OIMDataProvider] [SRC_METHOD: getSearchFilter] Search Criteria : arg1 = (arg1 = (usr_status) NOT_EQUAL arg2 = (Deleted)) AND arg2 = (arg1 = (usr_key) EQUAL arg2 = (null))
[2020-02-20T15:01:45.758-06:00] [oim_server1] [TRACE:32] [] [oracle.iam.oimdataproviders.impl] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0] [SRC_CLASS: oracle.iam.oimdataproviders.impl.OIMDataProvider] [SRC_METHOD: getSearchFilter] SQL Query : ( (usr.usr_status IS NULL OR UPPER(usr.usr_status) != (UPPER(?))) AND usr.usr_key = ? )
[2020-02-20T15:01:45.758-06:00] [oim_server1] [TRACE] [] [oracle.iam.oimdataproviders.impl] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0] [SRC_CLASS: oracle.iam.oimdataproviders.impl.OIMDataProvider] [SRC_METHOD: search] oracle.iam.oimdataproviders.impl.OIMUserDataProvider
[2020-02-20T15:01:45.759-06:00] [oim_server1] [ERROR] [] [oracle.iam.oimdataproviders.impl] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0] ORA-01722: invalid number[[
 java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:947)
..................
    at oracle.iam.identity.usermgmt.impl.UserSearch.search(UserSearch.java:395)
    at oracle.iam.identity.usermgmt.impl.UserManagerImpl.search(UserManagerImpl.java:1567)
..................

    at oracle.iam.catalog.util.Utility.fetchUserInformation(Utility.java:273)
    at oracle.iam.catalog.util.Utility.buildCatalogfromResultSet(Utility.java:238)

..................
    at oracle.iam.certification.impl.IDCCatalogUtil.findCatalogs(IDCCatalogUtil.java:82)
    at oracle.iam.certification.impl.IDCCatalogUtil.getCertifiableAppInstanceIds(IDCCatalogUtil.java:131)
    at oracle.iam.certification.scheduledtasks.CertificationCreationTask.getAllApplicationInstanceIds(CertificationCreationTask.java:946)
    at oracle.iam.certification.scheduledtasks.CertificationCreationTask.createUserCertifications(CertificationCreationTask.java:335)    
..................
    


[2020-02-20T15:01:45.849-06:00] [oim_server1] [TRACE:32] [] [oracle.iam.oimdataproviders.impl] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0] [SRC_CLASS: oracle.iam.oimdataproviders.impl.OIMDataProvider] [SRC_METHOD: getSearchFilter] Search Criteria : arg1 = (ugp_key) EQUAL arg2 = (22275)
[2020-02-20T15:01:45.849-06:00] [oim_server1] [TRACE:32] [] [oracle.iam.oimdataproviders.impl] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0] [SRC_CLASS: oracle.iam.oimdataproviders.impl.OIMDataProvider] [SRC_METHOD: getSearchFilter] SQL Query : UGP.ugp_key = ?
[2020-02-20T15:01:45.849-06:00] [oim_server1] [TRACE] [] [oracle.iam.oimdataproviders.impl] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0] [SRC_CLASS: oracle.iam.oimdataproviders.impl.OIMDataProvider] [SRC_METHOD: search] oracle.iam.oimdataproviders.impl.OIMRoleDataProvider
[2020-02-20T15:01:45.850-06:00] [oim_server1] [NOTIFICATION] [] [oracle.iam.identity.rolemgmt.utils] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0] Searching for Role with the specified criteria.
[2020-02-20T15:01:45.851-06:00] [oim_server1] [TRACE:32] [] [oracle.iam.oimdataproviders.impl] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0] [SRC_CLASS: oracle.iam.oimdataproviders.impl.OIMDataProvider] [SRC_METHOD: getSearchFilter] Search Criteria : arg1 = (ugp_key) EQUAL arg2 = ((null))
[2020-02-20T15:01:45.851-06:00] [oim_server1] [TRACE:32] [] [oracle.iam.oimdataproviders.impl] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0] [SRC_CLASS: oracle.iam.oimdataproviders.impl.OIMDataProvider] [SRC_METHOD: getSearchFilter] SQL Query : UGP.ugp_key = ?
[2020-02-20T15:01:45.852-06:00] [oim_server1] [TRACE] [] [oracle.iam.oimdataproviders.impl] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0] [SRC_CLASS: oracle.iam.oimdataproviders.impl.OIMDataProvider] [SRC_METHOD: search] oracle.iam.oimdataproviders.impl.OIMRoleDataProvider
[2020-02-20T15:01:45.853-06:00] [oim_server1] [ERROR] [] [oracle.iam.oimdataproviders.impl] [tid: OIMQuartzScheduler_Worker-84] [userId: oiminternal] [ecid: <ECID>] [APP: oim#11.1.2.0.0] ORA-01722: invalid number[[
 java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1115)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1315)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1441)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3823)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1671)
    at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:135)
    at oracle.iam.oimdataproviders.impl.OIMDataProvider.search(OIMDataProvider.java:1315)
    at oracle.iam.oimdataproviders.impl.OIMDataProvider.search(OIMDataProvider.java:1129)
    at oracle.iam.oimdataproviders.impl.OIMRoleDataProvider.search(OIMRoleDataProvider.java:940)
    at oracle.iam.platform.entitymgr.impl.EntityManagerImpl.findEntities(EntityManagerImpl.java:921)
..................
    at oracle.iam.identity.rolemgmt.impl.RoleManagerSearch.searchEntity(RoleManagerSearch.java:275)
    at oracle.iam.identity.rolemgmt.impl.RoleManagerSearch.search(RoleManagerSearch.java:198)
..................
    at oracle.iam.catalog.util.Utility.getRoleDisplaName(Utility.java:253)
    at oracle.iam.catalog.util.Utility.buildCatalogfromResultSet(Utility.java:234)
..................
    at oracle.iam.certification.impl.IDCCatalogUtil.findCatalogs(IDCCatalogUtil.java:82)
    at oracle.iam.certification.impl.IDCCatalogUtil.getCertifiableAppInstanceIds(IDCCatalogUtil.java:131)
    at oracle.iam.certification.scheduledtasks.CertificationCreationTask.getAllApplicationInstanceIds(CertificationCreationTask.java:946)
    at oracle.iam.certification.scheduledtasks.CertificationCreationTask.createUserCertifications(CertificationCreationTask.java:335)

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.