Solutions

LiveSync fails with ORA-01843 or ORA-01861 error when using the Database Table Connector in IDM/OpenIDM (All versions)

Last updated Jul 9, 2018

The purpose of this article is to provide assistance if you see one of the following errors when LiveSync fails in IDM/OpenIDM: "ORA-01843: not a valid month" or "ORA-01861: literal does not match format string". These errors occur when using the Database Table Connector to connect to a table on an Oracle® database.


Symptoms

You will see one of the following errors when LiveSync fails depending on the data type as explained in the Causes section:

2017-01-22T12:09:42.062+0000 WARNING org.forgerock.openidm.quartz.impl.SchedulerServiceJob execute org.forgerock.openidm.quartz.impl.SchedulerServiceJob: Scheduled service "scheduler-service-group.sync_systemSunidmAccount_managedUser" invocation reported failure: org.forgerock.json.resource.InternalServerErrorException: Failed to get OperationOptionsBuilder: Can not read from the table or view 'sunidm_openidm.account'.^_ 
org.forgerock.openidm.quartz.impl.ExecutionException: org.forgerock.json.resource.InternalServerErrorException: Failed to get OperationOptionsBuilder: Can not read from the table or view 'sunidm_openidm.account'. 
   at org.forgerock.openidm.provisioner.impl.SystemObjectSetService.execute(SystemObjectSetService.java:412) 
   at org.forgerock.openidm.quartz.impl.SchedulerServiceJob.execute(SchedulerServiceJob.java:133) 
   at org.quartz.core.JobRunShell.run(JobRunShell.java:223) 
   at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549) 
Caused by: org.forgerock.json.resource.InternalServerErrorException: Failed to get OperationOptionsBuilder: Can not read from the table or view 'sunidm_openidm.account'. 
   at org.forgerock.openidm.provisioner.openicf.impl.OpenICFProvisionerService.liveSynchronize(OpenICFProvisionerService.java:2166) 
   at org.forgerock.openidm.provisioner.impl.SystemObjectSetService.liveSync(SystemObjectSetService.java:449) 
   at org.forgerock.openidm.provisioner.impl.SystemObjectSetService.execute(SystemObjectSetService.java:407) 
... 3 more 
Caused by: org.identityconnectors.framework.common.exceptions.ConnectorException: Can not read from the table or view 'sunidm_openidm.account'. 
   at org.identityconnectors.databasetable.DatabaseTableConnector.sync(DatabaseTableConnector.java:644) 
   at org.identityconnectors.framework.impl.api.local.operations.SyncImpl.sync(SyncImpl.java:73) 
   at sun.reflect.GeneratedMethodAccessor86.invoke(Unknown Source) 
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
   at java.lang.reflect.Method.invoke(Method.java:498) 
   at org.identityconnectors.framework.impl.api.local.operations.ConnectorAPIOperationRunnerProxy.invoke(ConnectorAPIOperationRunnerProxy.java:104) 
   at com.sun.proxy.$Proxy31.sync(Unknown Source) 
   at sun.reflect.GeneratedMethodAccessor86.invoke(Unknown Source) 
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
   at java.lang.reflect.Method.invoke(Method.java:498) 
   at org.identityconnectors.framework.impl.api.local.operations.ThreadClassLoaderManagerProxy.invoke(ThreadClassLoaderManagerProxy.java:96) 
   at com.sun.proxy.$Proxy31.sync(Unknown Source) 
   at sun.reflect.GeneratedMethodAccessor86.invoke(Unknown Source) 
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
   at java.lang.reflect.Method.invoke(Method.java:498) 
   at org.identityconnectors.framework.impl.api.BufferedResultsProxy$BufferedResultsHandler.run(BufferedResultsProxy.java:157) 
