Help with Ruby < - > Oracle Connectivity


#1

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


#2

On Wed, May 30, 2007 at 10:30:40PM +0900, Peter B. wrote:

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.


#3

Brian C. wrote:

On Wed, May 30, 2007 at 10:30:40PM +0900, Peter B. wrote:

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


#4

On Wed, May 30, 2007 at 11:12:02PM +0900, Peter B. wrote:

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.


#5

Brian C. wrote:

On Wed, May 30, 2007 at 11:12:02PM +0900, Peter B. wrote:

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.

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::DatabaseError => 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

 62116

#6

Peter B. wrote:

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

#7

On Fri, Jun 01, 2007 at 03:45:58AM +0900, Peter B. wrote:

end
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.


#8

Drew O. wrote:

Peter B. wrote:

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

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.


#9

Brian C. wrote:

On Fri, Jun 01, 2007 at 03:45:58AM +0900, Peter B. wrote:

end
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.

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::DatabaseError => 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.


#10

On Fri, Jun 01, 2007 at 08:44:36PM +0900, Peter B. wrote:

 puts "Error code: #{e.err}"

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
:slight_smile:

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.


#11

Brian C. wrote:

On Fri, Jun 01, 2007 at 08:44:36PM +0900, Peter B. wrote:

 puts "Error code: #{e.err}"

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
:slight_smile:

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


#12

Brian C. wrote:

On Fri, Jun 01, 2007 at 11:55:29PM +0900, Peter B. wrote:

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.

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


#13

On Sat, Jun 02, 2007 at 12:35:35AM +0900, Peter B. wrote:

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 D. 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.


#14

Brian C. wrote:

On Sat, Jun 02, 2007 at 12:35:35AM +0900, Peter B. wrote:

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 D. 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


#15

On Fri, Jun 01, 2007 at 11:55:29PM +0900, Peter B. wrote:

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.