insert on legacy tables with before insert triggers

[cardliv]

We have many legacy oracle databases where I work. The convention is
fairly consistent across all of them. They usually have "before
insert" triggers that create the primary key from a sequence.

I am writing a rails app, which needs to do inserts into tables which
have before insert triggers that create the primary key from a
sequence. I have seen a blog post that says this isn't possible. Why
is this? (I know plsql, but I have no OCI experience, so please
forgive my ignorance).

Why can't we get ActiveRecord to do an insert statement like the
following?

insert into exa_example_table(column_a, column_b) returning id
into :id;

At the moment my workaround is to comment out the following line from
oracle_enhanced_adapter.rb:

@connection.exec("select #{sequence_name}.nextval id from dual")
{ |r| id = r[0].to_i }

This results in active record create not returning the new id, so it
is not ideal.

[Raimonds Simanovskis]

I can offer the following "ugly" workaround using custom create method
(I think you need also to install ruby-plsql gem even if you are not
using PL/SQL calls because otherwise custom create method will not
work in oracle_enhanced):

<pre>
class Example < ActiveRecord::Base
set_table_name "exa_example_table"
set_create_method do
conn = connection.raw_connection
cursor = conn.parse <<-EOS
BEGIN
INSERT INTO exa_example_table (column_a, column_b) VALUES
(:column_a, :column_b)
RETURNING id INTO :id;
END;
EOS
cursor.bind_param(':column_a', column_a)
cursor.bind_param(':column_b', column_b)
cursor.bind_param(':id', nil, Integer)
cursor.exec
id = cursor[':id']
cursor.close
id
end
end
</pre>

In this way you can override the way how to create record in database.
And this will also assign ID column value to created Ruby object.

Raimonds

[cardliv]

Thanks very much for that code. I have generalized the insert
statement to work with any model:

set_create_method do
quoted_attributes = attributes_with_quotes

conn = connection.raw_connection
cursor = conn.parse <<-EOS
BEGIN

INSERT INTO #{self.class.quoted_table_name} (#
{quoted_column_names.join(', ')})
VALUES(#{quoted_attributes.values.join(', ')})
RETURNING #{self.class.primary_key} INTO :id;
END;
EOS