Help with Ruby < - > Oracle Connectivity

[Peter Bailey]

Hello,
I can't seem to get connected to an Oracle server here at my company.
I'm using the oci8 gem along with DBI. Here's what I code and here's
what I get. . . .

require 'oci8'
require 'dbi'

begin
# connect to the Oracle server
dbh =
DBI.connect("DBI:OCI8:ORCL:graphicsdb-prod.bna.com/grpprod.bna.com",
"user", "passw")
...

I get:

An error occurred
Error code: 12154
Error message: ORA-12154: TNS:could not resolve the connect identifier
specified

Program exited with code 0

I've tried it with and without the "ORCL" in the connect line, but, with
the same results.

Thanks,
Peter

 

[Brian Candler]

I can't seem to get connected to an Oracle server here at my company.
I'm using the oci8 gem along with DBI. Here's what I code and here's
what I get. . . .

require 'oci8'
require 'dbi'

begin
# connect to the Oracle server
dbh =
DBI.connect("DBI:OCI8:ORCL:graphicsdb-prod.bna.com/grpprod.bna.com",
"user", "passw")
...

Check your tnsnames.ora file.

If you are using oracle-instantclient library and ruby-oci8-1.0.0-rc1/rc2,
then you are allowed to use "//hostname/dbname" as the connect string.
Otherwise you must use a database name which matches an entry in
tnsnames.ora

I don't use DBI, but here are some ruby-oci8 examples which work for me:

$ irb1.8
irb(main):001:0> require 'oci8'
=> true
irb(main):002:0> c = OCI8.new('candlerb','XXXXXXXX','dcfgdb')
=> #<OCI8:0xb6b3fb00 @privilege=nil, @svc=#<OCISvcCtx:0xb6b3fab0>, @ctx=[0, #<Mutex:0xb6b3fac4 @locked=false, @waiting=[]>, nil, 65535], @prefetch_rows=nil>
irb(main):003:0>

where /etc/tnsnames.ora contains:

DCFGDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DCFGDB)
)
)

Alternatively,

irb(main):003:0> c = OCI8.new('candlerb','XXXXXXXX','//db.example.com/dcfgdb')
=> #<OCI8:0xb6b3b5dc @privilege=nil, @svc=#<OCISvcCtx:0xb6b3b58c>, @ctx=[0, #<Mutex:0xb6b3b5a0 @locked=false, @waiting=[]>, nil, 65535], @prefetch_rows=nil>
irb(main):004:0>

because I'm using oracle-instantclient.

If you can get these direct oci8 examples to work, you should find it easier
to make a DBI connect string which works.

HTH,

Brian.

 

[Peter Bailey]

DBI.connect("DBI:OCI8:ORCL:graphicsdb-prod.bna.com/grpprod.bna.com",
"user", "passw")
...

Check your tnsnames.ora file.

If you are using oracle-instantclient library and
ruby-oci8-1.0.0-rc1/rc2,
then you are allowed to use "//hostname/dbname" as the connect string.
Otherwise you must use a database name which matches an entry in
tnsnames.ora

I don't use DBI, but here are some ruby-oci8 examples which work for me:

$ irb1.8
irb(main):001:0> require 'oci8'
=> true
irb(main):002:0> c = OCI8.new('candlerb','XXXXXXXX','dcfgdb')
=> #<OCI8:0xb6b3fb00 @privilege=nil, @svc=#<OCISvcCtx:0xb6b3fab0>,
@ctx=[0, #<Mutex:0xb6b3fac4 @locked=false, @waiting=[]>, nil, 65535],
@prefetch_rows=nil>
irb(main):003:0>

where /etc/tnsnames.ora contains:

DCFGDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DCFGDB)
)
)

Alternatively,

