DESC teble_name failed; does it exist?

[Matalo]

Hi, I found weird behavior inside oracle_enhanced_connection.rb (version 1.3.0) file, from where i always get error mentioned in title. I looked into source code of this file at line 68, which states "if result = select_one(sql)" before this statement I created code "puts sql" which returns SELECT statement which is about to be executed, when I run this statement inside sql developer, under same connection(user,password) as set in database.yml, I get result one line, but inside oracle_enhanced_connection.rb will return nil and cause exception from line 76.

[rsim]

That sounds strange. Could you show me that sql statement and can you show me your database.yml (without sensitive information) and how do you define your ActiveRecord model class?

[Matalo]

Hi, here it goes

SQL statement: SELECT owner, table_name, 'TABLE' name_type FROM all_tables WHERE owner = 'DB_USER' AND table_name = 'PRODUCT_TEAMS' UNION ALL SELECT owner, view_name table_name, 'VIEW' name_type FROM all_views WHERE owner = 'DB_USER' AND view_name = 'PRODUCT_TEAMS' UNION ALL SELECT table_owner, DECODE(db_link, NULL, table_name, table_name||'@'||db_link), 'SYNONYM' name_type FROM all_synonyms WHERE owner = 'DB_USER' AND synonym_name = 'PRODUCT_TEAMS' UNION ALL SELECT table_owner, DECODE(db_link, NULL, table_name, table_name||'@'||db_link), 'SYNONYM' name_type FROM all_synonyms WHERE owner = 'PUBLIC' AND synonym_name = 'DB_USER.PRODUCT_TEAMS'

database.yml: development: adapter: oracle_enhanced database: 10.118.12.99:1521/orcl.oracle.com username: user pool: 5

test: adapter: oracle_enhanced database: 10.118.12.99:1521/orcl.oracle.com username: user

ActiveRecord: class ProductTeam < ActiveRecord::Base

belongs_to :product, :class_name => "Product", :foreign_key => "product_id" has_one :project, :through => :product

set table name including schema

set_table_name "db_user.product_teams" set_primary_key "product_team_id"

Used to tell active record that the database creates the primary key itself (using triggers and sequences)

set_sequence_name :autogenerated

end

[rsim]

Does database user "user" (which is specified in database.yml) have access to db_user.product_teams table? Can you try

require "rubygems"
require "oci8"
# replace with real password
conn = OCI8.new "user", "password", "10.118.12.99:1521/orcl.oracle.com"
pust conn.select_one("select * from db_user.product_teams"

[Matalo]

I granted 'user' all right for table product_teams, but when I run code you attached I receive error: OCIError: ORA-00942: table or view does not exist from stmt.c:306:in oci8lib.so

but when I run same code for other table e.g. db_user.products it runs fine. Strange is also that when I use SQL developer and connect to DB using same credentials as in attached code, sql statement "select * from db_user.product_teams" works.

[Matalo]

Ok, I fixed issue, I don't fully understand why but after recompiling objects the query starts to work

[1st8]

Unfortunately I'm having the same issue, will have to look into it now. select * from "db_user"."my_table" runs fine via ActiveRecord::Base.connection.execute.

[1st8]

conn.describe_table('"db_user"."mytable"') works fine and returns a OCI8::Metadata::Column object

[1st8]

This was it: #301

[OneHoopyFrood]

I'm seeing this error with the rails42 branch, which i switched to to solve this: https://github.com/rails/rails/issues/18739

Here's the error:

ActiveRecord::ConnectionAdapters::OracleEnhancedConnectionException: "DESC famous_databases" failed; does it exist?

Here's my setup:

Gemfile:

...
# Oracle adapter
gem "activerecord-oracle_enhanced-adapter", github: 'rsim/oracle-enhanced', branch: 'rails42' 
gem "ruby-oci8"
....

database.yml:

famous: 
  adapter: oracle_enhanced
  host: <IP>
  port: 1521
  database: famous
  # database: //192.168.0.25:1521/famous
  username: secret
  password: secret

The database is a legacy readonly and not the only database that will be used so I'm set up like this, but I don't think that should cause issues:

class ArTrxHeader < FamousDatabase
    self.table_name = :AR_TRX_HEADER

    self.primary_key = "ARTRXHDRIDX"
end

That inherits from:

class FamousDatabase < ActiveRecord::Base
    establish_connection :famous
    # Prevent creation of new records and modification to existing records
    def readonly?
        return true
    end

    # Prevent objects from being destroyed
    def before_destroy
        raise ActiveRecord::ReadOnlyRecord
    end
end

[OneHoopyFrood]

As I wrote that I found the issue. It's the inheritance. All I had to do was add: self.abstract_class = true to the FamousDatabase model and it worked. Should have been obvious. I'll just leave it here for anyone else who runs into the issue though.