Customizing NLS_DATE_FORMAT needed due to legacy stored procedures

[f3ndot]

Steps to reproduce

We tried to upgrade our Rails app from 5.x to 6.x (which in turn upgraded oracle-enhanced from 5.x to 6.x), but noticed we lost the ability to set the ENV['NLS_DATE_FORMAT'] = 'DD-MON-RR' which, due to our legacy systems we're connecting to, must have all connections set as such.

We have stored procedures that our Rails app calls, which make assumptions about the date format being the non-standard DD-MON-RR. If the OCI8 connection has any different NLS_DATE_FORMAT set, such as the fixed one defined in ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter::FIXED_NLS_PARAMETERS of YYYY-MM-DD HH24:MI:SS then invoking those stored procedures will fail.

To support our custom NLS_DATE_FORMAT, we were able to make further customizations in our Rails initializers to have ActiveRecord play ball, serializing/deserializing properly. This included defining ActiveRecord::ConnectionAdapters::OracleEnhanced::Quoting#quoted_date to serialize to our NLS format.

I see https://github.com/rsim/oracle-enhanced/issues/1717 and https://github.com/rsim/oracle-enhanced/pull/1685 exist but I ask you reconsider, letting those who truly need to set the parameter to be able to do so. Maybe emit a warning it's unsupported and further customization is likely required as a result.

Expected behavior

Being able to control the Oracle session's nls_date_format with ENV['NLS_DATE_FORMAT']

Actual behavior

The setting is ignored

System configuration

Rails version: 6.0.3.3

Oracle enhanced adapter version: 6.0.4

Ruby version: 2.6.6

Oracle Database version: 12.1.0.2.0

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[yahonda]

Thanks for opening an issue. I understand that changes made at #1717 conflict with your environment. I still prefer not to change NLS_DATE_FORMAT at Oracle enhanced adapter.

What I can tell now is using logon trigger to execute alter session set nls_date_format command like https://stackoverflow.com/questions/1419966/set-up-default-alter-session-for-an-oracle-user http://www.dba-oracle.com/oracle_tips_ault_custom_parameters_users.htm