irb(main):003:0> c =
OCI8.new('candlerb','XXXXXXXX','//db.example.com/dcfgdb')
=> #<OCI8:0xb6b3b5dc @privilege=nil, @svc=#<OCISvcCtx:0xb6b3b58c>,
@ctx=[0, #<Mutex:0xb6b3b5a0 @locked=false, @waiting=[]>, nil, 65535],
@prefetch_rows=nil>
irb(main):004:0>

because I'm using oracle-instantclient.

If you can get these direct oci8 examples to work, you should find it
easier
to make a DBI connect string which works.

HTH,

Brian.

Thanks, Brian. I had one of our company DBA guys come down and installed
the Oracle 10g client on my server, so, I don't know whether or not it's
the "instant client," but I think not. I know that I'm able to use and
connect with my database using SQLPlus, which came with the client. My
"tnsnames.ora" file shows this:
...
(ADDRESS = (PROTOCOL = TCP)(HOST = graphicsdb-prod.bna.com)(PORT =
1521))
...

I'm using DBI here, but, I'm ignorant. I don't know whether I need to
use it or not. It's all I've found in googling around to do this. But,
anyway, nothing you suggest is working for me.

-Peter

 

[Brian Candler]

I know that I'm able to use and
connect with my database using SQLPlus, which came with the client. My
"tnsnames.ora" file shows this:
...
(ADDRESS = (PROTOCOL = TCP)(HOST = graphicsdb-prod.bna.com)(PORT =
1521))
...

Unfortunately, you've missed out the important bit, which is what goes where
the first "..." is. That's the service name, and that's the name you use to
refer to the host when connecting to it.

I'm using DBI here, but, I'm ignorant. I don't know whether I need to
use it or not. It's all I've found in googling around to do this.

The homepage for ruby-oci8 is at
http://ruby-oci8.rubyforge.org/

You can use this directly - it's a simple enough API. Using the DBI layer
around this means that in theory you can write code which talks to databases
other than Oracle. But that's only true if you don't use any Oracle-specific
SQL.

If you want an OO abstraction layer, look at ActiveRecord.

But, anyway, nothing you suggest is working for me.

"Nothing is working" is not helpful. Unless you show exactly what you tried,
and exactly what error(s) you got - cut and paste - then I'm not going to be
able to help you.

Showing a working sqlplus command line would also be extremely helpful.
Basically, the same parameters you give there should be usable in your oci8
connect string.

Brian.

 

[Peter Bailey]

Unfortunately, you've missed out the important bit, which is what goes
where
the first "..." is. That's the service name, and that's the name you use
to
refer to the host when connecting to it.


The homepage for ruby-oci8 is at
http://ruby-oci8.rubyforge.org/

You can use this directly - it's a simple enough API. Using the DBI
layer
around this means that in theory you can write code which talks to
databases
other than Oracle. But that's only true if you don't use any
Oracle-specific
SQL.

If you want an OO abstraction layer, look at ActiveRecord.


"Nothing is working" is not helpful. Unless you show exactly what you
tried,
and exactly what error(s) you got - cut and paste - then I'm not going
to be
able to help you.

Showing a working sqlplus command line would also be extremely helpful.
Basically, the same parameters you give there should be usable in your
oci8
connect string.

Brian.

OK, Brian. Thanks for your help. Well, here's the dinky script I'm
trying to use. This script was borrowed, in fact, from the oci8 site.

require 'oci8'
require 'dbi'

begin
# connect to the Oracle server
dbh = DBI.connect("DBI:OCI8:ORCL:graphicsdb-
prod.bna.com/grpprod.bna.com", "user", "passw")

# get server version string and display it
row = dbh.select_one("SELECT VERSION()")
puts "Server version: " + row[0]
rescue DBI:atabaseError => e
puts "An error occurred"
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
ensure
# disconnect from server
dbh.disconnect if dbh
end

And, I get this:
An error occurred
Error code: 12154
Error message: ORA-12154: TNS:could not resolve the connect identifier
specified

Program exited with code 0

And, here's what I've tried with SQLPlus. It's not doing the same thing
as above, but it's interrogating the same database.

First, I simply connected to the database using the SQLPlus initial GUI.
Then,

SQL> SELECT file_size from GRAPHIC.RENDITION where image_name = 'zc1'
and format_name = 'pdf';

FILE_SIZE

 

[Drew Olson]

First, I simply connected to the database using the SQLPlus initial GUI.

This is the part you should be interested in. Make sure you are using
the EXACT same hostname, port and SSID with oci8 as you are with
SQLPlus. The error you are seeing means the SSID you are specifying can
not be found on the machine to which you are connecting.

- Drew

 

[Peter Bailey]

This is the part you should be interested in. Make sure you are using
the EXACT same hostname, port and SSID with oci8 as you are with
SQLPlus. The error you are seeing means the SSID you are specifying can
not be found on the machine to which you are connecting.

- Drew

Connecting with SQLPlus, I used "grpprod" as my target database. I put
that into the oci8 script, the same one as above, and I still get the
same error.

 

[Brian Candler]

OK, Brian. Thanks for your help. Well, here's the dinky script I'm
trying to use. This script was borrowed, in fact, from the oci8 site.

require 'oci8'
require 'dbi'

begin
# connect to the Oracle server
dbh = DBI.connect("DBI:OCI8:ORCL:graphicsdb-
prod.bna.com/grpprod.bna.com", "user", "passw")

# get server version string and display it
row = dbh.select_one("SELECT VERSION()")
puts "Server version: " + row[0]
rescue DBI:atabaseError => e
puts "An error occurred"
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
ensure
# disconnect from server
dbh.disconnect if dbh
end

And, I get this:
An error occurred
Error code: 12154
Error message: ORA-12154: TNS:could not resolve the connect identifier
specified

Program exited with code 0

And, here's what I've tried with SQLPlus. It's not doing the same thing
as above, but it's interrogating the same database.

First, I simply connected to the database using the SQLPlus initial GUI.

Can you connect using the sqlplus command line? I didn't even know that
sqlplus came in a GUI variant. (Are you sure it's SQLPlus, and not something
like Toad?)

Your later post says that you connect to "grpprod". In that case, try the
pure oci8 script I posted before, using "grpprod" as the database name. And
try your DBI script with

DBI.connect("DBI:OCI8:grpprod","user","passw")

Can you show your entire /etc/tnsnames.ora ?

Brian.

 

[Peter Bailey]

Can you connect using the sqlplus command line? I didn't even know that
sqlplus came in a GUI variant. (Are you sure it's SQLPlus, and not
something
like Toad?)

Your later post says that you connect to "grpprod". In that case, try
the
pure oci8 script I posted before, using "grpprod" as the database name.
And
try your DBI script with

DBI.connect("DBI:OCI8:grpprod","user","passw")

Can you show your entire /etc/tnsnames.ora ?

Brian.

Here's my tnsnames.ora file, Brian.

# tnsnames.ora Network Configuration File:
E:\live\oracle\product\10.2.0\client_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
GRPPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = graphicsdb-prod.bna.com)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = GRPPROD.bna.com)
)
)

