Sample one-liner fails to run in IRB on OSX

[belgoros]

I tried to run in the Terminal the simple one-liner provided in README:

ruby -r oci8 -e "OCI8.new('scott', 'tiger').exec('select * from emp') do |r| puts r.join(','); end"

after installing the 3 required packages with Homebrew as exmplained in README installation section..

It fails with the following error:

➜  ~ irb
2.4.0 :001 > ruby -r oci8 -e "OCI8.new('scott', 'tiger').exec('select * from emp') do |r| puts r.join(','); end"
SyntaxError: (irb):1: syntax error, unexpected tIDENTIFIER, expecting keyword_do or '{' or '('
ruby -r oci8 -e "OCI8.new('scott', 'tiger'
            ^
(irb):1: syntax error, unexpected tSTRING_BEG, expecting keyword_do or '{' or '('
ruby -r oci8 -e "OCI8.new('scott', 'tiger').exe
                 ^
    from /Users/XXXX/.rvm/rubies/ruby-2.4.0/bin/irb:11:in `<main>'

Any idea on what is wrong here ?

I have ruby-oci8 installed (on ruby 2.4.0p0 (2016-12-24 revision 57164) [x86_64-darwin16]):

 ~ gem list oci8

*** LOCAL GEMS ***

ruby-oci8 (2.2.4.1)

Thank you.

[koic]

There seems to be wrong usage of irb. How about doing it as follows?

% irb -r oci8
> OCI8.new('scott', 'tiger').exec('select * from emp') do |r| puts r.join(','); end

[belgoros]

Failed:

➜  ~ irb -r oci8
2.4.0 :001 > OCI8.new('scott', 'tiger').exec('select * from emp') do |r| puts r.join(','); end
OCIError: ORA-12162: TNS:net service name is incorrectly specified
    from oci8.c:659:in oci8lib_240.bundle
    from /Users/XXX/.rvm/gems/ruby-2.4.0/gems/ruby-oci8-2.2.4.1/lib/oci8/oci8.rb:143:in `initialize'
    from (irb):1:in `new'
    from (irb):1
    from /Users/XXX/.rvm/rubies/ruby-2.4.0/bin/irb:11:in `<main>'

[koic]

It is necessary to correctly write Oracle information in the argument of OCI8.new. That is the information you are actually trying to connect to Oracle. http://www.rubydoc.info/gems/ruby-oci8/2.2.4.1/OCI8#initialize-instance_method

[belgoros]

Weird, I used the same username/password provided in the example from README as well in the oci8.rb:

=== connecting to the local server
  #
  # Set +username+ and +password+ or pass "username/password" as a
  # single argument.
  #
  #   OCI8.new('scott', 'tiger')
  # or
  #   OCI8.new('scott/tiger')

