CODEWITHSUNDEEP
javaoracle-databasejdbc
johnny-b-goode
johnny-b-goode

Reputation: 3893

How to get database schema name when using oracle jdbc connection?

I am trying to get all db tables using DatabaseMetaData.getTables() method. But this method requires database schema name pattern. Is it possible to get schema name for current db connection?

Upvotes: 10

Views: 36747

Answers (5)

Darrell Teague
Darrell Teague

Reputation: 4282

The answer unfortunately is that there are no consistent solutions. If John has access to Sally.Table ... the query will work but getUserName() will return John and not Sally schema. For Oracle the user owns their schema and while others may have access, that user ID is the default schema on that connection.

Further, neither getSchemaName() nor getCatalog() will return the schema name.

@horse_with_no_name has the closest answer for Oracle since a given user name is the (default) schema name unless overridden in object reference as shown.

For other databases the same rules do not apply consistently.

Upvotes: 3

Tim Moore
Tim Moore

Reputation: 9482

Since Java 7, Connection has a getSchema method: https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#getSchema--

Upvotes: 3

user330315
user330315

Reputation:

The standard schema for your current connection is the name of the user you use to log in. So if your user is SCOTT you have to use SCOTT for DatabaseMetaData.getTables().

You can obtain the username through DatabaseMetaData.getUserName().

But remember that the comparison of schema/username done in the JDBC driver is case-sensititve and normally usernames are in uppercase.

I am not 100% sure if DatabaseMetaData.getUserName() will return the name in the correct case in all situations. To be sure, you might want to do an upperCase() before using that value.

Upvotes: 9

Jacob
Jacob

Reputation: 14741

You can get schema name using 

Connection conn = 
DriverManager.getConnection("jdbc:oracle:thin:@server:port:SID",prop);    
DatabaseMetaData databaseMetaData = conn.getMetaData();
System.out.println("schema name >>>> "+databaseMetaData.getUserName());

Upvotes: 1

Thai Tran
Thai Tran

Reputation: 9935

Try to play with getCatalogs(). This is a quick draft 

  public List<String> getDatabases(DBEnv dbEnv) {

        Connection conn = getConnection(dbEnv);
        List<String> resultSet = new ArrayList<String>();

        try {
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet res = metaData.getCatalogs();

            while (res.next()) {
                resultSet.add(res.getString("TABLE_CAT"));
            }

        } catch (SQLException e) {
            logger.error(e.toString());
        }

        return resultSet;

    }

Upvotes: 3

Related Questions