JPA: Example executing a stored procedure on Oracle

This is pretty straightforward as described here.

My Mac Software

Purpose	Mac	Windows
Text Editor	TextMate	Notepad++
SVN Client	Versions	Tortoise SVN
Command Line XSL	xsltproc	msxsl

Passenger & Oracle

Not sure what happened in production today, oracle_enhanced_driver stopped working, and this was required to make it work again.

Date, Time, Zone

Ruby

Date.today - returns a date object set to the system clock date (ignores timezone).
Time.now - returns the current time from the system's clock including the timezone.
Time.now.utc - returns the current time converted to UTC.

Ruby on Rails

Date.today -- extends ruby's Date.today to Date.today.to_s
Time.zone -- set or view the timezone set, default is utc, unless specific in application.rb
Time.zone.now - returns the current time converted to whatever is set in Time.zone.
Time.zone.now.utc - returns the current time converted to UTC (via Time.zone)

application.rb

config.time_zone : the timezone in which rails should return datetime values
config.active_record.default_timezone : default is :utc, which converts datetimes to UTC before storing, if set to :local, DateTime values will go in without conversion.

Oracle Functions

SYSTIMESTAMP - the time in the timezone of the machine on which the database is running
SESSIONTIMEZONE - the timezone of the session
LOCALTIMESTAMP - the time in the timezone of the session (this defaults to the machine in which the client is running unless explicitly set by ALTER SESSION SET TIMEZONE = '')
CURRENT_TIMESTAMP - the same as LOCALTIMESTAMP, but also returns the TIMEZONE

Oracle DataType

DATE: Stores date and datetime, loses timezone information.
TIMESTAMP: Stores datetime with a higher precision, loses timezone information.
TIMESTAMP WITH TIME ZONE: Stores the datetime and preserves the timezone information, as well as the TZ of the session that created the data. This is the most complete representation.
TIMESTAMP WITH LOCAL TIME ZONE: Stores the datetime and preserves the timezone information, but does not preserve the time zone value as it internally converts the value to UTC. This is enough for relative comparison of values, but not sufficient where actual timezone values are important - see choosing a timestamp


The oracle-enhanced adaptorsupports TIMESTAMP but not TIMEZONE, it fires a TO_TIMESTAMP when manipulating data, which means that the TZ information is taken from the session and cannot be supplied. This has the consequence that there is no point in using TIMESTAMP WITH TIME ZONE columns as all values will be of the same TZ. What we are left is to use TIMESTAMP WITH LOCAL TIME ZONE columns.

---

My configuration.

1. Set the config.time_zone to the default timezone - 'Mumbai'
2. Dont use Date.today, this will give the server's time, instead always use Time.zone.today.
3. Timestamp columns should be created as TIMESTAMP WITH LOCAL TIME ZONE and not TIMESTAMP or DATE - this is really useful for us since we have to pull data into other Oracle database's that are working with IST as the default timezone.
4. Set the ENV['TZ'] in oracle.rb to UTC
5. Ensure config.active_record.default_timezone is kept to UTC (in sync with ENV['TZ'])

months_since, next_month

Faced a wierd problem today, when running in production, ruby next_month and the >> operator failed to work, while the rails method months_since ran.

Both worked on the my local dev environment!