Caused by: java.sql.SQLDataException: ORA-01843: not a valid month
Mar 03, 2017 2:50:16 PM org.forgerock.openidm.servlet.internal.ServletConnectionFactory$4 handleException
WARNING: Resource exception: 500 Internal Server Error: "Failed to get OperationOptionsBuilder: Can not read from the table or view 'DB_TABLE'."
org.forgerock.json.resource.InternalServerErrorException: Failed to get OperationOptionsBuilder: Can not read from the table or view 'DB_TABLE'.
   at org.forgerock.openidm.provisioner.impl.SystemObjectSetService.actionInstance(SystemObjectSetService.java:318)
   at org.forgerock.json.resource.InterfaceSingletonHandler.handleAction(InterfaceSingletonHandler.java:34)
...
Caused by: org.forgerock.json.resource.InternalServerErrorException: Failed to get OperationOptionsBuilder: Can not read from the table or view 'DB_TABLE'.
   at org.forgerock.openidm.provisioner.openicf.impl.OpenICFProvisionerService.liveSynchronize(OpenICFProvisionerService.java:2330)
   at org.forgerock.openidm.provisioner.impl.SystemObjectSetService.liveSync(SystemObjectSetService.java:448)
   at org.forgerock.openidm.provisioner.impl.SystemObjectSetService.actionInstance(SystemObjectSetService.java:300)
	... 130 more
Caused by: org.identityconnectors.framework.common.exceptions.ConnectorException: Can not read from the table or view 'DB_TABLE'.
   at org.identityconnectors.databasetable.DatabaseTableConnector.sync(DatabaseTableConnector.java:628)
   at org.identityconnectors.framework.impl.api.local.operations.SyncImpl.sync(SyncImpl.java:73)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:606)
   at org.identityconnectors.framework.impl.api.local.operations.ConnectorAPIOperationRunnerProxy.invoke(ConnectorAPIOperationRunnerProxy.java:104)
   at com.sun.proxy.$Proxy26.sync(Unknown Source)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:606)
   at org.identityconnectors.framework.impl.api.local.operations.ThreadClassLoaderManagerProxy.invoke(ThreadClassLoaderManagerProxy.java:96)
   at com.sun.proxy.$Proxy26.sync(Unknown Source)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:606)
   at org.identityconnectors.framework.impl.api.BufferedResultsProxy$BufferedResultsHandler.run(BufferedResultsProxy.java:157)
Caused by: java.sql.SQLDataException: ORA-01861: literal does not match format string

Recent Changes

Implemented the Database Table Connector.

Causes

The Database Table Connector cannot handle Oracle's TIMESTAMP or DATE data type. This issue will affect you when the field specified as the changeLogColumn in your provisioner configuration is of type TIMESTAMP or DATE.

The error you see is dependent on your data type as follows:

  • TIMESTAMP:
    ORA-01843: not a valid month
  • DATE:
    ORA-01861: literal does not match format string

Solution

This issue can be resolved as follows:

  1. Update your provisioner configuration file (for example, provisioner.openicf-contractordb.json in the /path/to/idm/conf directory) to ensure the allNative and nativeTimestamps configuration properties are set to true as follows:
         "allNative" : "true",
         "changeLogColumn" : "LAST_MODIFIED",
         "nativeTimestamps" : "true",
    
    By default, allNative is set to false and changeLogColumn is typically set to LAST_MODIFIED, which has a data type of TIMESTAMP. You should amend changeLogColumn to match your settings if you use a different field.
  2. Add the following JVM option to the Java execution line in the startup.sh or startup.bat file:
    -Doracle.jdbc.J2EE13Compliant=true

See Also

Connectors Reference › Database Table Connector

Related Training

N/A

Related Issue Tracker IDs

OPENICF-81 (DatabaseTableConnector can't handle timestamp data type from Oracle)

OPENIDM-2265 (Got "ORA-01843: not a valid month" while trying to liveSync from Oracle database)



Copyright and TrademarksCopyright © 2018 ForgeRock, all rights reserved.
Loading...