OCIError: ORA-22816: unsupported feature with RETURNING clause

[Dave Smylie]

After upgrading from 1.2.4 to 1.3.0 I'm now getting a ORA-22816 error
when trying to insert a record into a view with an INSTEAD OF trigger
defined. eg:

Repairer Create (0.0ms) OCIError: ORA-22816: unsupported feature
with RETURNING clause: INSERT INTO "REPAIRER" ("REPA_MAIL_LIST_IND",
"REPA_PICTURE_FILE_NAME", "REPA_POSTAL_ADDR", "REPA_EFF_DTE",
"REPA_REF", "REPA_ACTIVE_IND", "REPA_DESC_OF_SERVICES", "REPA_NAME")
VALUES(NULL, NULL, NULL, NULL, NULL, NULL, 'Car Fixin', 'John')
RETURNING "REPA_ID" INTO :insert_id
vendor/gems/activerecord-oracle_enhanced-adapter-1.3.0/lib/
active_record/connection_adapters/oracle_enhanced_adapter.rb:1726:in
`log'
vendor/gems/activerecord-oracle_enhanced-adapter-1.3.0/lib/
active_record/connection_adapters/oracle_enhanced_adapter.rb:605:in
`execute'
vendor/gems/activerecord-oracle_enhanced-adapter-1.3.0/lib/
active_record/connection_adapters/oracle_enhanced_adapter.rb:629:in
`insert_sql'


This seems to be expected behavior as you can't use RETURNING INTO
when inserting in a view. (See http://www.orafaq.com/forum/t/55859/0/)

The problem seems to lie in the insert_sql function in
oracle_enhanced_adapter, but only when the primary key hasn't already
been set - I've worked around this (temporarily at least) by manually
fetching the primary key from the sequence in a before_create callback
on the model.

Is this a bug, or just a limitation of working with views that I'm
going to have to put up with?

Cheers
Dave Smylie

[Raimonds Simanovskis]

Haven't used instead of triggers on views, now I read about them and
know what they do :)

If INSERT with RETURNING into view with instead of trigger is not
supported then it sounds quite problematic how to get back new primary
key value after INSERT statement - insert_sql method should return it
back to ActiveRecord. Do you have any suggestions?

If primary key values are fetched from sequence then it would be
simpler to specify this sequence with set_sequence_name method in
class definition and specify primary key with set_primary_key method.

Raimonds

On Jul 20, 4:27 am, Dave Smylie <davesmy...@gmail.com> wrote:
> After upgrading from 1.2.4 to 1.3.0 I'm now getting a ORA-22816 error
> when trying to insert a record into a view with an INSTEAD OF trigger
> defined. eg:
>
>   Repairer Create (0.0ms)   OCIError: ORA-22816: unsupported feature
> with RETURNING clause: INSERT INTO "REPAIRER" ("REPA_MAIL_LIST_IND",
> "REPA_PICTURE_FILE_NAME", "REPA_POSTAL_ADDR", "REPA_EFF_DTE",
> "REPA_REF", "REPA_ACTIVE_IND", "REPA_DESC_OF_SERVICES", "REPA_NAME")
> VALUES(NULL, NULL, NULL, NULL, NULL, NULL, 'Car Fixin', 'John')
> RETURNING "REPA_ID" INTO :insert_id
>   vendor/gems/activerecord-oracle_enhanced-adapter-1.3.0/lib/
> active_record/connection_adapters/oracle_enhanced_adapter.rb:1726:in
> `log'
>   vendor/gems/activerecord-oracle_enhanced-adapter-1.3.0/lib/
> active_record/connection_adapters/oracle_enhanced_adapter.rb:605:in
> `execute'
>   vendor/gems/activerecord-oracle_enhanced-adapter-1.3.0/lib/
> active_record/connection_adapters/oracle_enhanced_adapter.rb:629:in
> `insert_sql'
>
> This seems to be expected behavior as you can't use RETURNING INTO