I put in what you suggested above and, it does seem to be actually
talking to the database, and, it's respecting my script's error
presentations.

require 'oci8'
require 'dbi'
begin
# connect to the Oracle server
#dbh =
OCI8.new('oracleuser','oracle2user','//graphicsdb-prod.bna.com/grpprod.bna.com"')

#dbh =
DBI.connect("DBI:OCI8:ORCL:graphicsdb-prod.bna.com/grpprod.bna.com",
"orcauser", "orca2user")
dbh = DBI.connect("DBI:OCI8:grpprod","orcauser","orca2user")

# get server version string and display it
row = dbh.select_one("SELECT VERSION()")
puts "Server version: " + row[0]
rescue DBI:atabaseError => e
puts "An error occurred"
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
ensure
# disconnect from server
dbh.disconnect if dbh
end

With the above, I get:

An error occurred
Error code: 923
Error message: ORA-00923: FROM keyword not found where expected

Program exited with code 0

which is exactly what the script said to do, to report the exact errors,
number and all.

 

[Brian Candler]

I put in what you suggested above and, it does seem to be actually
talking to the database, and, it's respecting my script's error
presentations.

require 'oci8'
require 'dbi'
begin
# connect to the Oracle server
#dbh =
OCI8.new('oracleuser','oracle2user','//graphicsdb-prod.bna.com/grpprod.bna.com"')

#dbh =
DBI.connect("DBI:OCI8:ORCL:graphicsdb-prod.bna.com/grpprod.bna.com",
"orcauser", "orca2user")
dbh = DBI.connect("DBI:OCI8:grpprod","orcauser","orca2user")

# get server version string and display it
row = dbh.select_one("SELECT VERSION()")
puts "Server version: " + row[0]
rescue DBI:atabaseError => e
puts "An error occurred"
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
ensure
# disconnect from server
dbh.disconnect if dbh
end

With the above, I get:

An error occurred
Error code: 923
Error message: ORA-00923: FROM keyword not found where expected

Program exited with code 0

