Friday, March 4, 2011

Avoid oracle sequence during ActiveRecord model record insert

I was writing rspecs for one ActiveRecord model in my project and was connecting to different schema in which the corresponding mapping table was defined. The schema object (tables) definition was a legacy code, what I meant to say is I used the already existing object definition from that schema which was not using oracle sequence. There was no primary key for that table as well.

I was setting up the data for that table in my rspec using ActiveRecord insert.

e.g. AnotherSchema::TableObject.create(:ts => 123, :proj_code => "ABC", ...)

But, in Rails, when you try to insert the record with ActiveRecord Model, it always looks for a sequence, either a default one (Table_Object_seq) or explicitly defined one with set_sequence_name in the Model class.

So, it threw error as expected :-
OCIError: ORA-02289: sequence does not exist: select "Table_Object_seq
".nextval id from dual

I could have defined a sequence on this table only in the test database, but I googled around for a way to avoid oracle sequence in a model and I got this link :-
http://www.dixis.com/?p=127

I followed the suggestion and it worked for me :-)

What I did was :-

Monkeypatching OracleEnhanced Adapter's next_sequence_value method :-


and adding set_sequence_name to the Model class :-

set_sequence_name 'autogenerated'

It avoided the default oracle sequence look up while inserting new records with Rails ActiveRecord methods which solved my problem.
Posted by at

7 comments:

  1. AnonymousFebruary 16, 2012 at 4:55 AM

    Thanks for sharing this informative article. I am not aware of this issue and is about to use this in my code. You not only saved my time but also guided me what to use instead of it to solve the issue.
    sap erp training

    ReplyDelete
    Replies
    1. NiranjanFebruary 16, 2012 at 5:44 AM

      Good to know that my small article solved your problem! Thanks for reading :-)

      Delete
  2. AlbertApril 26, 2012 at 4:13 AM

    Hello, i'm going to aply this solution because, I have this problem too.
    ¿where do i put this code? ¿do i have to modify de gem file?

    thank you

    Albert

    ReplyDelete
    Replies
    1. NiranjanApril 26, 2012 at 7:34 AM

      Hi Albert, Thanks for reading! You can create a file say "oracle_enhanced_adapter_extension.rb" under app/extensions or lib/rails_extensions and put the below code :-

      http://gist.github.com/2499933

      Hope this helps ! Thanks !

      Delete
    2. NiranjanApril 26, 2012 at 7:39 AM

      And you can load the file under config/initializers folder if you have created that file under lib/rails_extensions folder with:-

      # load custom extensions
      Dir["#{RAILS_ROOT}/lib/rails_extensions/*"].each {|file| require file}

      Delete
    3. AlbertMay 5, 2012 at 1:09 PM

      Hello, I didn't receive any mail about your reply!! thanks. I figured out what you say by anther ways. But it doesn't work too.
      the problem I have is something than worked in Rails 3.0.1 and it doesn't in 3.2.3:
      When i use a "create_association" method, for example "person.create_user" the user model doesn't have a sequence for primary key (using set_primary_key another field) but the key of this issue is than in user table the primary key and foring_key is the same. And this is not suported by new Rails version,
      But I recognize this is not a good practice...
      I'm changing and old application to web platform, and the table are ... weird

      Delete
    4. NiranjanMay 7, 2012 at 5:57 AM

      Ok ... so the problem comes with association. I need to check the above solution with association as well. Thanks!

      Delete

Subscribe to: Post Comments (Atom)