Need help connecting to Oracle db please..
Ashok Tulachan
Hi Everyone, i am trying to connect to oracle db using ruby. I found the following tutorial in the oracle page but wasn't able to move forward as per the tutorial. Any help would be highly appreciated.
Connecting to Oracle from Ruby on Rails
by Obie Fernandez
Learn how to connect to Oracle from a Rails application and related authentication and performance issues.
Published June 2007
In the Java world, there are two common types of drivers for Oracle: the pure-Java (a.k.a. thin) driver and the native OCI driver. At the time of writing, no pure Ruby driver, similar to Java's JDBC-based thin driver, exists.
In order to connect to Oracle, you need to install the Ruby/Oracle Call Interface (OCI8) library, which is a database driver based on Ruby/DBI (Database Interface module). RubyDBI provides a database-independent interface for Ruby to talk to databases similar to JDBC or ODBC. The Ruby OCI8 driver provides connectivity to Oracle versions 8 through 10 via standard Oracle Client software. OCI8 is a Ruby wrapper written around native C code which does the actual interaction.
Non-windows developers can install by typing sudo gem install ruby-oci8. Windows developers should download the binary distribution of the driver from www.rubyforge.org/projects/ruby-oci8 and install it manually:
ruby ruby-oci8-0.1.16-mswin32.rb
A simple test of your Ruby OCI8 driver is to query a database containing the demo (HR) schema, with the following command-line Ruby program. Substitute the name of your Oracle database and password in the following command:
set oracle_sid=xe
ruby -r oci8 -e "OCI8.new('hr', 'password').exec('SELECT * FROM jobs ORDER BY 1') {|r| puts r.join}"
If the connection and query succeed, the output should look something like this:
AC_ACCOUNT | Public Accountant | 4200 | 9000
AC_MGR | Accounting Manager | 8200 | 16000
AD_ASST | Administration Assistant | 3000 | 6000
AD_PRES | President | 20000 | 40000
AD_VP | Administration Vice President | 15000 | 30000
FI_ACCOUNT | Accountant | 4200 | 9000
FI_MGR | Finance Manager | 8200 | 16000
HR_REP | Human Resources Representative | 4000 | 9000
IT_PROG | Programmer | 4000 | 10000
MK_MAN | Marketing Manager | 9000 | 15000
MK_REP | Marketing Representative | 4000 | 9000
PR_REP | Public Relations Representative | 4500 | 10500
PU_CLERK | Purchasing Clerk | 2500 | 5500
PU_MAN | Purchasing Manager | 8000 | 15000
SA_MAN | Sales Manager | 10000 | 20000
SA_REP | Sales Representative | 6000 | 12000
SH_CLERK | Shipping Clerk | 2500 | 5500
ST_CLERK | Stock Clerk | 2000 | 5000
ST_MAN | Stock Manager | 5500 | 8500
My code:
I did in cmd so that it would be easier
I installed oci8 from ruby library manually and unzipped it which is ruby-oci8-2.0.3
After running the query as stated above.
set oracle_sid=xe
ruby -r oci8 -e "OCI8.new('hr', 'password').exec('SELECT * FROM jobs ORDER BY 1') {|r| puts r.join}"
where hr is the username and password is the password.
I got the following error.
C:\Documents and Settings\atulach>ruby -r oci8 -e "OCI8.new('timr', 'timr#2009')
.exec('SELECT * from jobs ORDER BY 1') {|r| puts r.join}"
C:/Ruby192/lib/ruby/site_ruby/1.9.1/rubygems/custom_require.rb:36:in `require':
OCI.DLL: 126(The specified module could not be found. ) (LoadError)
from C:/Ruby192/lib/ruby/site_ruby/1.9.1/rubygems/custom_require.rb:36:i
n `require'
from C:/Ruby192/lib/ruby/gems/1.9.1/gems/ruby-oci8-2.0.4-x86-mingw32/lib
/oci8.rb:23:in `<top (required)>'
from <internal:lib/rubygems/custom_require>:33:in `require'
from <internal:lib/rubygems/custom_require>:33:in `rescue in require'
from <internal:lib/rubygems/custom_require>:29:in `require'
Can you please help me out on this?
--
Ashok Tulachan
Thanks
Hi Everyone, i am trying to connect to oracle db using ruby. I found the following tutorial in the oracle page but wasn't able to move forward as per the tutorial. Any help would be highly appreciated.
Connecting to Oracle from Ruby on Rails
by Obie Fernandez $connection = OCI8.new('timr', 'timr#2009', '//t01oracleqtp01.dsm.tic.wellsfargo.com:1523/qtpdb')
person=$connection.exec("SELECT B.BI_PREFIX, B.BI_FNAME, B.BI_MNAME, B.BI_LNAME, B.BI_SUFFIX, B.BI_ID_TYPE, B.BI_ID_NUMBER, B.BI_DOB, B1.*, R.*, M.*, C.*, L.* FROM chhayap.SCENARIO_NEW S, chhayap.SC_BORROWERPREF_NEW S1, chhayap.BORROWER_NEW B, chhayap.BORROWERPREF_NEW B1, chhayap.RES_ADD R, chhayap.MAIL_ADD M, chhayap.CLOS_ADD C, chhayap.LLORD_ADD L WHERE S.SCENARIO_ID = S1.SCENARIO_ID AND S1.PREF_ID = B1.PREF_ID AND B1.BORROWER_ID = B.BORROWER_ID AND B1.PREF_ID = R.RES_PREF_ID AND B1.PREF_ID = M.MAIL_PREF_ID AND B1.PREF_ID = C.CLOS_PREF_ID AND B1.PREF_ID = L.LLORD_PREF_ID AND S.RELEASE_ID = 'R.Other' AND S.SCENARIO_NO = '2' ORDER BY S1.SC_BORROWERPREF_ID") do |person|
#~ x=connection.query(sql)
puts "#{person["BI_EMAIL"]}"
end
--