Ruby/Oracle connectivity

ruby-talk
1

Hi All,

I have tried my level best to connect to oracle using ruby but all in
vain.

Can any one provide concrete steps to follow to do that.
Please list down what all we require for this.

Thanks in advance.

Regards
Dheeraj Gambhir

···

--
Posted via http://www.ruby-forum.com/.

2

Are you running ruby on linux, windows, aix, freebsd, what version of ruby?

Where and what version of oracle are you using and trying to connect to?

Cheers--

Charles

···

On Jul 21, 2009, at 8:05 AM, Dheeraj Gambhir wrote:

Hi All,

I have tried my level best to connect to oracle using ruby but all in
vain.

Can any one provide concrete steps to follow to do that.
Please list down what all we require for this.

Thanks in advance.

Regards
Dheeraj Gambhir
-- Posted via http://www.ruby-forum.com/\.

---
Charles Johnson
Advanced Computing Center for Research and Education
Vanderbilt University

3

Dheeraj Gambhir wrote:

I have tried my level best to connect to oracle using ruby but all in
vain.

Can any one provide concrete steps to follow to do that.
Please list down what all we require for this.

It's much more productive if we take this the other way round:

1. Show us what you have tried (exact copy-paste of irb sessions or code
snippets)

2. Show us the exact error messages you got (again, copy-paste)

3. If there were no error messages, show what the actual behaviour was
of your program, and what you expected it to be.

It will then be easy for us to help you. For more info, see
http://www.catb.org/~esr/faqs/smart-questions.html#intro

(and if you haven't seen this before, it's well worth reading the whole
way through)

Regards,

Brian.

···

--
Posted via http://www.ruby-forum.com/\.

4

You can also try reading this: Connecting to Oracle from Ruby on Rails | Gnana Prasad Ruby

Cheers--

Charles

···

On Jul 21, 2009, at 8:05 AM, Dheeraj Gambhir wrote:

Hi All,

I have tried my level best to connect to oracle using ruby but all in
vain.

Can any one provide concrete steps to follow to do that.
Please list down what all we require for this.

Thanks in advance.

Regards
Dheeraj Gambhir
--
Posted via http://www.ruby-forum.com/\.

---
Charles Johnson
Advanced Computing Center for Research and Education
Vanderbilt University

5

Hi Brian,

First of all, thanks a lot for replying so quickly.

I am using Oracle 9i, ruby 1.8.6 .

Till now i have tried:

require 'rubygems'
gem 'ActiveRecord-JDBC'
require 'jdbc_adapter'
require 'active_record'
require 'active_record/version'

