Rails - Nov 2006 - 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/con
nection_adapters/oracle_adapter.rb:
361:in `columns''
        from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/bas
e.rb:696:in `columns''
        from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/bas
e.rb:704:in `columns_hash''
        from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/bas
e.rb:1562:in `column_for_attribute''

        from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/bas
e.rb:1801:in `read_attribute''
        from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/bas
e.rb:2067:in `send''
        from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/bas
e.rb:2067:in `clone_attribute_value
''
        from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/bas
e.rb:2061:in `clone_attributes''
        from
c:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.3.1/lib/active_support/in
flector.rb:161:in `inject''
        from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/bas
e.rb:2060:in `each''
        from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/bas
e.rb:2060:in `inject''
        from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/bas
e.rb:2060:in `clone_attributes''
        from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/bas
e.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


This communication is the property of Qwest and may contain confidential or
privileged information. Unauthorized use of this communication is strictly 
prohibited and may be unlawful.  If you have received this communication 
in error, please immediately notify the sender by reply e-mail and destroy 
all copies of the communication and any attachments.