<p>Oracle database software’s near-zero cost in academia result in ubiquitous usage. Pairing Rails with Oracle isn’t presently void of issues, but there are no real show stoppers. Here are a few things I’ve ran into and how I worked around them.<br />Oracle’s Missing Time Field<br />Oracle doesn’t have a time field like other popular databases. You may very well define a :time type in an ActiveRecord::Migration, but Oracle will give you a date field instead. The date field will hold a time value most of the time, but not always.<br />A sort of “gotcha” occurs when you get a record with the time portion set to midnight. In this special case Oracle doesn’t return any time value at all in this special case, instead it just returns the date part of the record. On the Ruby/Rails end you get back a Date class instead of a Time class. This means your time field will not have min or hour methods. The solution is coercion, using to_time:<br />foo = Foo.find( :first )<br />foo.my_time_field = foo.my_time_field.to_time<br />After that your date field will contain a time that’s set to midnight (luckily this matches our special case from above exactly). Another workaround is to rescue the exception with a zero value:<br />foo.my_time_field.hour rescue 0<br />foo.my_time_field.min rescue 0<br />Keep session data stored in Oracle simple<br />The Oracle-Ruby database driver has some issues with storing complex data in a session. I had a scenario where I needed to store objects in a session temporarily while waiting to put their parent data in the database first. Something like this would cause the issue:<br />foo = Foo.new( params[:foo] )<br />session[:foos] << foo<br />I suspect the cause is the fact that an ActiveRecord object owns a database handle, and when you accumulate several of them, threading issues arise. I will admit I did not pursue it further once I found a workaround.<br />The solution was to not store the complex data in the session at all, but instead only store a session variable pointing to the data:<br />foo = Foo.create( params[:foo] )<br />session[:foos] << foo.id<br />This solution works fine but has the side effect of allowing orphaned data to appear in the database if a session to browser connection is ever lost.<br />I will also mention this issue never bubbled up until my app was deployed to a 64-bit machine. In a 32-bit environment it seems I can store all the ActiveRecord objects in a session that I want to.<br />Learn to purge Oracle’s recycle bin<br />Rails migrations allow the destroying and rebuilding of a project database on a whim.<br />rake db:migrate VERSION=0; rake db:migrate<br />A “gotcha” occurs after running these commands several dozen times or more. Newer versions of Oracle do not actually drop constraints. Instead Oracle saves them to the Oracle recycle bin. Over time they build up and cause otherwise speedy migrations to take longer and longer to run.<br />The solution is to dump Oracle’s recycle bin periodically. Add this to the end (and beginning too if you want) of your ActiveRecord migrations:<br />execute ‘purge recyclebin’<br />Oracle’s Sequence Cache<br />By default Oracle caches requests for new sequences in blocks of 20. This might become a problem if you need numbers without gaps as you will get 1, 2, 3... and then later 23, 24, 25... To fix it I just added this to the appropriate ActiveRecord::Migration:<br />execute ‘ALTER SEQUENCE FOO_SEQ NOCACHE’</p>
2009-08-30T03:28:38Z
http://railsmagazine.com/forums/2/topics/73
44
4
8
Oracle Tips and Tricks
2009-12-24T20:53:26Z