In all Ruby on Rails on Oracle projects where I am using original ActiveRecord Oracle adapter I always create some “monkey patches” of Oracle adapter to support my needs. I have written about several of these patches in this blog (see 1, 2, 3).
As such monkey patches are not easily maintainable and reusable between projects I decided to fork existing Oracle adapter and create my own ActiveRecord Oracle “enhanced” adapter where I will add all my enhancements.
Today I released first version (1.1.0) of Oracle enhanced adapter on RubyForge as well as source code is available at GitHub.
To install Oracle enhanced adapter execute (should be available soon):
sudo gem install activerecord-oracle_enhanced-adapter
To use it you need to use “oracle_enhanced” as the adapter name in database.yml configuration file instead of “oracle”. In addition I recommend to create config/initializers/oracle_enhanced.rb file where to put any adapter configuration options that you can see below.
Initial version contains the following enhancements:
- Improved perfomance of schema dump methods when used on large data dictionaries
- Added LOB writing callback for sessions stored in database (see previous post)
- Added emulate_dates_by_column_name option
Set the option below and as a result columns with DATE in their name will be emulated as Date (and not as Time which is default for DATE columns in database)
ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_dates_by_column_name = true
Set the option below and as a result number columns with ID at the end of column always will be emulated as Fixnum (useful if in legacy database column type is specified just as NUMBER without precision information which by default is mapped to BigDecimal Ruby type)
ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_integers_by_column_name = true
Set the option below and as a result CHAR(1), VARCHAR2(1) columns or VARCHAR2 columns with FLAG or YN at the end of their name will be emulated as booleans (and “Y” and “N” will be used to store true and false values). This is useful for legacy databases where Rails default convention of NUMBER(1) for boolean values cannot be used (e.g. if you are using Oracle E-Business Suite where booleans are stored as “Y” and “N”).
ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_booleans_from_strings = true
If you need to override how date, integer or boolean columns are identified then you can redefine class methods is_date_column?, is_integer_column? and is_boolean_column? definitions in OracleEnhancedAdapter class.
Please comment if you find these enhancements useful in your projects and also what other Oracle adapter enhancements you need in your Ruby on Rails on Oracle projects.
great job :)
Comment by jessehu — May 13, 2008 @ 4:24 am |
Great job!
Has this been checked with both ruby_oci8-1.0.1 and ruby-oci8-2.0-unstable?
Comment by Matt Larson — May 13, 2008 @ 5:54 pm |
Currently I am testing it just with ruby-oci8 1.0. But as far as I have looked at ruby-oci8 2.0 it should be backwards compatible with version 1.0 and therefore should not break anything. But sometime later I plan to set up experimental environment where to test ruby-oci8 2.0 and then I will be able to tell for sure if it works or not.
Comment by Raimonds Simanovskis — May 13, 2008 @ 6:01 pm |
Hi, I’m trying to use oracle_enhanced adapter but I’m having problems doing my first test. Here is the example:
require ‘rubygems’
require ‘active_record’
ActiveRecord::Base.establish_connection(
:adapter => “oracle_enhanced”,
:username => “pabloq”,
:password => “******”,
:host => “127.0.0.1/ABC”)
class Testing < ActiveRecord::Base
set_table_name “idm_test_tables”
end
temp = Testing.new
I’m received this:
env.c:257:in oci8lib.so: ORA-12560: TNS:protocol adapter error (OCIError)
from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:229:in `initialize’
from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.1.0/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:692:in `new’
from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.1.0/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:692:in `new_connection’
from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.1.0/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:728:in `initialize’
from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.1.0/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:46:in `new’
from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.1.0/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:46:in `oracle_enhanced_connection’
from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:291:in `send’
from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:291:in `connection=’
from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:259:in `retrieve_connection’
from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:78:in `connection’
from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/base.rb:1080:in `columns’
from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/base.rb:2363:in `attributes_from_column_definition_without_lock’
from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/locking/optimistic.rb:55:in `attributes_from_column_definition’
from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/base.rb:1922:in `initialize’
from test_activerecord_oracle.rb:14:in `new’
from test_activerecord_oracle.rb:14
I checked that SID ‘ABC’ is working with the sqlplus client, and it works. I don’t know if I need something more. Could you help me with this? I’m on a windows server machine. Thanks for your time!
Comment by Pablo Q. — May 13, 2008 @ 8:18 pm |
Instead of :host => “127.0.0.1/ABC” try to use :database => “ABC”
In addition check if ruby-oci8 can connect to the database:
irb
require ‘oci8′
OCI8.new(‘pabloq’, ‘****’,'ABC’).exec( ’select * from dual’ ) do |r| puts r.join( ‘,’ ) ; end
Comment by Raimonds Simanovskis — May 13, 2008 @ 9:32 pm |
Thanks for your time, I changed the code just like this and it works now!
ActiveRecord::Base.establish_connection(
:adapter => “oracle_enhanced”,
:username => “pabloq”,
:password => “*****”,
:host => “localhost”,
:database => ‘ABC’
)
Thank You!
Comment by Pablo Q. — May 13, 2008 @ 10:15 pm |
First of all, I like your enhanced Oracle adapter.
Instead of using a sequence to populate a primary key (id), I use the follwing code in a before row insert trigger:
if :new.id is null
then
select to_number(sys_guid(), ‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX’)
into :new.id
from dual;
end if;
The generated id is a very high number.
The integer convertion of your enhanced Oracle adapter gives me not the same id as stored in the database.
Please help!
Comment by Guido Zeelen — May 14, 2008 @ 11:50 am |
Does this also support LONG datatypes? We have some tables that still use LONG (although we are in the process of converting them to LOBs), and the current Oracle adapter chokes on these.
Comment by Sean Stickle — May 14, 2008 @ 12:30 pm |
@Guido: Thanks for this example – it was really interesting to investigate it :)
I did some experiments with your case and here are my findings.
By default if table or select results have NUMBER type without precision and scale information (as in your case ID column) then ruby-oci8 is mapping this to Float Ruby data type.
And this is the issue in your case that Float data type cannot store more than 16 significant digits – ruby-oci8 converts selected value to Float and then later Oracle enhanced adapter translates it to Integer and as a result last digits are different compared to database value (so you can try by yourself in irb 12345678901234567.to_f.to_i).
ruby-oci8 allows to change this default data type to which NUMBER without precision and scale is mapped (see README file in ruby-oci8 source).
If you always want that NUMBER is mapped to integer then you can put in some initialization file
OCI8::BindType::Mapping[:number_no_prec_setting] = OCI8::BindType::Integer
And then in your example you should get the same ID value as in database as no conversion to Float would be doen. But in this case the issue could be that there are other decimal fields which are defined as NUMBER without scale and also you could have some calculations in views or custom selects which will also have resulting value type NUMBER without scale – and then such values will be translated to integers and will loose decimal digits.
Therefore better option would be to use setting
OCI8::BindType::Mapping[:number_no_prec_setting] = OCI8::BindType::OraNumber
OraNumber is data type that is defined by ruby-oci8 library and which corresponds to Oracle NUMBER data type. If this setting is used together with Oracle enhanced adapter’s ID column integer translation then you will get ID column values as Fixnum or Bignum values and other values will be BiDecimal values. So far it seems to me that everything is working correctly with this setting.
You can see some examples / tests using your example in GitHub project branch – see at the end of file http://github.com/rsim/oracle-enhanced/tree/bignum_ids/spec/active_record/connection_adapters/oracle_enhanced_adapter_spec.rb
Additional comment about this trigger – as I understand this is some legacy database with existing tables? The issue is that you cannot use ActiveRecord to insert values in such table because current Oracle adapter tries to select new sequence value before inserting record. Is that OK for you or you want also to insert records using ActiveRecord?
Comment by Raimonds Simanovskis — May 14, 2008 @ 9:52 pm |
@Sean
If you already have existing data in LONG columns then you should be able to select these values using Oracle adapter and they will appear as String values in Ruby. The issue is that you will not be able to insert or update large strings (more than 4000 characters) in LONG columns using Oracle adapter – the issue is that ActiveRecord constructs all values as literals in INSERT or UPDATE statement and does not use bind variables – and Oracle has an issue that it allows maximum value of 4000 characters for string literals.
Therefore for BLOB/CLOB data types Oracle adapter has implemented workaround using save callbacks who update LOB columns after main INSERT or UPDATE statement. For LONG columns similar workaround should be implemented to fully support them – currently Oracle “enhanced” adapter does not have such support and it behaves in the same way as original Oracle adapter.
If there are also others who are interested in LONG data type support then I might consider it for implementation in later Oracle “enhanced” adapter versions :)
Comment by Raimonds Simanovskis — May 14, 2008 @ 10:32 pm |
Raimonds,
Your solution works great.
Thank you very much.
You are right that it is a legacy database with existing tables.
However, I would like to use ActiveRecord to insert records.
Is there a way to bypass the population of the primary key using a sequence?
Comment by Guido Zeelen — May 15, 2008 @ 12:31 pm |
Thanks for this enhanced adapter. I will see on incorporating it in my project. I have two questions: As part of my legacy Oracle database support, I use Dr. Nic’s composite_primary_keys gem. Have you tried this, by any chance, with your enhanced adapter?
Also, one of the more persistant problems I deal with is the fact that the legacy database is replicated, which requires most tables to have timestamp with time zone columns, which as you probably know, is very much unsupported. I’ve had to resort to views which do not include this column.
Any suggestions on how to deal with this? I once attempted a patch to try to filter out that column or type, but with no success. This type, when used for replication, I believe uses a standardized column name of “INS_UPD_TIMESTAMP”. If I can’t support it, it would be so nice to be able to throw a switch to ignore it.
Thanks for this site. It’s invaluable for rails on oracle.
Comment by Todd F — May 15, 2008 @ 3:40 pm |
@Guido
The easiest way would be to put the following in initialization file:
ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.class_eval do
def prefetch_primary_key?(table_name = nil)
false
end
end
This will tell ActiveRecord not to select next ID value from sequence before insert. The only issue is that the object that will be created from Rails will have nil value in “id” attribute – so you will need to requery it from database using other attributes as search criteria for later updates.
Do you have any suggestions how can we get the generated ID column value by trigger after INSERT statement? Then it would be possible to add functionality to Oracle adapter to set correct “id” value after creation of it in database. And then I could add some configuration option for Oracle enhanced adapter to support such autogenerated primary keys.
Comment by Raimonds Simanovskis — May 15, 2008 @ 7:32 pm |
@Guido
Actually I found a better workaround – put the following in initialization file (and do not modify prefetch_primary_key? method):
ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.class_eval do
def next_sequence_value(sequence_name)
id = 0
@connection.exec(“SELECT to_number(sys_guid(), ‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX’ ) FROM dual” ) { |r| id = r[0].to_i }
id
end
end
In this way you can override, how to get new ID values for primary key – in your example your trigger will not override primary key value if we already pass it in INSERT statement. And in this way your newly created object will have “id” attribute filled with correct new primary key value.
Comment by Raimonds Simanovskis — May 15, 2008 @ 7:55 pm |
@Todd
We are using composite_primary_keys gem in one project together with original Oracle adapter. I have not yet tested but I think that it should also work with my enhanced adapter.
I am not very familiar with how Oracle database replication works – will need to consult with our DBAs :)
But anyway I will try to include in future versions of Oracle adapter some option how to exclude some database table values from list of ActiveRecord object’s attributes.
Comment by Raimonds Simanovskis — May 15, 2008 @ 7:58 pm |
Thanks again for your excellent solution. However, this solution will be implemented for all tables. When you have tables in the same legacy system of which the primary key is populated by a sequence, than you will have a problem.
Regarding your question, the generated ID column value by a trigger after an INSERT statement can be retrieved as following:
insert into
(…)
values
(…) returning into
See Oracle documentation:
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_913a.htm#2133422
Comment by Guido Zeelen — May 15, 2008 @ 9:37 pm |
@Guido
OK, then try the following.
Put this in initialization file:
ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.class_eval do
alias_method :orig_next_sequence_value, :next_sequence_value
if sequence_name == ‘autogenerated’
id = 0
@connection.exec(“SELECT to_number(sys_guid(), ‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX’ ) FROM dual”) { |r| id = r[0].to_i }
id
else
orig_next_sequence_value(sequence_name)
end
end
And define your model with trigger generated primary keys in the following way:
class LegacyModel < ActiveRecord::Base
set_sequence_name ‘autogenerated’
end
Is it OK for you now? :)
Comment by Raimonds Simanovskis — May 15, 2008 @ 10:25 pm |
Wow, you are fast.
This solution is perfect.
Comment by Guido Zeelen — May 15, 2008 @ 10:38 pm |
I created Lighthouse project where to register bugs or create new enhancement requests for Oracle enhanced adapter – http://rsim.lighthouseapp.com/projects/11468-oracle-enhanced/tickets
Everyone interested is invited to use it :)
Comment by Raimonds Simanovskis — May 16, 2008 @ 9:34 pm |
oops… I added a ticket only to see that you already took an approach to the issue. Sorry about that, and thanks for the fix. I’ll pull it down and take a look.
Comment by Todd F — May 19, 2008 @ 8:30 pm |
I tried using enhanced oracle adapter. No session data is getting saved. When I changed session store to cookies then everything works fine. I unpacked the gem under vendor and loaded it inside our env config file. For database config file, used the name oracle_enhanced as the adapter name. Seeems to be not working for me. Did I miss anything?
Please let me know…
Thanks in advance for your help !!
Comment by Amit Kumar — June 3, 2008 @ 4:25 am |
@Amit – could you tell me which Rails version are you using?
I am currently using Rails 2.0.2 and have tested it with oracle_enhanced adapter and with this version sessions are successfully stored in database.
Comment by Raimonds Simanovskis — June 3, 2008 @ 8:34 am |
My application is running on version 2.0.2, Linux and Windows environment. It appears as if enhanced_write_lob method is not getting called. Do I need to switch on some other stuff to make it work?
Help needed…. Thanks in advance !
Comment by Amit Kumar — June 4, 2008 @ 2:14 am |
@Amit – probably the issue is that you unpacked gem under vendor. In this case probably the loading sequence of libraries is different, I need to check it.
So try to install it as a gem and then see if it works – in this case gem should be loaded after ActiveSupport gem and then enhanced_write_lobs callback should be added to session store class.
Comment by Raimonds Simanovskis — June 4, 2008 @ 9:26 am |
It works the other way, when the gem is not unpacked inside the app. To not leave any external dependency I prefer having all gems unpacked inside app. Is there a way to load gem later.. I tried Googling with not much success so far.
I appreciate your help.
Let me know if you find a solution to my problem.
Thanks !
Comment by Amit Kumar — June 5, 2008 @ 1:47 am |
hi i have used ur gem its working fine,.but when i want to add some extra column in my model its not get migrated.My migration file will look like
class User1 40
end
def self.down
remove_column :users,:Email
end
end
i am getting error like this,waiting for ur reply
20080618133606 CreateUsers: migrating ======================================
– create_table(:users)
rake aborted!
OCIError: ORA-00955: name is already used by an existing object:
Comment by N.T.Gen — June 19, 2008 @ 1:19 pm |
There is some problem in your provided code example – this is not full migration file.
From the error message I just can tell that you are running CreateUser migration (which tries to create new database table users) but already in the database there is table users and Oracle is complaining that it cannot create other object with the same name.
Comment by Raimonds Simanovskis — June 19, 2008 @ 5:50 pm |
hi again i am getting the same problem.This is my model migration file
class CreateImages 30
t.string :thumbnail ,:limit=>30
t.string :filename ,:limit=>30
t.timestamps
end
end
def self.down
drop_table :images
end
end
Its giving error as
(in /home/lostwarrior/Myworks/GD)
== 20080624095119 CreateUsers: migrating ======================================
– create_table(:users)
rake aborted!
OCIError: ORA-00955: name is already used by an existing object: CREATE TABLE users (id NUMBER(38) NOT NULL PRIMARY KEY, firstname VARCHAR2(30) DEFAULT NULL NULL, lastname VARCHAR2(30) DEFAULT NULL NULL, age NUMBER(38) DEFAULT NULL NULL, sex VARCHAR2(3) DEFAULT NULL NULL, dateofbirth DATE DEFAULT NULL NULL, telephonenumber VARCHAR2(30) DEFAULT NULL NULL, mobilenumber VARCHAR2(30) DEFAULT NULL NULL, emailid VARCHAR2(30) DEFAULT NULL NULL, occupation VARCHAR2(30) DEFAULT NULL NULL, reffer VARCHAR2(30) DEFAULT NULL NULL, address1 VARCHAR2(2048) DEFAULT NULL NULL, address2 VARCHAR2(2048) DEFAULT NULL NULL, city NUMBER(38) DEFAULT NULL NULL, state NUMBER(38) DEFAULT NULL NULL, pincode NUMBER(38) DEFAULT NULL NULL, created_at DATE DEFAULT NULL NULL, updated_at DATE DEFAULT NULL NULL)
(See full trace by running task with –trace)
Comment by N.T.Gen — June 25, 2008 @ 8:05 am |
You provided code for CreateImages migration, but later the error is for CreateUsers migration.
It means that in the environment where you are running these migrations, schema_migrations table does not contain record for migration 20080624095119 (which is migration for CreateUsers) and therefore is trying to apply this migration. But this migration is failing as users table is already present in this environment. So please check records in schema_migrations table and check if users table is already present. If users table contents are not necessary then just drop this users table and rerun migrations.
Comment by Raimonds Simanovskis — June 25, 2008 @ 8:44 am |
i solved it since i didnt give full access to my db user
Comment by N.T.Gen — June 25, 2008 @ 10:40 am |
Hi. I am using enhanced oracle adapter. Session data is not getting saved in database. When I changed session store to cookies it works. Any suggestions to make it work? I have installed enhanced oracle adapter as gem on my system.
Thanks.
Comment by Kunjan — June 25, 2008 @ 1:36 pm |
for me the id is directly incremented from 1000 not from 1
Comment by Thillai — July 18, 2008 @ 8:08 am |
Oracle adapter takes sequence values from corresponding Oracle sequences – e.g. if table name is users, then id values are taken from sequence users_seq. In original Oracle adapter sequences are created with option “START WITH 10000″ which means that first id value will be 10000. I do not know why it was done so and I also did not change that in Oracle enhanced adapter. Probably one reason could be that you can use id values <10000 in your test fixtures and then also create new records in tests with automatically assigned id values and can be sure that id values will not overlap.
But this should not cause any issues for application development as id values should just be unique and it does not matter what is the starting value.
Comment by Raimonds Simanovskis — July 18, 2008 @ 10:56 pm |
Very cool stuff!
Exactly what I needed to try and beat my 10+ year old legacy oracle app into shape.
Thanks for the work =)
Cheers
Dave Smylie
Comment by Dave Smylie — July 21, 2008 @ 11:15 pm |
Will this work??
ruby -r oracle_enhanced -e “OCI8.new(‘ruby’, ‘ruby’, ‘ifstest’).exec(’select sysdate FROM dual’){|r| puts r.join(‘,’)}”
I wanted to test the oracle_enhanced adapter and I get the error msg
ruby: no such file to load — oracle_enhanced (LoadError)
Comment by Burma — September 24, 2008 @ 11:58 pm |
oracle_enhanced is meant to be used together with ActiveRecord in Rails and not just in standalone mode. In your Rails database.yml file just use oracle_enhanced fro adapter name.
Or do you need to use ActiveRecord without Rails to connect to Oracle database?
Comment by Raimonds Simanovskis — September 25, 2008 @ 1:20 am |
While attempting to generate a scaffold for my app, I’ve run into the following error:
/Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:74:in `establish_connection’: Please install the oracle_enhanced adapter: `gem install activerecord-oracle_enhanced-adapter` (Expected /Library/Ruby/Gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.1.8/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb to define ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter) (RuntimeError)
As you can see from my gem list, the oracle_enhanced-adapter is installed. Here is the complete list in case it’s useful.
actionmailer (2.2.2, 2.1.1, 2.1.0, 1.3.6)
actionpack (2.2.2, 2.1.1, 2.1.0, 1.13.6)
actionwebservice (1.2.6)
activerecord (2.2.2, 2.1.1, 2.1.0, 1.15.6)
activerecord-oracle_enhanced-adapter (1.1.8, 1.1.7)
activeresource (2.2.2, 2.1.1, 2.1.0)
activesupport (2.2.2, 2.1.1, 2.1.0, 1.4.4)
acts_as_ferret (0.4.1)
capistrano (2.0.0)
cgi_multipart_eof_fix (2.5.0, 2.2)
daemons (1.0.9, 1.0.7)
dnssd (0.6.0)
fastthread (1.0.1, 1.0)
fcgi (0.8.7)
ferret (0.11.4)
gem_plugin (0.2.3, 0.2.2)
highline (1.2.9)
hpricot (0.6)
libxml-ruby (0.3.8.4)
mocha (0.9.1)
mongrel (1.1.4, 1.0.1)
needle (1.3.0)
net-sftp (1.1.0)
net-ssh (1.1.2)
piston (1.4.0)
rails (2.2.2, 2.1.1, 2.1.0, 1.2.6)
rake (0.8.3, 0.8.1, 0.7.3)
RedCloth (3.0.4)
redgreen (1.2.2)
ruby-openid (1.1.4)
ruby-yadis (0.3.4)
rubygems-update (1.3.1, 1.2.0)
rubynode (0.1.3)
sqlite3-ruby (1.2.1)
termios (0.9.4)
Everything seems to be in place, but it’s not working. Despite many attempts to uninstall and re-install much of rails as well as trying out different versions of the gems, nothing has fixed it. I can hobble along using the standard oracle adaptor which seems to work fine, but I’d love to find a solution to this problem. Any ideas?
Comment by Erik — December 11, 2008 @ 2:45 am |
@Erik
Please verify that
1. Oracle Instant Client is installed correctly.
Can you connect to database using sqlnet?
2. ruby-oci8 1.0.3 is installed correctly.
Try something like:
ruby -r oci8 -e “OCI8.new(‘user’, ‘password’,'database’).exec(’select * from dual’) do |r| puts r.join(‘,’); end”
to verify that ruby-oci8 is working
3. Verify that activerecord-oracle_enhanced-adapter is working from irb:
require ‘rubygems’
gem ‘activerecord’
gem ‘activerecord-oracle_enhanced-adapter’
require ‘activerecord’
ActiveRecord::Base.establish_connection(:adapter => “oracle_enhanced”,
:database => “database”,:username => “user”,:password => “password”)
and see if it is successful (use your correct database, username and password)
Btw if you have further issues please use http://groups.google.com/group/oracle-enhanced discussion group – it will be more appropriate than long comments here :)
Comment by Raimonds Simanovskis — December 11, 2008 @ 10:58 am |
I had the same error as Erik; it seems that oracle-enhanced depends on “hoe”, but this is not automatically installed. Once I installed it manually, it worked as expected:
gem install hoe
Comment by Brian — January 20, 2009 @ 3:29 pm |
Thanks Raimonds,
I have been battling weird errors with date/datetime/time while using Oracle’s original adapter. I installed yours and everything worked like a charm.
Comment by Pepe — December 31, 2009 @ 12:19 am |
Hi All,
I seem to be having a problem with the ActiveRecord find parameter.
I get the following error back:
NoMethodError: undefined method `empty?’ for 20:Fixnum
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:1624:in `instantiate’
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:661:in `find_by_sql’
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:661:in `collect!’
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:661:in `find_by_sql’
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:1548:in `find_every’
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:1505:in `find_initial’
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:613:in `find’
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:623:in `first’
from (irb):1
Any pointers?
The table I am trying to connect to is:
create_table “reporter_status”, :id => false, :force => true do |t|
t.string “identifier”
t.integer “serial”, :limit => 16, :precision => 16, :scale => 0
t.string “node”, :limit => 64
t.string “nodealias”, :limit => 64
t.string “manager”, :limit => 64
t.string “agent”, :limit => 64
t.string “alertgroup”, :limit => 64
t.string “alertkey”, :limit => 64
t.string “summary”
t.string “location”, :limit => 64
t.integer “class”, :limit => 16, :precision => 16, :scale => 0
t.integer “poll”, :limit => 16, :precision => 16, :scale => 0
t.integer “type”, :limit => 16, :precision => 16, :scale => 0
t.integer “tally”, :limit => 16, :precision => 16, :scale => 0
t.boolean “severity”, :precision => 1, :scale => 0
t.integer “owneruid”, :limit => 16, :precision => 16, :scale => 0
t.integer “ownergid”, :limit => 16, :precision => 16, :scale => 0
t.integer “acknowledged”, :limit => 16, :precision => 16, :scale => 0
t.datetime “lastmodified”
t.datetime “firstoccurrence”
t.datetime “lastoccurrence”
t.datetime “deletedat”
t.boolean “originalseverity”, :precision => 1, :scale => 0
t.string “ipaddress”, :limit => 64
t.string “customername”, :limit => 64
t.integer “devicecategory”, :limit => 8, :precision => 8, :scale => 0
t.string “position”, :limit => 64
t.string “tag”, :limit => 64
t.string “custlocation”, :limit => 64
t.string “ttno”, :limit => 64
t.integer “escalation”, :limit => 8, :precision => 8, :scale => 0
t.datetime “clearedtime”
t.datetime “acktime”
t.string “circuitdetails”, :limit => 50
t.integer “impactstate”, :limit => 4, :precision => 4, :scale => 0
t.integer “notificationid”, :limit => 16, :precision => 16, :scale => 0
t.string “accountid”, :limit => 64
t.integer “servicediff”, :limit => 4, :precision => 4, :scale => 0
t.string “relationship”, :limit => 64
t.integer “sourceserverserial”, :limit => 16, :precision => 16, :scale => 0
t.string “servername”, :limit => 64, :null => false
t.integer “serverserial”, :limit => 16, :precision => 16, :scale => 0, :null => false
t.decimal “expiretime”
t.string “alerttype”, :limit => 64
end
Comment by Bruce — January 18, 2010 @ 3:39 pm |
Please post your issue at discussion group http://groups.google.com/group/oracle-enhanced – it will be more appropriate to use it as then also other oracle_enhanced users can help. And please include also your model class definition and also show exactly what find method call is causing this error.
Comment by Raimonds Simanovskis — January 18, 2010 @ 7:40 pm |
I want to executed a named_scope
named_scope :order_by_position, :order => ‘CASE WHEN position = 1 THEN -1 ELSE name END ASC’
In Log file I am getting:
OCIError: ORA-00932: inconsistent datatypes: expected NUMBER got CHAR: SELECT * FROM groups ORDER BY CASE WHEN position = 1 THEN -1 ELSE name END ASC)
# Table name: products
#
# id :integer(38) not null, primary key
# name :string(255)
# created_at :datetime
# updated_at :datetime
# position :integer(38)
I am running this with Rails 2.3.3 and activerecord-oracle_enhanced-adapter (1.2.3)
I tried using TO_NUMBER(to_number) but didn’t work. And in general what things cause these errors.
What changes are required here to get it working?
Thanks,
Piyush Y. Patil
Comment by piyush — January 23, 2010 @ 11:55 am |