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

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.

Solution

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