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

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'