Daniel Berger
2006-Sep-20 14:47 UTC
Problems with ActiveRecord, Oracle adapter, find_by_sql, multi-table join - ORA-04043 error
Hi all,
Windows XP
Oracle 10g client
OCI8 0.1.16
Ruby 1.8.5
Rails 1.1.6
I''m hitting weird behavior with the Oracle adapter and a find_by_sql
call on a multi-table join.
The SQL looks like this:
SELECT DISTINCT e1.ip_address, loc.street, loc.city, loc.state
FROM schema.net_element ne, schema.equipment e1, schema.equipment e2,
schema.building b, schema.cust_loc cl, schema.location loc
WHERE e2.equipment_id = e1.equipment_id
AND e2.some_column like ''SOME_VAL%''
AND e1.equipment_id = ne.net_element_id
AND ne.class_name = ''FooBar''
AND e1.building_id = b.building_id
AND b.main_address = cl.cust_loc_id
AND cl.location_id = loc.location_id
So, I setup a class for the ''Location'' table like this:
class OracleLocation < ActiveRecord::Base
establish_connection(
:adapter => "oracle",
:database => "our_db",
:username => "user",
:password => "password"
)
set_table_name :location
set_primary_key "location_id"
end
I can get a record back just fine:
loc = OracleLocation.find_by_sql(sql).first
p loc
#<OracleLocation:0x385c610 @attributes={"state"=>"WA",
"street"=>"1234
Block St", "city"=>"Smallville",
"ip_address"=>"172.1.2.3"}>
However, if I try to refer to an attribute, either directly or via the
''attributes'' method, I get an error:
p loc.attributes # Boom!
(eval):3:in `__send__'': ORA-04043: object location does not exist
(OCIError)
from (eval):3:in `describe''
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/oracle_adapter.rb:
361:in `columns''
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:696:in
`columns''
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:704:in
`columns_hash''
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:1562:in
`column_for_attribute''
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:1801:in
`read_attribute''
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:2067:in
`send''
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:2067:in
`clone_attribute_value ''
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:2061:in
`clone_attributes''
from
c:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.3.1/lib/active_support/inflector.rb:161:in
`inject''
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:2060:in
`each''
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:2060:in
`inject''
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:2060:in
`clone_attributes''
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:1519:in
`attributes''
from oracletest2.rb:35:in `send''
from oracletest2.rb:35
This error confuses me. There clearly *is* a location table, or the
query wouldn''t work in the first place. Also, the fact that
I''m
getting back an ORA-04043 indicates to me that, when I call
loc.attributes, it''s calling out to the database *again* for some
reason. Why?
Is this error caused by the fact that I''m doing a multi-table join
here, but haven''t actually mapped any of the other tables besides
Location? What''s going on?
Thanks,
Dan
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---
Michael A. Schoen
2006-Sep-21 04:31 UTC
Re: Problems with ActiveRecord, Oracle adapter, find_by_sql, multi-table join - ORA-04043 error
> FROM schema.net_element ne, schema.equipment e1, schema.equipment e2, > schema.building b, schema.cust_loc cl, schema.location loc> set_table_name :location> This error confuses me. There clearly *is* a location table, or the > query wouldn''t work in the first place. Also, the fact that I''m > getting back an ORA-04043 indicates to me that, when I call > loc.attributes, it''s calling out to the database *again* for some > reason. Why?The problem is that your "Location" table appears to be in the schema "schema", but you''re setting the table name to just "location". Try: set_table_name ''schema.location'' --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---