Issue using time_zone in database yml connecting to aws rds oracle databases

[grantlees]

Steps to reproduce

use time_zone: "Pacific/Auckland" instead of time_zone: '+13:00' within database.yml file

Expected behavior

ActiveRecord::Base.connection.select_one("select sysdate from dual");
should return a result, the same error below even fails with User.all.first

I am going to create a sample app but as the issue is aws specific I wonder if anyone has ever come across the same sort of issue. I have checked the time_zone files, and though they are different in aws the behaviour works with the offset but not the zone name...

Has anyone had any success using time_come: "region name" in database.yml connecting to an aws oracle rds database?

Ive had a trace done and the data returns from the database but is failing in ruby-oci, however as it shows different behaviour with the adapter config I thought I'd ask here.

Thanks,
Grant

Actual behavior

Can not use the time_zone: xxx config that uses "Pacific/Auckland" when connecting to an aws oracle rds database

F, [2023-01-17T16:45:44.488839 #92] FATAL -- :
ActiveRecord::StatementInvalid (OCIError: ORA-01805: possible error in date/time operation):

ocidatetime.c:119:in oci8lib_270.so
ruby-oci8 (2.2.11) lib/oci8/datetime.rb:393:in get' ruby-oci8 (2.2.11) lib/oci8/cursor.rb:576:in get_data'
ruby-oci8 (2.2.11) lib/oci8/cursor.rb:576:in block in fetch_one_row_as_array' ruby-oci8 (2.2.11) lib/oci8/cursor.rb:575:in collect'
ruby-oci8 (2.2.11) lib/oci8/cursor.rb:575:in fetch_one_row_as_array' ruby-oci8 (2.2.11) lib/oci8/cursor.rb:162:in fetch'
activerecord-oracle_enhanced-adapter (6.1.6) lib/active_record/connection_adapters/oracle_enhanced/

System configuration

ruby '2.7.6'
gem 'rails', '6.1.4.1'

gem 'activerecord-oracle_enhanced-adapter', '~> 6.1.0'
gem 'ruby-oci8'
gem 'ruby-plsql'
gem 'composite_primary_keys'

Oracle 19

--database.yml

development:
adapter: oracle_enhanced
database: name
username: user
password: pwd

time_zone: '+13:00' <--works aws

time_zone: "Pacific/Auckland" <-- doesnt work aws, but does with on premise

[grantlees]

Im going to close this issue here, as I had some results after reading this article https://stackoverflow.com/questions/69381749/where-is-the-oracle-instant-client-timezone-file-located and after persevering with this solution after being told many times it would not make a difference....it does....
It goes a long way that I was able to use the adapter config half way there...but yeah...following the steps in the article fixed the issue for me. Cheers

[grantlees]

feel free to close when you see fit

[yahonda]

Thanks for the report. ORA-01805 error should be fixed at Oracle client layer. So closing this issue.