JDBC DatabaseMetaData.getColumns() returns duplicate columns

Question

I'm busy on a piece of code to get alle the column names of a table from an Oracle database. The code I came up with looks like this:

DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection(
  "jdbc:oracle:thin:@<server>:1521:<sid>", <username>, <password>);

DatabaseMetaData meta = conn.getMetaData();
ResultSet columns = meta.getColumns(null, null, "EMPLOYEES", null);
int i = 1;
while (columns.next())
{
  System.out.printf("%d: %s (%d)\n", i++, columns.getString("COLUMN_NAME"), 
    columns.getInt("ORDINAL_POSITION"));
}

When I ran this code to my surprise too many columns were returned. A closer look revealed that the ResultSet contained a duplicate set of all the columns, i.e. every column was returned twice. Here's the output I got:

1: ID (1)
2: NAME (2)
3: CITY (3)
4: ID (1)
5: NAME (2)
6: CITY (3)

When I look at the table using Oracle SQL Developer it shows that the table only has three columns (ID, NAME, CITY). I've tried this code against several different tables in my database and some work just fine, while others exhibit this weird behaviour.

Could there be a bug in the Oracle JDBC driver? Or am I doing something wrong here?

---

Update: Thanks to Kenster I now have an alternative way to retrieve the column names. You can get them from a ResultSet, like this:

DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@<server>:1521:<sid>", <username>, <password>);

Statement st = conn.createStatement();
ResultSet rset = st.executeQuery("SELECT * FROM \"EMPLOYEES\"");
ResultSetMetaData md = rset.getMetaData();
for (int i=1; i<=md.getColumnCount(); i++)
{
    System.out.println(md.getColumnLabel(i));
}

This seems to work just fine and no duplicates are returned! And for those who wonder: according to this blog you should use getColumnLabel() instead of getColumnName().

Answer 1

In oracle, Connection.getMetaData() returns meta-data for the entire database, not just the schema you happen to be connected to. So when you supply null as the first two arguments to meta.getColumns(), you're not filtering the results for just your schema.

You need to supply the name of the Oracle schema to one of the first two parameters of meta.getColumns(), probably the second one, e.g.

meta.getColumns(null, "myuser", "EMPLOYEES", null);

It's a bit irritating having to do this, but that's the way the Oracle folks chose to implement their JDBC driver.

Answer 2

This doesn't directly answer your question, but another approach is to execute the query:

select * from tablename where 1 = 0

This will return a ResultSet, even though it doesn't select any rows. The result set metadata will match the table that you selected from. Depending on what you're doing, this can be more convenient. tablename can be anything that you can select on--you don't have to get the case correct or worry about what schema it's in.