and still have no clues why it fails :(

[koic]

Does Oracle boot on a virtual environment or another server? In that case you also need to specify dbname.

  # === connecting to a remote server
  #
  # Set +username+, +password+ and +dbname+ or pass
  # "username/password@dbname" as a single argument.
  #
  #   OCI8.new('scott', 'tiger', 'orcl.world')
  # or
  #   OCI8.new('scott/tiger@orcl.world')
  #
  # The +dbname+ is a net service name or an easy connectection
  # identifier. The former is a name listed in the file tnsnames.ora.
  # Ask to your DBA if you don't know what it is. The latter has the
  # syntax as "//host:port/service_name".
  #
  #   OCI8.new('scott', 'tiger', '//remote-host:1521/XE')
  # or
  #   OCI8.new('scott/tiger@//remote-host:1521/XE')
  #

https://github.com/kubo/ruby-oci8/blob/ruby-oci8-2.2.4.1/lib/oci8/oci8.rb#L46-L63

[belgoros]

Nope, I have all the stuff on my Mac

[koic]

Is it connected with sqlplus?

% sqlplus scott/tiger

[belgoros]

Here is the response:

~ sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 11 13:55:32 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-12162: TNS:net service name is incorrectly specified

Enter user-name: 

[belgoros]

May be this solution could work ?

[koic]

Hmm. I don't know if that is the cause. Certainly, before using ruby-oci8, it seems necessary to solve the problem of connection to Oracle server. First of all, let connect to Oracle server using sqlplus. AFAIK, If you write a connectable argument of sqlplus in argument of OCI8.new, you can connect from ruby-oci8.

[belgoros]

Certainly, but what is strange is that when trying just to check if ruby-oci8 works locally as needed with the provided example, it failed. And there no any additional information nor settings described.

[kubo]

And there no any additional information nor settings described.

I'll add notice to README saying users need a Oracle database server to which sqlplus can connect. Describing database setting is not easy. It is out of scope of ruby-oci8 documents.

[belgoros]

When I use jruby(which does not use ruby-oci8), I can query a remote Oracle DB without problems.

[kubo]

@Javix How did you connect to the remote Oracle DB by jruby? Could you post the setting?

[belgoros]

Here we go:

rvm rubies

   jruby-9.1.6.0 [ x86_64 ]
=* ruby-2.4.0 [ x86_64 ]

# => - current
# =* - current && default
#  * - default

➜  ruby_drafts rvm use 9.1.6.0
Using /Users/Serguei/.rvm/gems/jruby-9.1.6.0

Here is the ruby file to run:

require 'active_record'

ActiveRecord::Base.establish_connection(
  adapter: 'oracle_enhanced',
  host: 'some.url.preprod.org',
  port: '1531',
  database: 'some_name',
  username: 'username',
  password: 'userpassword'
)
class Atelier < ActiveRecord::Base
  self.table_name = 'ATELIER_ELT_NIVEAU'
end

result = Atelier.first
puts "First: #{result.inspect}"

Run:

➜  ruby_drafts jruby lib/stores_to_newton.rb 
WARNING: Active Record does not support composite primary key.

ATELIER_ELT_NIVEAU has composite primary key. Composite primary key is ignored.

First: #<Atelier org_num_organisation_eln: 2, niv_num_niveau_eln: 2, eln_num_elt_niveau_eln: 1719, aen_type: 1, aen_forfaitaire: "N", aen_magasin: "O", aen_sc: "N">

[kubo]

Thanks. Could you connect to the server by sqlplus?

sqlplus username/userpassword@//some.url.preprod.org:1531/some_name

If it works, you can connect using ruby-oci8 by OCI8.new('username/userpassword@//some.url.preprod.org:1531/some_name') or OCI8.new('username', 'userpassword', '//some.url.preprod.org:1531/some_name').

The syntax is described here.

[belgoros]

@kubo Here is the response for what I ran: sqlplus username/userpassword@//some.url.preprod.org:1531/some_name (sure I replaced the right values :):

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name: 

[kubo]

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Could you check some_name at the last part of //some.url.preprod.org:1531/some_name? The TNS listener is running at some.url.preprod.org:1531 but it doesn't know some_name.

According to your setting, these lines create JDBC url jdbc:oracle:thin:@some.url.preprod.org:1531:some_name, which corresponds to easy connect naming //some.url.preprod.org:1531/some_name.

[belgoros]

@kubo The value of some_nameis the same as in my previous snippet:

ActiveRecord::Base.establish_connection(
  adapter: 'oracle_enhanced',
  host: 'some.url.preprod.org',
  port: '1531',
  database: 'some_name',
  username: 'username',
  password: 'userpassword'
)

So the command I run was as follows:

sqlplus username/userpassword@//some.url.preprod.org:1531/some_name

[kubo]

I have no more idea. When sqlplus cannot connect to the server, ruby-oci8 cannot also. JDBC and OCI on macOS may create a bit different connect descriptor.

[kubo]

@Javix If you have tried to use ruby-oci8 even now, could you check whether sqlplus on other Linux or Windows can connect to the oracle server remotely? Oracle client on macOS sometimes doesn't work on OS upgrade.

[belgoros]

@kubo OK, the above was tested on macOS 10.12.4, I'll take a try on a Windows 10 Family 64x at let you know.

[belgoros]

@kubo Here is what I produced on a Windows 10, 64x:

• installed Ruby (ruby 2.4.2p198 (2017-09-14 revision 59899) [x64-mingw32];
• installed ruby-oci8 and activerecord-oracle_enhanced-adapter:

  
  *** LOCAL GEMS ***

activemodel (5.1.4) activerecord (5.1.4) activerecord-oracle_enhanced-adapter (1.8.2) activesupport (5.1.4) arel (8.0.0) bigdecimal (default: 1.3.0) concurrent-ruby (1.0.5) did_you_mean (1.1.0) i18n (0.9.0) io-console (default: 0.4.6) json (default: 2.0.4) minitest (5.10.1) net-telnet (0.1.1) openssl (default: 2.0.5) power_assert (0.4.1) psych (default: 2.2.2) rake (12.0.0) rdoc (default: 5.0.0) ruby-oci8 (2.2.4.1 x64-mingw32) ruby-plsql (0.6.0) test-unit (3.2.3) thread_safe (0.3.6) tzinfo (1.2.3) xmlrpc (0.2.1)

When I ran `irb -r oci8`, here is the error I got:

C:\Users\Serge>irb -r oci8 C:/Ruby24-x64/lib/ruby/2.4.0/rubygems/core_ext/kernel_require.rb:55:in `require':LoadError: OCI.DLL: 126(Le module sp�cifi� est introuvable. )

Sorry, the OS is in French, the message means that OCI.DLL module could not be found.
Whe I ran 

ruby -r oci8 -e "OCI8.new('scott', 'tiger').exec('select * from emp') do |r| puts r.join(','); end"

the error I got is as follows:

irb(main):001:0> ruby -r oci8 -e "OCI8.new('scott', 'tiger').exec('select * from emp') do |r| puts r.join(','); end" SyntaxError: (irb):1: syntax error, unexpected tIDENTIFIER, expecting keyword_do or '{' or '(' ruby -r oci8 -e "OCI8.new('scott', 'tiger' ^ (irb):1: syntax error, unexpected tSTRING_BEG, expecting keyword_do or '{' or '(' ruby -r oci8 -e "OCI8.new('scott', 'tiger').exe ^ from C:/Ruby24-x64/bin/irb.cmd:19:in `

'

Hope this helps to catch the problem if there is one.

[kubo]

If you use Oracle instant client, you need to install the Visual C++ runtime library. See: http://www.rubydoc.info/github/kubo/ruby-oci8/file/docs/install-instant-client.md#Windows