Why isn't querying a JDBC-compliant database from Oracle as easy as pie?

Question

Ok, so it's almost as easy as pie already. But it really should be as easier than it is.

I think I should be able to connect to another database just by putting a JDBC connection string into TNSNAMES. Every database vendor has a type-4 JDBC driver and there's usually a good, free alternative.

With Oracle being such keen Java fans, and with a JVM built-in to the database I'd have thought a JDBC-based linking technology would have been a no-brainer. It seems a natural extension to have a JDBC connection string in TNSNAMES and everything would "just work" - you could "sql*plus" to anything.

But it doesn't work this way. If you want to connect to another non-Oracle database You have to buy something called Oracle Gateways or mess around with ODBC (through something called Generic Connectivity).

[Originality warning... This is related to a previous question of mine but someone suggested I enter a supplementary comment as a separate question. Who am I to argue?]

Answer 1

I certainly think the question was somewhat rhetorical and to be taken with a large pinch of salt. :-)

In that spirit, a suitably flipant answer might be "because they don't want you to use anyone else's database"?

Answer 2

It's a real question - perhaps slightly jokey but certainly not rhetorical. It is entirely in Oracle's interest to make it really easy to access other people's data. At the moment there's lots of ways to do it but none sufficiently straightforward. There's a JVM in the database and JDBC drivers to every other database - it should "just work" so I can only assume it hasn't been made to "just work" for a reason.

My question is whether anyone knows that reason. Does anyone?

Answer 3

The answer is the same as for the following questions:

1. Why doesn't Oracle provide an efficient way of unloading data into a non-proprietary format (e.g. comma-delimited or XML)
2. Why do most Oracle non-DB products only work with the Oracle RDBMS? (without having to use Oracle Database Gateways)

You've ever heard of the concept of Vendor lock-in?

Answer 4

TNS is a bit of a mess, imho. It seems to behave inconsistently between different platforms, in my (admittedly limited) experience.

Far be it for me to defend Oracle, but I've had no problems with the JDBC thin driver. (If you read the oracle jdbc docs, they discourage you from using oci unless you have a very good reason.

Pair it with JNDI (on a j2ee app server), and all your connection management problems are taken care of.