Asp Forum - Help with Ruby < - > Oracle Connectivity

Peter Bailey

5/30/2007 1:31:00 PM

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

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

14 Answers

Brian Candler

5/30/2007 1:51:00 PM

On Wed, May 30, 2007 at 10:30:40PM +0900, Peter Bailey wrote:
> 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

5/30/2007 2:12:00 PM

Brian Candler wrote:
> On Wed, May 30, 2007 at 10:30:40PM +0900, Peter Bailey 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

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

Brian Candler

5/30/2007 2:41:00 PM

On Wed, May 30, 2007 at 11:12:02PM +0900, Peter Bailey 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.ruby...

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

5/31/2007 6:46:00 PM

Brian Candler wrote:
> On Wed, May 30, 2007 at 11:12:02PM +0900, Peter Bailey 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.ruby...
>
> 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

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

Drew Olson

5/31/2007 7:01:00 PM

Peter Bailey 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

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

Peter Bailey

5/31/2007 7:05:00 PM

Drew Olson wrote:
> Peter Bailey 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.

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

Brian Candler

5/31/2007 7:27:00 PM

On Fri, Jun 01, 2007 at 03:45:58AM +0900, Peter Bailey wrote:
> 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.

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

6/1/2007 11:45:00 AM

Brian Candler wrote:
> On Fri, Jun 01, 2007 at 03:45:58AM +0900, Peter Bailey 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.

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

Brian Candler

6/1/2007 1:42:00 PM

On Fri, Jun 01, 2007 at 08:44:36PM +0900, Peter Bailey wrote:
> 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.

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/b142...

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

6/1/2007 2:55:00 PM

Brian Candler wrote:
> On Fri, Jun 01, 2007 at 08:44:36PM +0900, Peter Bailey 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
> :-)
>
> There's good documentation online at
> http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b142...
>
> 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

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

comp.lang.ruby

Help with Ruby < - > Oracle Connectivity

Peter Bailey

Brian Candler

Peter Bailey

Brian Candler

Peter Bailey

Drew Olson

Peter Bailey

Brian Candler

Peter Bailey

Brian Candler

Peter Bailey

x Login to ForumsZone