Confused about OCI8

[eddy.pagotto]

Hi folks,
I'm studying Ruby converting old Java programs.
I'm encoutering many difficults connecting to a Oracle 8.0.6 box (say
responding at 10.0.0.22).
With Java I simply connect using this infos:

host: 10.0.0.22
SID: TEST
username: user
password: pwd10

and the relative JAR library for Oracle 8.
With Ruby (version 1.8.6/Win32) I installed the OCI8, then I copied
the necessary DLL libraries
from a Oracle/bin directory (such as OCI.DLL) because I don't have the
Oracle client on my machine,
and the Instant client I can download from Oracle's site is the 11g
version which doesn't support Oracle 8.
So, finally I tried some code.
First an example from the Oracle's site:

ruby -r oci8 -e "OCI8.new('user', 'pwd10', '//10.0.0.22:1521/
TEST').exec('SELECT * FROM test_table
WHERE rownum = 1') do |r| puts r.join(' | '); end"

But this error occured:

env.c:257:in oci8lib.so: Error while trying to retrieve text for error
ORA-06401 (OCIError)
from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:228:in
`initialize'
from -e:1:in `new'
from -e:1


Second, I tried this other example:

require 'oci8'

conn = OCI8.new('user', 'pwd10', '//10.0.0.22/TEST')
cursor = conn.exec('SELECT * FROM test_table WHERE rownum = 1')

while r = cursor.fetch()
puts r.join(',')
end

cursor.close
conn.logoff

But this error occured:

env.c:257:in oci8lib.so: Error while trying to retrieve text for
error ORA-12154 (OCIError)
from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:228:in
`initialize'
from Test.rb:3:in `new'
from Test.rb:3

I already look for documentation crawling Google, but it didn't find
anything useful.
Where is my fault?
Thanks in advance for any suggestion to heal my first Ruby-ache. )

Eddy

 

[Jesse Hu]

[Note: parts of this message were removed to make it a legal post.]

*TNS-12154 TNS:could not resolve service name.*

Can you connect to the Oracle 8 db using sqlplus with this connection string
'//10.0.0.22/TEST"? The service_name of the db is "TEST"?

 

[eddy.pagotto]

No, I cannot connect using the string //10.0.0.22/TEST.
Maybe I'm understanding: OCI8 uses the SQLPlus approach for
connections to the DB,
which is different from JDBC. Am I right or not?
So, could it be a DB configuration problem? If I'm able to connect
with SQLPlus
I'm able to connect with OCI8?
Best regards,

Eddy

 

[eddy.pagotto]

In the meanshile I added this to the TNSNAMES.ORA
on a machine with SQLPlus:

TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.22)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST)
)
)

Using "TEST" as connection string, SQLPlus works.
But, using OCI8, how can I use the TNSNAMES.ORA?
Thanks in advance for any help to solve this doubt,

Eddy

No, I cannot connect using the string //10.0.0.22/TEST.
Maybe I'm understanding: OCI8 uses the SQLPlus approach for
connections to the DB,
which is different from JDBC. Am I right or not?
So, could it be a DB configuration problem? If I'm able to connect
with SQLPlus
I'm able to connect with OCI8?
Best regards,

Eddy

[Note: parts of this message were removed to make it a legal post.]

*TNS-12154 TNS:could not resolve service name.*

Can you connect to the Oracle 8 db using sqlplus with this connection string
'//10.0.0.22/TEST"? The service_name of the db is "TEST"?

2008/2/21, (e-mail address removed) <[email protected]>:

 

[KUBO Takehiro]

Hi,

With Ruby (version 1.8.6/Win32) I installed the OCI8, then I copied
the necessary DLL libraries
from a Oracle/bin directory (such as OCI.DLL) because I don't have the
Oracle client on my machine,
and the Instant client I can download from Oracle's site is the 11g
version which doesn't support Oracle 8.

Full client's OCI.DLL won't work if it is copied to outside the installed
directory. You need to install the Oracle client on your machine.
It may work by copying all files under ORACLE_HOME and all
registry keys under \\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE.
But I'm not sure that is all.

So, finally I tried some code.
First an example from the Oracle's site:

ruby -r oci8 -e "OCI8.new('user', 'pwd10', '//10.0.0.22:1521/
TEST').exec('SELECT * FROM test_table
WHERE rownum = 1') do |r| puts r.join(' | '); end"

'//10.0.0.22:1521/TEST' is a new feature of Oracle 10g.
It won't work on older Oracle versions.

 

[eddy.pagotto]

OK,
to summarize:
1. I have to install the full version of Oracle client
2. I have to add the service name to TNSNAMES.ORA
3. To test the connection I will use OCI8.new("<user>", "<password>",
"<service_name>")
So, with Oracle 8 and 9, I cannot use the Instant client 10.x because
I can only use the //<server>/<service_name> form only with 10g
database.
Is it all right?
Thanks,

Eddy

 

[KUBO Takehiro]

HI,

OK,
to summarize:
1. I have to install the full version of Oracle client
Yes.

2. I have to add the service name to TNSNAMES.ORA
Yes.

3. To test the connection I will use OCI8.new("<user>", "<password>",
"<service_name>")

3. To test the connection I will use OCI8.new("<user>", "<password>",
"<tns_name>")

So, with Oracle 8 and 9, I cannot use the Instant client 10.x because
I can only use the //<server>/<service_name> form only with 10g
database.

10g client, exactly speaking.
10g client can use //<server>/<service_name> to connect to 9i databases.

 

[eddy.pagotto]

OK,
all work, finally! )
I installed the Oracle client 9.2 on my machine, then configured
the TNSNAMES.ORA with the right service name, and both SQL*Plus
and my Ruby test app work.
Thanks a lot for your support Kubo, and thanks to Jesse for the first
important suggestion.
Best regards,

Eddy