Confused about OCI8

ruby-talk
1

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. :o)

Eddy

2

*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, eddy.pagotto@gmail.com <eddy.pagotto@gmail.com>:

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. :o)

Eddy

--
Best regards,
Jesse

3

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.

···

On Thu, Feb 21, 2008 at 1:20 AM, <eddy.pagotto@gmail.com> wrote:

4

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

···

On Feb 21, 4:06 am, Jesse Hu <yiz...@gmail.com> wrote:

[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, eddy.pago...@gmail.com <eddy.pago...@gmail.com>:

--
Best regards,
Jesse

5

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

···

On Feb 21, 10:12 am, KUBO Takehiro <k...@jiubao.org> wrote:

Hi,

On Thu, Feb 21, 2008 at 1:20 AM, <eddy.pago...@gmail.com> wrote:
> 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.

6

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

···

On Feb 21, 8:58 am, eddy.pago...@gmail.com wrote:

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

On Feb 21, 4:06 am, Jesse Hu <yiz...@gmail.com> wrote:

> [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, eddy.pago...@gmail.com <eddy.pago...@gmail.com>:

> --
> Best regards,
> Jesse

7

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>")

<tns_name> is a name defined in TNSNAMES.ORA.
Tns name may be differnet name with 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.

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

···

On Thu, Feb 21, 2008 at 6:59 PM, <eddy.pagotto@gmail.com> wrote:

8

OK,
all work, finally! :o)
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

···

On Feb 21, 11:25 am, KUBO Takehiro <k...@jiubao.org> wrote:

HI,

On Thu, Feb 21, 2008 at 6:59 PM, <eddy.pago...@gmail.com> wrote:
> 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>")

<tns_name> is a name defined in TNSNAMES.ORA.
Tns name may be differnet name with 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.

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