Neo.ClientError.Procedure.ProcedureCallFailed: Failed to invoke procedure `apoc.load.jdbc`: Caused by: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1 ORA-01882: timezone region not found

[yeshveer_yadav]

version : neo4j-community-3.5.11

debug log

Error:
ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found
ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found

java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:456)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:451)
at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:1040)
at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:552)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:501)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:1292)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:1025)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:743)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:793)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:57)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:747)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:562)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:270)
at apoc.load.util.JdbcUtil.getConnection(JdbcUtil.java:34)
at apoc.load.Jdbc.executeQuery(Jdbc.java:76)
at apoc.load.Jdbc.jdbc(Jdbc.java:61)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.neo4j.kernel.impl.proc.ReflectiveProcedureCompiler$ReflectiveProcedure.apply(ReflectiveProcedureCompiler.java:664)
at org.neo4j.kernel.impl.proc.ProcedureRegistry.callProcedure(ProcedureRegistry.java:219)
at org.neo4j.kernel.impl.proc.Procedures.callProcedure(Procedures.java:290)
at org.neo4j.kernel.impl.newapi.AllStoreHolder.callProcedure(AllStoreHolder.java:1098)
at org.neo4j.kernel.impl.newapi.AllStoreHolder.procedureCallRead(AllStoreHolder.java:863)
at org.neo4j.cypher.internal.runtime.interpreted.CallSupport$$anonfun$2.apply(CallSupport.scala:62)
at org.neo4j.cypher.internal.runtime.interpreted.CallSupport$$anonfun$2.apply(CallSupport.scala:62)
at org.neo4j.cypher.internal.runtime.interpreted.CallSupport$.callProcedure(CallSupport.scala:167)
at org.neo4j.cypher.internal.runtime.interpreted.CallSupport$.callReadOnlyProcedure(CallSupport.scala:64)
at org.neo4j.cypher.internal.runtime.interpreted.TransactionBoundQueryContext.callReadOnlyProcedure(TransactionBoundQueryContext.scala:974)
at org.neo4j.cypher.internal.compatibility.v3_5.ExceptionTranslatingQueryContext$$anonfun$callReadOnlyProcedure$1.apply(ExceptionTranslatingQueryContext.scala:166)
at org.neo4j.cypher.internal.compatibility.v3_5.ExceptionTranslatingQueryContext$$anonfun$callReadOnlyProcedure$1.apply(ExceptionTranslatingQueryContext.scala:166)
at org.neo4j.cypher.internal.compatibility.v3_5.ExceptionTranslationSupport$class.translateException(ExceptionTranslationSupport.scala:33)
at org.neo4j.cypher.internal.compatibility.v3_5.ExceptionTranslatingQueryContext.translateException(ExceptionTranslatingQueryContext.scala:42)
at org.neo4j.cypher.internal.compatibility.v3_5.ExceptionTranslationSupport$class.translateIterator(ExceptionTranslationSupport.scala:47)
at org.neo4j.cypher.internal.compatibility.v3_5.ExceptionTranslatingQueryContext.translateIterator(ExceptionTranslatingQueryContext.scala:42)
at org.neo4j.cypher.internal.compatibility.v3_5.ExceptionTranslatingQueryContext.callReadOnlyProcedure(ExceptionTranslatingQueryContext.scala:166)
at org.neo4j.cypher.internal.runtime.LazyReadOnlyCallMode.callProcedure(ProcedureCallMode.scala:48)
at org.neo4j.cypher.internal.runtime.interpreted.pipes.ProcedureCallPipe.org$neo4j$cypher$internal$runtime$interpreted$pipes$ProcedureCallPipe$$call(ProcedureCallPipe.scala:90)
at org.neo4j.cypher.internal.runtime.interpreted.pipes.ProcedureCallPipe$$anon$$$$e6882d5096c8b8441ac36d3c0cc459e$$$$ateResultsByAppending$1.apply(ProcedureCallPipe.scala:73)
at org.neo4j.cypher.internal.runtime.interpreted.pipes.ProcedureCallPipe$$anon$$$$e6882d5096c8b8441ac36d3c0cc459e$$$$ateResultsByAppending$1.apply(ProcedureCallPipe.scala:71)
at scala.collection.Iterator$$anon$12.nextCur(Iterator.scala:435)
at scala.collection.Iterator$$anon$12.hasNext(Iterator.scala:441)
at scala.collection.Iterator$$anon$11.hasNext(Iterator.scala:409)
at scala.collection.Iterator$$anon$11.hasNext(Iterator.scala:409)
at scala.collection.Iterator$$anon$11.hasNext(Iterator.scala:409)
at org.neo4j.cypher.internal.runtime.RuntimeJavaValueConverter$feedIteratorToVisitable.accept(RuntimeJavaValueConverter.scala:53)
at org.neo4j.cypher.internal.compatibility.v3_5.runtime.PipeExecutionResult.accept(PipeExecutionResult.scala:76)
at org.neo4j.cypher.internal.compatibility.v3_5.runtime.executionplan.StandardInternalExecutionResult.accept(StandardInternalExecutionResult.scala:192)
at org.neo4j.cypher.internal.compatibility.ClosingExecutionResult$$anonfun$accept$2.apply$mcV$sp(ClosingExecutionResult.scala:158)
at org.neo4j.cypher.internal.compatibility.ClosingExecutionResult$$anonfun$accept$2.apply(ClosingExecutionResult.scala:158)
at org.neo4j.cypher.internal.compatibility.ClosingExecutionResult$$anonfun$accept$2.apply(ClosingExecutionResult.scala:158)
at org.neo4j.cypher.internal.compatibility.ClosingExecutionResult$$anonfun$safelyAndClose$1.apply(ClosingExecutionResult.scala:171)
at org.neo4j.cypher.exceptionHandler$runSafely$.apply(exceptionHandler.scala:89)
at org.neo4j.cypher.internal.compatibility.ClosingExecutionResult.safelyAndClose(ClosingExecutionResult.scala:174)
at org.neo4j.cypher.internal.compatibility.ClosingExecutionResult.accept(ClosingExecutionResult.scala:157)
at org.neo4j.bolt.v1.runtime.CypherAdapterStream.accept(CypherAdapterStream.java:73)
at org.neo4j.bolt.v1.messaging.ResultHandler.onRecords(ResultHandler.java:40)
at org.neo4j.bolt.v3.runtime.StreamingState.lambda$processStreamResultMessage$0(StreamingState.java:41)
at org.neo4j.bolt.v1.runtime.TransactionStateMachine$State.consumeResult(TransactionStateMachine.java:484)
at org.neo4j.bolt.v1.runtime.TransactionStateMachine$State$1.streamResult(TransactionStateMachine.java:328)
at org.neo4j.bolt.v1.runtime.TransactionStateMachine.streamResult(TransactionStateMachine.java:128)
at org.neo4j.bolt.v3.runtime.StreamingState.processStreamResultMessage(StreamingState.java:40)
at org.neo4j.bolt.v3.runtime.AbstractStreamingState.processUnsafe(AbstractStreamingState.java:44)
at org.neo4j.bolt.v3.runtime.FailSafeBoltStateMachineState.process(FailSafeBoltStateMachineState.java:48)
at org.neo4j.bolt.v1.runtime.BoltStateMachineV1.nextState(BoltStateMachineV1.java:144)
at org.neo4j.bolt.v1.runtime.BoltStateMachineV1.process(BoltStateMachineV1.java:92)
at org.neo4j.bolt.messaging.BoltRequestMessageReader.lambda$doRead$1(BoltRequestMessageReader.java:89)
at org.neo4j.bolt.runtime.DefaultBoltConnection.processNextBatch(DefaultBoltConnection.java:191)
at org.neo4j.bolt.runtime.DefaultBoltConnection.processNextBatch(DefaultBoltConnection.java:139)
at org.neo4j.bolt.runtime.ExecutorBoltScheduler.executeBatch(ExecutorBoltScheduler.java:171)
at org.neo4j.bolt.runtime.ExecutorBoltScheduler.lambda$scheduleBatchOrHandleError$2(ExecutorBoltScheduler.java:154)
at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1604)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)

