I've got a legacy application with no time zone support. This application is supporting users in a time zone that is different from the databases default time zone. I can not change the default the database time zone because other application share this database. It's been suggested that I create a logon trigger for the application's schema that would set the sessions time zone to my users time zone. The problem with this strategy is that the application uses SYSDATE instead of CURRENT_DATE which results in the databases not sessions time zone being used. Without changing the code I need to get the date based on sessions default time zone.
I've tried (unsuccessfully) to redirect the calls to SYSDATE using a synonym and a function but strategies have failed. Any ideas on other strategies?
This cannot be done without application changes or moving your application into a separate database. As SYSDATE is a reserved word, you would need to change all references to quoted identifier "SYSDATE" to be able to use a user-defined function "SYSDATE".
You could theoretically try to change SYSDATE just for your application by connecting in the dedicated server mode through a separate listener started with another TZ variable, but such mixing of time zones in one instance is not recommended. I am not even sure if it is supported at all. It could pose problems in some situations, generate non-continuous timestamps in alert.log, confuse time based point-in-time recovery, etc.