> when inserting in a view. (Seehttp://www.orafaq.com/forum/t/55859/0/)

[Dave Smylie]

I am already using the set_sequence_name and set_primary_key methods
(curse these
legacy oracle schemas =)

Should it be picking the fact these options are set and grabbing the
primary key prior
to calling insert_sql? (It looks like prefetch_primary_key? should
always return true,
so I'm not sure why it wouldn't be grabbing these?)

Dave


On Jul 22, 8:33 am, Raimonds Simanovskis

[minni]

ok, there is my "PERFORMANCE KILL" patch....
only a seed...
just look if there is an INSTEAD OF trigger:
SELECT description FROM USER_TRIGGERS WHERE table_name='#{table_name}'
AND description LIKE '%INSTEAD OF%' AND ROWNUM=1

diff -rupN activerecord-oracle_enhanced-adapter-1.3.0/lib/
active_record/connection_adapters/oracle_enhanced_adapter.rb /Library/
Ruby/Gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.3.0/lib/
active_record/connection_adapters/oracle_enhanced_adapter.rb
--- activerecord-oracle_enhanced-adapter-1.3.0/lib/active_record/
connection_adapters/oracle_enhanced_adapter.rb 2010-07-27
16:35:34.000000000 +0200
+++ /Library/Ruby/Gems/1.8/gems/activerecord-oracle_enhanced-
adapter-1.3.0/lib/active_record/connection_adapters/
oracle_enhanced_adapter.rb 2010-07-27 16:35:52.000000000 +0200
@@ -113,10 +113,6 @@ module ActiveRecord
def self.table_comment
connection.table_comment(self.table_name)
end
-
- def has_instead_of?
- connection.select_all("SELECT description FROM USER_TRIGGERS
WHERE table_name='#{table_name}' AND description LIKE '%INSTEAD OF%'
AND ROWNUM=1").size > 0
- end
end


diff -rupN activerecord-oracle_enhanced-adapter-1.3.0/lib/
active_record/connection_adapters/oracle_enhanced_jdbc_connection.rb /
Library/Ruby/Gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.3.0/
lib/active_record/connection_adapters/
oracle_enhanced_jdbc_connection.rb
--- activerecord-oracle_enhanced-adapter-1.3.0/lib/active_record/
connection_adapters/oracle_enhanced_jdbc_connection.rb 2010-07-27
16:35:34.000000000 +0200
+++ /Library/Ruby/Gems/1.8/gems/activerecord-oracle_enhanced-
adapter-1.3.0/lib/active_record/connection_adapters/
oracle_enhanced_jdbc_connection.rb 2010-07-27 16:36:04.000000000
+0200
@@ -219,7 +219,7 @@ module ActiveRecord
end

def returning_clause(quoted_pk)
- self.class.has_instead_of? ? '' : " RETURNING #{quoted_pk}
INTO ?"
+ " RETURNING #{quoted_pk} INTO ?"
end

# execute sql with RETURNING ... INTO :insert_id
diff -rupN activerecord-oracle_enhanced-adapter-1.3.0/lib/
active_record/connection_adapters/oracle_enhanced_oci_connection.rb /
Library/Ruby/Gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.3.0/
lib/active_record/connection_adapters/
oracle_enhanced_oci_connection.rb
--- activerecord-oracle_enhanced-adapter-1.3.0/lib/active_record/
connection_adapters/oracle_enhanced_oci_connection.rb 2010-07-27
16:35:34.000000000 +0200
+++ /Library/Ruby/Gems/1.8/gems/activerecord-oracle_enhanced-
adapter-1.3.0/lib/active_record/connection_adapters/
oracle_enhanced_oci_connection.rb 2010-07-27 16:35:59.000000000
+0200
@@ -79,7 +79,7 @@ module ActiveRecord
end

def returning_clause(quoted_pk)
- self.class.has_instead_of? ? '' : " RETURNING #{quoted_pk}
INTO :insert_id"
+ " RETURNING #{quoted_pk} INTO :insert_id"
end

# execute sql with RETURNING ... INTO :insert_id

[minni]

Same problem.
My WORKAROUND is:
def id
attributes['id'] || 0
end

my target is to force the if statement at row 618 of
activerecord-oracle_enhanced-adapter-1.3.0/lib/active_record/
connection_adapters/oracle_enhanced_adapter.rb:
def insert_sql(sql, name = nil, pk = nil, id_value = nil,
sequence_name = nil) #:nodoc:
# if primary key value is already prefetched from sequence
# or if there is no primary key
if id_value || pk.nil?
execute(sql, name)
return id_value
end

You can patch this adding to the statement a test if an INSTEAD OF is
defined into the object....

I hope this is useful, minni

[Raimonds Simanovskis]

Dave or minni

Could you create small script which would reproduce this issue (create
table, create view, create instead of trigger, define model class and
try to insert record into it).
Then it would be easier for me to investigate this issue.

Raimonds

[Manan]

Hi Raimonds,

Here is the script.

--------------------------------------------------------------------------------

-- TABLE

create table INT_CUSTOMERS
(
ID integer not null,
NAME varchar2(20) not null
);

-- VIEW

create view CUSTOMERS as select * from INT_CUSTOMERS;

-- SEQUENCE

create sequence CUSTOMERS_SEQ;

-- INSTEAD OF TRIGGER

create trigger CUSTOMERS_II
instead of insert on CUSTOMERS
declare
new_id integer;
begin

if nvl(:new.id, 0) <= 0 then
select CUSTOMERS_SEQ.nextval into new_id from dual;
else
new_id := :new.id;
end if;

insert into int_customers (id, name) values (new_id, :new.name);

end;

-- TEST FROM ORACLE

declare
my_new_id integer;
begin
insert into CUSTOMERS (name) values ('Raimonds') returning id into
my_new_id;
dbms_output.put_line(my_new_id);
end;

-- Model

class Customer < ActiveRecord::Base

# workaround to prevent error

before_create :set_my_id

def set_my_id
self.id = ActiveRecord::Base.connection.execute("select
customers_seq.nextval from dual").fetch[0].to_i
end

end

--------------------------------------------------------------------------------


Thanks
Manan




On Aug 2, 5:06 pm, Raimonds Simanovskis

[Rémi Gagnon]

Hello,

I'm facing this issue as well.  

Do you have any suggestion for me?

I'm with 

Rails 3.2.x

Enhanced_Oracle_Adapter 1.4.1

Thanks in advance.

Rémi

--
You received this message because you are subscribed to the Google Groups "Oracle enhanced adapter for ActiveRecord" group.
To post to this group, send email to oracle-...@googlegroups.com.
To unsubscribe from this group, send email to oracle-enhanc...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/oracle-enhanced?hl=en.