which is exactly what the script said to do, to report the exact errors,
number and all.

Your login has been successful. Now you just need to learn Oracle SQL

There's good documentation online at
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm

As the error says, you are missing the FROM keyword. Try the following:

SELECT 1+1 FROM DUAL

as a very heavyweight desk calculator. Also, a quick Google suggests that

SELECT * FROM v$version

will report the Oracle software component versions.

Good luck,

Brian.

 

[Peter Bailey]

Your login has been successful. Now you just need to learn Oracle SQL


There's good documentation online at
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm

As the error says, you are missing the FROM keyword. Try the following:

SELECT 1+1 FROM DUAL

as a very heavyweight desk calculator. Also, a quick Google suggests
that

SELECT * FROM v$version

will report the Oracle software component versions.

Good luck,

Brian.

Success! Thanks, Brian! I got something. Here's what I put in there, at
your suggestion.

row = dbh.select_one("SELECT * FROM v$version")

And here's what I got:

Server version: Oracle Database 10g Release 10.2.0.2.0 - 64bit
Production

Program exited with code 0

I'm a bloody genius. What can I say? So, from what you've shown me, it
appears that the SQL stuff is inside those parentheses, like above.
Right? In SQLPlus, I have to end every instruction with a semi-colon,
Perl-like. But, inside Ruby, that doesn't seem to be necessary.

Thanks again, Brian
-Peter

 

[Brian Candler]

I'm a bloody genius. What can I say? So, from what you've shown me, it
appears that the SQL stuff is inside those parentheses, like above.
Right? In SQLPlus, I have to end every instruction with a semi-colon,
Perl-like. But, inside Ruby, that doesn't seem to be necessary.

If you google for "ruby dbi tutorial", the first hit is
http://www.kitebird.com/articles/ruby-dbi.html
which should get you started.

But unless you're wedded to working directly at the SQL layer, have a look
at ActiveRecord too. It rocks.

(No offence to the other OO-DB mappings out there, but this is the one which
Rails uses, and so there's lots of good documentation you can buy. In my
opinion, $40 is well spent if it saves you half-an-hour of head scratching)

Brian.

 

[Peter Bailey]

If you google for "ruby dbi tutorial", the first hit is
http://www.kitebird.com/articles/ruby-dbi.html
which should get you started.

But unless you're wedded to working directly at the SQL layer, have a
look
at ActiveRecord too. It rocks.

(No offence to the other OO-DB mappings out there, but this is the one
which
Rails uses, and so there's lots of good documentation you can buy. In my
opinion, $40 is well spent if it saves you half-an-hour of head
scratching)

Brian.

Thanks, Brian. Yes, that Oracle doc. you pointed me to looks pretty
in-depth. Regarding Active Record, yes, I've certainly read that it
rocks, mainly as part of Rails. Can it be used in a non-Rails way, too?
I'm perfectly fine with shelling out some bucks for a good book.

Thanks again,
Peter

 

[Brian Candler]

Regarding Active Record, yes, I've certainly read that it
rocks, mainly as part of Rails. Can it be used in a non-Rails way, too?

Absolutely. That's how I first started using it.

There are plenty of intros and blogs if you just google for them, e.g.
http://www.it-eye.nl/weblog/2006/01/06/starting-with-ruby-and-oracle/

And AR's own API documentation is pretty good too:
http://ar.rubyonrails.com/

I'm perfectly fine with shelling out some bucks for a good book.

I have "Agile Web Development with Rails" and I'd strongly recommend it,
even if you're only interested in ActiveRecord, as it has several chapters
dedicated to it.

Regards,

Brian.

 

[Peter Bailey]

Absolutely. That's how I first started using it.

There are plenty of intros and blogs if you just google for them, e.g.
http://www.it-eye.nl/weblog/2006/01/06/starting-with-ruby-and-oracle/

And AR's own API documentation is pretty good too:
http://ar.rubyonrails.com/


I have "Agile Web Development with Rails" and I'd strongly recommend it,
even if you're only interested in ActiveRecord, as it has several
chapters
dedicated to it.

Regards,

Brian.

Yup, I've got that book, too, and it looks great, from what I've read so
far. But, I just haven't had the time yet to dive in. But, I definitely
going to look into Active Rails, even before I get into Rails, which I
plan some time in the future.

Cheers,
Peter