Java DatabaseMetaData.getColumns() method doesn't work for all users

Question

I've got a few users in Oracle database, let's say UserOne, UserTwo and UserTree with same and not empty table with name - "tableExample".

In my case I need to initialize ResultSet throught getColumns() method:

DatabaseMetaData md = conn.getMetaData();

ResultSet r = md.getColumns(null, "UserTwo", "tableExample", null);
while(r.next())
{
//Do something;
}

In above example r.next() is true, but when I'm using UserOne or UserTree r.next() is false.

How can I solve this issue so to achieve this table's ResultSet regardless of which user I have chosen?

*If I use:

ResultSet r = md.getColumns(null, null, "tableExample", null);

I will receive result for all users into database, but I need to dynamically concretize users using the second argument in this method.

Izruo · Accepted Answer · 2017-11-24T00:10:30.903

1

From the apidocs:

schemaPattern - a schema name pattern; must match the schema name as it is stored in the database; [...]

So it's a pattern, but unfortunately there's no hint on what type of pattern is expected from the method, but you might just try something out:

ResultSet r = md.getColumns(null, "User*", "tableExample", null);
ResultSet r = md.getColumns(null, "User.+", "tableExample", null);

Alternatively you can use oracle's system tables directly (apidoc for 10g):

String sql = "SELECT * FROM SYS.ALL_TAB_COLUMNS WHERE OWNER LIKE 'User%'";
ResultSet r = conn.createStatement().executeQuery(sql);

edited Nov 24 '17 at 00:10

answered Nov 22 '17 at 20:10

Izruo

2,246
1
11
23

Thanks for the anwer, in fact my issue was that I have to dynamically change database Connection initialication(URL, pass, userName) and I fixed it today. *In your answer there is one mistake -ResultSet r = conn.createStatement().executeQuery(sql); – Armer B. Nov 23 '17 at 15:42

score 0 · Answer 2 · answered Jul 20 '22 at 19:13

This is old, but might help someone searching around and seeing this. I am using the ojbc10 jar version 19.3.0.0 . It works - but internally it seems to use the add_tab_cols or dba_tab_cols view and needs the schema and table names to be upper case, or it seems to fail returning nothing.

This works for me (non-Oracle is currently Postgres):

    if (databaseMetaData instanceof OracleDatabaseMetaData) {
        ResultSet rs = databaseMetaData.getColumns(null, schema.toUpperCase(), tableName.toUpperCase(), null);
        return getColumnsFromResultSet(tableName, rs);
    } else {
        ResultSet rs = databaseMetaData.getColumns(null, schema, tableName, null);
        return getColumnsFromResultSet(tableName, rs);
    }

Java DatabaseMetaData.getColumns() method doesn't work for all users

2 Answers2