CODEWITHSUNDEEP
javasqloracle-databaseoracle-sqldeveloperdatabase-metadata
Armer B.
Armer B.

Reputation: 772

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

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.

Upvotes: 0

Views: 1534

Answers (2)

Randy
Randy

Reputation: 809

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);
    }

Upvotes: 0

Izruo
Izruo

Reputation: 2276

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);

Upvotes: 1

Related Questions