Problems with ActiveRecord, Oracle adapter, find_by_sql, mul

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

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'

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs