ActiveRecord and Oracle Views

Rails
1

Hi all, so I’m developing an app in Rails (first time, have been using
Ruby a while), and I’m running into an issue with ActiveRecord. It
doesn’t seem to like Oracle views.

We have a large application where most stuff happens in Oracle stored
procedures. But, for displaying and joining this data I was hoping to
use ActiveRecord and Rails. When I try to simply say:

class Environment < ActiveRecord::Base
end

I get:

ORA-24372: invalid object for describe

I assume ActiveRecord is doing “describe environments” magic to create
accessors/etc. (Note: I get this same error trying this from sqlplus as
you can’t describe views)

Has anyone tried this and/or had success/failure? I’d like to be able
to use views with Rails (and ActiveRecord is not a requirement; just
trying to understand everything).

Thanks,
Nate

2

Nate,

I assume ActiveRecord is doing “describe environments” magic to create
accessors/etc. (Note: I get this same error trying this from sqlplus as
you can’t describe views)

It may not be using the describe function. For example I can use
sqlplus to
describe a view that I created called venvironments. I don’t have
ActiveRecord
setup so I didn’t test that part. But Oracle describe seemed to work on
a view.

Matt

Listing:

sqlplus blah/blah@blah
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> select object_name,object_type
2 from all_objects
3 where object_name = ‘VENVIRONMENTS’;

OBJECT_NAME OBJECT_TYPE

VENVIRONMENTS VIEW

SQL> desc venvironments
Name Null? Type

ID NOT NULL NUMBER(38)
ENVIRONMENT NOT NULL VARCHAR2(64)
LAYOUT_ID NOT NULL NUMBER(38)
LAYOUT VARCHAR2(32)
ENVIRONMENT_TYPE_ID NOT NULL NUMBER(38)
ENVIRONMENT_TYPE VARCHAR2(32)
LIFE_CYCLE_ID NOT NULL NUMBER(38)
LIFE_CYCLE VARCHAR2(32)
DEFAULT_DBSERVER_ID NUMBER(38)
DEFAULT_DBSERVER VARCHAR2(32)
AUTH_OBJECT_ID NOT NULL NUMBER(38)
AUTH_OBJECT VARCHAR2(64)
UPDATE_USER VARCHAR2(64)
UPDATE_DATE DATE
CREATE_USER VARCHAR2(64)
CREATE_DATE DATE

3

In the code of oracle connection adaptor for active record it looks
like views are intended to be supported. See the “when OCI_PTYPE_TABLE,
OCI_PTYPE_VIEW” line.

--------- excerpt -----------
# Uses the describeAny OCI call to find the target owner and
table_name
# indicated by +name+, parsing through synonynms as necessary.
Returns
# an array of [owner, table_name].
def describe(name)
@desc ||= @@env.alloc(OCIDescribe)
@desc.attrSet(OCI_ATTR_DESC_PUBLIC, -1) if VERSION >= ‘0.1.14’
@desc.describeAny(@svc, name.to_s, OCI_PTYPE_UNK)
info = @desc.attrGet(OCI_ATTR_PARAM)

  case info.attrGet(OCI_ATTR_PTYPE)
  when OCI_PTYPE_TABLE, OCI_PTYPE_VIEW
    owner      = info.attrGet(OCI_ATTR_OBJ_SCHEMA)
    table_name = info.attrGet(OCI_ATTR_OBJ_NAME)
    [owner, table_name]
  when OCI_PTYPE_SYN
    schema = info.attrGet(OCI_ATTR_SCHEMA_NAME)
    name   = info.attrGet(OCI_ATTR_NAME)
    describe(schema + '.' + name)
  end
end

Source File:
http://dev.rubyonrails.org/svn/rails/trunk/activerecord/lib/active_record/connection_adapters/oracle_adapter.rb

4

Just so everyone knows the resolution, it turned out my view had become
invalid because an underlying table had issues. The Oracle error was
just confusing. I managed to get the real error by doing a “select *
from environments”. Once I corrected the view it works.