[dana_canzano]

as this is a native Oracle error presumably the failure is with the SQL statement sent to Oracle. If you run the SQL statement natively in Oracle do you encounter the same failure? If you do not then maybe this is an issue with the Oracle JDBC driver you are using. What version of the Oracle JDBC driver are you using?

[yeshveer_yadav]

@dana.canzano this same query is running on Neo4j Server version: [3.4.9] with
(apoc-3.4.0.3-all.jar, ojdbc8.jar)

but this same query is giving above error on Neo4j Server version: [3.5.11]
with procedures (apoc-3.5.0.5-all.jar,ojdbc8.jar)

the sql query is running fine on oracle.

[dana_canzano]

thank you for this detail. having this detail might help narrow down the issue. Are you able to share the SQL statement too?
Also, what version of Oracle are you connecting to?

[yeshveer_yadav]

The query is:
call apoc.load.jdbc("jdbc:oracle:thin:abc/efghi@something:PORT/SERVICE","SELECT USER_ID, 1 FLAG FROM USER WHERE rownum<5") yield row RETURN row

and the version of oracle is 11g
is this sufficient?

[yeshveer_yadav]

@michael.hunger @dana.canzano
please suggest any solution

[paulare]

Has anyone found the solution for this?

[yeshveer_yadav]

A different version of the oracle jar worked for me, I guess that was the lower version.

[paulare]

conker84:

The user said that upgrading the oracle java driver fixed the issue.

Thank you for the clues. In my case trying different versions of the java driver didn't work.
The root issue was that the timezone setting is inherited from the operating system environment.
In my case, the root issue was that the docker container running in the UTC timezone.

I changed the timezone in the docker container for my local time & restarted the container

cp /usr/share/zoneinfo/Europe/Tallinn /etc/localtime
echo "Europe/Tallinn" >  /etc/timezone

Now the oracle connection works!

[conker84]

As explained by @dana.canzano it's a native Oracle error, googling out the web I found this:

stackoverflow.com

ORA-01882: timezone region not found

oracle, jdbc, timezone

asked by ndalama on 06:30AM - 06 Feb 12 UTC

The user said that upgrading the oracle java driver fixed the issue.