i'm working with an existing oracle database with several database user. My problem is that i have to integrate an existing view in my Ruby on Rails Application, that is hold by another database user. My User has only 'granted' the rights for the view. How can I handle this?
2
-
What do you need to with it? Just view info from it? Treat it like an active record object etc. assuming it doesn't match AR's default idea of what the table should look like.Doon– Doon2015-11-26 13:50:44 +00:00Commented Nov 26, 2015 at 13:50
-
Yes, I only want to view Info from it. What do you mean exactly? Is it sufficient when I generate a Model like 'rails generate model view_name' ?FloWe– FloWe2015-11-26 14:11:48 +00:00Commented Nov 26, 2015 at 14:11
Add a comment
|
3 Answers
I've had success with the following:
Download basic, SQLPlus, SDK, instant client from Oracle website (eg for Mac http://www.oracle.com/technetwork/apps-tech/intel-macsoft-096467.html)
Extract and move the above to /usr/lib Create these symlinks:
ln -s libclntsh.dylib.12.1 libclntsh.dylib
ln -s libclntsh.so.12.1 libclntsh.so
Add this to your gemfile
gem 'ruby-oci8'
gem 'activerecord-oracle_enhanced-adapter', :git => 'git://github.com/rsim/oracle-enhanced.git'
run bundle install
Run these commands:
export NLS_LANG="AMERICAN_AMERICA.UTF8"
export ORACLE_HOME=/bin/oracle/11_2x64
export DYLD_LIBRARY_PATH=$ORACLE_HOME
export LD_LIBRARY_PATH=/usr/lib
Now that you have this working, add an environment like:
my_oracle_db:
adapter: oracle_enhanced
database: IPADDRESS/INSTANCE
username: USER
password: PASSWORD
to your database.yml
Now create a model:
class OracleBase < ActiveRecord::Base
self.abstract_class=true
establish_connection (:my_oracle_db)
#whatever methods
end
And then create a model based off of your view in Oracle
class MyOracleView < OracleBase
self.table_name = "SCHEMA.NAMEOFORACLEVIEW"
#whatever methods you want to add
end
Then create your controller and view in Rails as usual. Also please note that you'll probably need to use all caps for the Oracle schema and table name.
Comments
You could generate a model to get data from the view. Depending upon the view name and schema you might need to override AR's defaults. For example say your view was called other_user_view_ora and had a primary_key called user_id, and you want to access it as User
class User < ActiveRecord::Base
set_primary_key "user_id"
set_table_name "other_user_view_ora"
# database perms will enforce this, but just being pedantic
def readonly?
true
end
end
You should then be able to use the view like you would any ActiveRecord Object.
Another option would be to just issue sql commands via ActiveRecord::Base.connection. So in your controller do something like
def from_user_view
@results = ActiveRecord::Base.connection.execute "select * from other_user_view_ora limit 2"
end
and then in your view you can iterate over the @results array, treating each result like a hash.
example:
<% @results.each do |result| %>
<td><%=result['user_id']%></td>
<td><%=result['name']%></td>
<% end %>
Comments
Thanks everyone for your help!
It was satisfactory, as @JimEvans solution, to create a Model:
class TankBank < ActiveRecord::Base
self.table_name = "GEVIS.TANK_BANK"
end