DBI, OCI8 and the 'tables' method

ruby-talk
1

Hi all,

Ruby 1.8.1
Windows XP Pro/Solaris 9
Oracle 9.2.x
OCI8 0.1.7
DBI 0.0.22

The tables() method is returning an empty array when I use DBI and the
OCI8 driver.

require "dbi"
dbh = DBI.connect(dsn,user,passwd)
p dbh.tables # empty with OCI8
dbh.disconnect

If I use the old Oracle driver, it returns the table names as
expected. Is this part of the ‘array_fetch’ issue? Or some other bug
with OCI8 and/or DBI?

Thanks.

Dan

2

I've had a quick look into OCI8 0.1.7. There's no table method defined,
and the fall-back behaviour is to return .

This is how DBD::Oracle implements the tables method:

  def tables
    stmt = execute("SELECT table_name FROM user_tables")
    rows = stmt.fetch_all ||
    stmt.finish
    rows.collect {|row| row[0]}
  end

Try to add this piece of code to class Database in OCI8.rb, and if it
works, please send the patch to the maintainer of OCI8.

Regards,

  Michael

···

On Sat, May 08, 2004 at 01:33:55AM +0900, Daniel Berger wrote:

Hi all,

Ruby 1.8.1
Windows XP Pro/Solaris 9
Oracle 9.2.x
OCI8 0.1.7
DBI 0.0.22

The tables() method is returning an empty array when I use DBI and the
OCI8 driver.

3

Hello,

Michael Neumann mneumann@ntecs.de writes:

Hi all,

Ruby 1.8.1
Windows XP Pro/Solaris 9
Oracle 9.2.x
OCI8 0.1.7
DBI 0.0.22

The tables() method is returning an empty array when I use DBI and the
OCI8 driver.

I’ve had a quick look into OCI8 0.1.7. There’s no table method defined,
and the fall-back behaviour is to return .

This is how DBD::Oracle implements the tables method:

def tables
stmt = execute(“SELECT table_name FROM user_tables”)
rows = stmt.fetch_all ||
stmt.finish
rows.collect {|row| row[0]}
end

Try to add this piece of code to class Database in OCI8.rb, and if it
works, please send the patch to the maintainer of OCI8.

Sorry too late to reply.

I’ll include your code to the next release of ruby-oci8.

But I’ll change “SELECT table_name FROM user_tables” to
“SELECT object_name FROM user_objects where object_type in (‘TABLE’, ‘VIEW’)”.
user_tables returns tables only but DBD_SPEC says: Returns an
(({Array})) of all tables and views.

cheers.

···

On Sat, May 08, 2004 at 01:33:55AM +0900, Daniel Berger wrote:

–
KUBO Takehiro
email: kubo@jiubao.org
web: http://www.jiubao.org
GnuPG fingerprint = 5F7B C8EF CA16 57D0 FDE1 9F47 C001 1F93 AC08 2262

4

Should I do the same in DBD::Oracle (OCI7 module)?
Does this work in Oracle 7 too?

Regards,

  Michael

···

On Thu, May 13, 2004 at 09:14:10PM +0900, KUBO Takehiro wrote:

Hello,

Michael Neumann <mneumann@ntecs.de> writes:

> On Sat, May 08, 2004 at 01:33:55AM +0900, Daniel Berger wrote:
>> Hi all,
>>
>> Ruby 1.8.1
>> Windows XP Pro/Solaris 9
>> Oracle 9.2.x
>> OCI8 0.1.7
>> DBI 0.0.22
>>
>> The tables() method is returning an empty array when I use DBI and the
>> OCI8 driver.
>
> I've had a quick look into OCI8 0.1.7. There's no table method defined,
> and the fall-back behaviour is to return .
>
> This is how DBD::Oracle implements the tables method:
>
> def tables
> stmt = execute("SELECT table_name FROM user_tables")
> rows = stmt.fetch_all ||
> stmt.finish
> rows.collect {|row| row[0]}
> end
>
> Try to add this piece of code to class Database in OCI8.rb, and if it
> works, please send the patch to the maintainer of OCI8.

Sorry too late to reply.

I'll include your code to the next release of ruby-oci8.

But I'll change "SELECT table_name FROM user_tables" to
"SELECT object_name FROM user_objects where object_type in ('TABLE', 'VIEW')".
user_tables returns tables only but DBD_SPEC says: Returns an
(({Array})) of all tables and views.

5

Michael Neumann mneumann@ntecs.de writes:

···

On Thu, May 13, 2004 at 09:14:10PM +0900, KUBO Takehiro wrote:

Hello,

Michael Neumann mneumann@ntecs.de writes:

On Sat, May 08, 2004 at 01:33:55AM +0900, Daniel Berger wrote:

Hi all,

Ruby 1.8.1
Windows XP Pro/Solaris 9
Oracle 9.2.x
OCI8 0.1.7
DBI 0.0.22

The tables() method is returning an empty array when I use DBI and the
OCI8 driver.

I’ve had a quick look into OCI8 0.1.7. There’s no table method defined,
and the fall-back behaviour is to return .

This is how DBD::Oracle implements the tables method:

def tables
stmt = execute(“SELECT table_name FROM user_tables”)
rows = stmt.fetch_all ||
stmt.finish
rows.collect {|row| row[0]}
end

Try to add this piece of code to class Database in OCI8.rb, and if it
works, please send the patch to the maintainer of OCI8.

Sorry too late to reply.

I’ll include your code to the next release of ruby-oci8.

But I’ll change “SELECT table_name FROM user_tables” to
“SELECT object_name FROM user_objects where object_type in (‘TABLE’, ‘VIEW’)”.
user_tables returns tables only but DBD_SPEC says: Returns an
(({Array})) of all tables and views.

Should I do the same in DBD::Oracle (OCI7 module)?
Does this work in Oracle 7 too?

Yes.
I know Oracle7.3 has user_objects. I have also looked in a pdf
ducument “Oracle7 Server Migration, Release 7.3” and find that
user_objects was changed in Oracle 7.0 and not changed since then.
So it works in Oracle 7 too.

–
KUBO Takehiro
email: kubo@jiubao.org
web: http://www.jiubao.org
GnuPG fingerprint = 5F7B C8EF CA16 57D0 FDE1 9F47 C001 1F93 AC08 2262