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:indescribe’
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:incolumns’
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:incolumn_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:insend’
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:inclone_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:ineach’
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:inclone_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:insend’
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