DBI, OCI8 and the 'tables' method

[Daniel Berger]

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

 

[Michael Neumann]

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.

Regards,

Michael

 

[KUBO Takehiro]

Hello,

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.

 

[Michael Neumann]

Hello,

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?

Regards,

Michael

 

[KUBO Takehiro]

Hello,

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.