search

kubo / ruby-oci8

Ruby-oci8 - Oracle interface for ruby
Other
169 stars 78 forks source link

OCIError: ORA-21500 when connecting to Oracle Database 18c Enterprise Edition #249

Closed dan-corneanu closed 1 year ago

dan-corneanu commented 1 year ago

Hello, I have a Rails app that gets deployed as an Azure App Service and needs to connect to an Oracle Database 18c Enterprise Edition Release 18.0.0.0.0. I am able to ssh into the running docker image and connect to the database with sqlplus. However, when I try to establish a connection from rails console, it fails on me with OCIError (ORA-21500: internal error code, arguments: [kpubalts10], [30073], [0], [0], [0], [0], [0], [0]).

I've spent an entire day trying to figure out what might be wrong, but to no avail. I am hoping that someone here can give me advice on what else I could try.

Here is my setup

Connecting with sqlplus is successful.

root@9fc6d76b95e4:/app# /opt/oracle/instantclient_21_7/sqlplus PHAT_NONPRD/******@10.6.x.x:1521/WHSL_UAT

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Oct 14 21:30:32 2022
Version 21.7.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Fri Oct 14 2022 21:29:31 +00:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.10.0.0.0

SQL> select * from dual;

D
-
X

SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.10.0.0.0

Trying to connect from rails console fails.

irb(main):001:0> OCI8.new('PHAT_NONPRD','*****','10.6.x.x:1521/WHSL_UAT')
Traceback (most recent call last):
        2: from (irb):1
        1: from (irb):1:in `new'
OCIError (ORA-21500: internal error code, arguments: [kpubalts10], [30073], [0], [0], [0], [0], [0], [0])

Regards, Dan

dan-corneanu commented 1 year ago

After a couple of days of frustrations, I have finally managed to find out what the problem is. IT turns out that the TZ environment variable was set to an incorrect value of New Zealand Standard Time. Once I have changed it to Pacific/Auckland the problem went away.

I've found the fix by pure luck. It would have been nice to have Oracle return more details in the OCIError.

I guess the lesson is to double and triple check your environment variables when you are dealing with generic OCIErrors.

kubo commented 1 year ago

Glad it's resolved. I was concerned about it but had no ideas.