ActiveRecord::Base.establish_connection(
   :adapter => 'jdbc',
   :driver => 'oracle.jdbc.driver.OracleDriver',
   :url => 'jdbc:oracle:thin:@myhost:1521:mydb',
   :username=>'mlbread',
   :password=>'mlbread'
)
ActiveRecord::Base.connection.execute("SELECT * FROM
bam_order.ec_invoice")

···

****************************************

It gives following error:
C:\Program Files\Watir\examples>ruby oraclecon.rb
warning: ActiveRecord-JDBC is for use with JRuby only
c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in
`gem_original_re
quire': no such file to load -- c:/ruby/jruby-1.2.0/lib/ojdbc14.jar
(MissingSour
ceFile)
        from
c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `re
quire'
        from
c:/ruby/lib/ruby/gems/1.8/gems/activesupport-2.3.2/lib/active_suppo
rt/dependencies.rb:156:in `require'
        from
c:/ruby/lib/ruby/gems/1.8/gems/activesupport-2.3.2/lib/active_suppo
rt/dependencies.rb:521:in `new_constants_in'
        from
c:/ruby/lib/ruby/gems/1.8/gems/activesupport-2.3.2/lib/active_suppo
rt/dependencies.rb:156:in `require'
        from oraclecon.rb:6

Regards
Dheeraj Gambhir

Brian Candler wrote:

Dheeraj Gambhir wrote:

I have tried my level best to connect to oracle using ruby but all in
vain.

Can any one provide concrete steps to follow to do that.
Please list down what all we require for this.

It's much more productive if we take this the other way round:

1. Show us what you have tried (exact copy-paste of irb sessions or code
snippets)

2. Show us the exact error messages you got (again, copy-paste)

3. If there were no error messages, show what the actual behaviour was
of your program, and what you expected it to be.

It will then be easy for us to help you. For more info, see
How To Ask Questions The Smart Way

(and if you haven't seen this before, it's well worth reading the whole
way through)

Regards,

Brian.

--
Posted via http://www.ruby-forum.com/\.

6

You can break it down to 1 single line in the error message:

no such file to load -- c:/ruby/jruby-1.2.0/lib/ojdbc14.jar

If you want to use JDBC with JRuby you need the ojdbc14.jar from oracle
homepage.

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdb
c9201.html

Have fun!
Viktor

···

Dheeraj Gambhir <checktestingthings@gmail.com> wrote:

7

Viktor Zacek wrote:

If you want to use JDBC with JRuby you need the ojdbc14.jar from oracle
homepage.

But he also said he was running ruby 1.8.6, which is not JRuby. And he
explicitly gets this message:

"warning: ActiveRecord-JDBC is for use with JRuby only"

So that's the problem - following someone's instructions for JRuby when
you're not running JRuby :slight_smile:

I'm afraid I don't use Windows. Under Linux the gems I need are:

activerecord
activerecord-oracle-adapter

and I also need ruby-oci8 installed - which I build from source, and
which needs the Oracle client libraries around (I have
oracle-instantclient-devel, oracle-instantclient-basiclite,
oracle-instantclient-sqlplus)

If you go to the ruby-oci8 homepage I expect you'll find either a
ready-made Windows package, or instructions for installing it under
Windows.

HTH,

Brian.

···

--
Posted via http://www.ruby-forum.com/\.

8

If i run this code to check whether i am able to connect to the Oracle
database server or not:

ruby -r oci8 -e "OCI8.new('mlbread', 'mlbread', 'MLBD').exec('select *
from bam_order.ec_invoice where order-id>2316027') do |r| puts r.join('

'); end"

It gives me:

C:\ruby>ruby -r oci8 -e "OCI8.new('mlbread', 'mlbread',
'MLBD').exec('select * f
rom bam_order.ec_invoice where order-id>2316027') do |r| puts r.join(' |
'); end
"
c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:52:in `init': OCI Library
Initialization
Error (OCIError)
        from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:52

Regards
D G

Brian Candler wrote:

···

Viktor Zacek wrote:

If you want to use JDBC with JRuby you need the ojdbc14.jar from oracle
homepage.

But he also said he was running ruby 1.8.6, which is not JRuby. And he
explicitly gets this message:

"warning: ActiveRecord-JDBC is for use with JRuby only"

So that's the problem - following someone's instructions for JRuby when
you're not running JRuby :slight_smile:

I'm afraid I don't use Windows. Under Linux the gems I need are:

activerecord
activerecord-oracle-adapter

and I also need ruby-oci8 installed - which I build from source, and
which needs the Oracle client libraries around (I have
oracle-instantclient-devel, oracle-instantclient-basiclite,
oracle-instantclient-sqlplus)

If you go to the ruby-oci8 homepage I expect you'll find either a
ready-made Windows package, or instructions for installing it under
Windows.

HTH,

Brian.

--
Posted via http://www.ruby-forum.com/\.

9

Now that you mention it... i see... :wink:

Took a quick search... take a look at the following link:
http://www.wit-eye.nl/weblog/2006/01/06/starting-with-ruby-and-oracle/

···

Brian Candler <b.candler@pobox.com> wrote:

Viktor Zacek wrote:
> If you want to use JDBC with JRuby you need the ojdbc14.jar from oracle
> homepage.

But he also said he was running ruby 1.8.6, which is not JRuby. And he
explicitly gets this message:

"warning: ActiveRecord-JDBC is for use with JRuby only"

So that's the problem - following someone's instructions for JRuby when
you're not running JRuby :slight_smile:

10

Haven't been using ruby with oracle for some time...
but do you have an oracle client (or oracle instant client) installed?
AFAIK when using OCI8 this is a prerequisite.

···

Dheeraj Gambhir <checktestingthings@gmail.com> wrote:

If i run this code to check whether i am able to connect to the Oracle
database server or not:

ruby -r oci8 -e "OCI8.new('mlbread', 'mlbread', 'MLBD').exec('select *
from bam_order.ec_invoice where order-id>2316027') do |r| puts r.join('
> '); end"

It gives me:

C:\ruby>ruby -r oci8 -e "OCI8.new('mlbread', 'mlbread',
'MLBD').exec('select * f
rom bam_order.ec_invoice where order-id>2316027') do |r| puts r.join(' |
'); end
"
c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:52:in `init': OCI Library
Initialization
Error (OCIError)
        from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:52

Regards
D G

11

Prerequisites:
- have clean ruby 1.8.6 installation
- have running oracle client

download:
ruby-oci8-1.0.6-mswin32.rb from http://rubyforge.org/project/ruby-oci8/

install with:
ruby ruby-oci8-1.0.6-mswin32.rb
Accept question with "Yes"

now use the following ruby-script:
require 'oci8'

begin
  conn = OCI8.new("system","manager","db")
  conn.exec('select host_name, instance_name from v$instance') do |r|
    puts r
  end
  conn.logoff
end

Best regards,
Viktor

···

Viktor Zacek <usenet2009@zacek.de> wrote:

Took a quick search... take a look at the following link:
http://www.wit-eye.nl/weblog/2006/01/06/starting-with-ruby-and-oracle/

12

Dheeraj Gambhir wrote:

C:\ruby>ruby -r oci8 -e "OCI8.new('mlbread', 'mlbread',
'MLBD').exec('select * f
rom bam_order.ec_invoice where order-id>2316027') do |r| puts r.join(' |
'); end
"
c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:52:in `init': OCI Library
Initialization
Error (OCIError)
        from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:52

This is probably worth raising on the ruby-oci8 list, where I suggest
you report the exact version of ruby-oci8 you installed, e.g. was it one
of the ruby-oci8-x86-mswin32 files from rubyforge?

Check the documentation to see what the prerequisites are in terms of
Oracle client libraries. Also, you might have issues if you are using
32bit OCI8 vs 64bit Oracle libraries, or vice versa.

You appear not to be using the gem version of ruby-oci8, so perhaps "gem
install ruby-oci8" would work better. Googling for "ruby-oci8 windows"
suggested this.

···

--
Posted via http://www.ruby-forum.com/\.

13

When i run the above code i.e.
require 'oci8'

begin
  conn = OCI8.new("system","manager","db")
  conn.exec('select host_name, instance_name from v$instance') do |r|
    puts r
  end
  conn.logoff
end

After having Prerequisites:

Prerequisites:
- have clean ruby 1.8.6 installation
- have running oracle client
- ruby-oci8-1.0.6-mswin32.rb

It gives me following error:

C:\testwatir>ruby contest.rb
c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:52:in `init': OCI Library
Initialization
Error (OCIError)
        from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:52
        from
c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `ge
m_original_require'
        from
c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `re
quire'
        from contest.rb:1

Please help me.

···

--
Posted via http://www.ruby-forum.com/.

14

Dheeraj Gambhir wrote:

Please help me.

If nobody can answer here, try taking this to the ruby-oci8 forum.

http://rubyforge.org/forum/?group_id=256

···

--
Posted via http://www.ruby-forum.com/\.

15

Connecting with the oracle client (i.e. sqlplus) works?

I am lacking experience with OCI, but my overall feeling says the
problem is somewhere located in the prerequisites... maybe some missing
oracle dll, or misconfigured client, missing tnsnames.ora or something
like that.

Best regards,
Viktor

···

Dheeraj Gambhir <checktestingthings@gmail.com> wrote:

It gives me following error:

C:\testwatir>ruby contest.rb
c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:52:in `init': OCI Library
Initialization
Error (OCIError)
        from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:52
        from
c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `ge
m_original_require'
        from
c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `re
quire'
        from contest.rb:1

Please help me.

16

The following URL may help you.
  http://rubyforge.org/forum/forum.php?thread_id=19973&forum_id=1078

···

On Wed, Jul 22, 2009 at 2:11 PM, Dheeraj Gambhir<checktestingthings@gmail.com> wrote:

c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:52:in `init': OCI Library
Initialization
Error (OCIError)

17

Takehiro Kubo wrote:

c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:52:in `init': OCI Library
Initialization
Error (OCIError)

The following URL may help you.
  http://rubyforge.org/forum/forum.php?thread_id=19973&forum_id=1078

I tried it also, but no gain :(,

still m getting the same error i.e:

C:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:329:in `load_driver': Unable to
load drive
r 'OCI8' (DBI::InterfaceError)
        from C:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:227:in
`_get_full_driver'
        from C:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:213:in `connect'
        from testcon.rb:8:in `dataVerification'
        from testcon.rb:15

On running the following code:

require 'dbi'
puts DBI.available_drivers
dbh = DBI.connect('DBI:OCI8:MLBD', 'mlbread', 'mlbread')
rs = dbh.prepare('select invoice_id from bam_order.ec_invoice where
order_id=139920;')
rs.execute
while rsRow = rs.fetch do
   p rsRow
end
rs.finish
dbh.disconnect

Please help.

Can you guys send me your dbi.rb and oci8.rb.

Regards
Dheeraj Gambhir

···

On Wed, Jul 22, 2009 at 2:11 PM, Dheeraj > Gambhir<checktestingthings@gmail.com> wrote:

--
Posted via http://www.ruby-forum.com/\.

18

Takehiro Kubo wrote:

c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:52:in `init': OCI Library
Initialization
Error (OCIError)

The following URL may help you.
http://rubyforge.org/forum/forum.php?thread_id=19973&forum_id=1078

I tried it also, but no gain :(,

What did you do?
Does sqlplus work? Does it work on the command console?
Did you checked the environment variable PATH and OCI.DLL location?
We are not beside you.

still m getting the same error i.e:

You have no need to post pointless code. You should check where is
wrong by making minimum code to reproduce the problem. It will be made
by just a line: require 'oci8'

···

On Mon, Jul 27, 2009 at 8:42 PM, Dheeraj Gambhir<checktestingthings@gmail.com> wrote:

On Wed, Jul 22, 2009 at 2:11 PM, Dheeraj >> Gambhir<checktestingthings@gmail.com> wrote:

19

Interessting...

when I install oci8 via gem I get the same error with my little script.

When I install oci8 manually (see my post from 6 days ago), I get no
error.

Maybe it helps if you deinstall ruby and reinstall from scratch?

Best regards,
Viktor

···

Dheeraj Gambhir <checktestingthings@gmail.com> wrote:

still m getting the same error i.e:

C:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:329:in `load_driver': Unable to
load drive
r 'OCI8' (DBI::InterfaceError)
        from C:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:227:in
`_get_full_driver'
        from C:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:213:in `connect'
        from testcon.rb:8:in `dataVerification'
        from testcon.rb:15