CODEWITHSUNDEEP
javadatabaseoracle-database
فربد ضروري
فربد ضروري

Reputation: 215

Automatically create a table in oracle database if it doesn't exist

I'm working on a AAA (Authentication and etc..) application in java and i want to know is there a way to check by error code or anything that if the table which i want to insert data in it exists.And if its dropped or any kind of problem occurred to it,the program create the table automatically.
P.S: I've checked other related questions and the answers did not fit the check by the java program thing i want.

Upvotes: 2

Views: 117

Answers (2)

Nicolas Filotto
Nicolas Filotto

Reputation: 45005

The most efficient way I know, is this:

public static boolean tableExists(String tableName, Connection con) {
    try (Statement stmt = con.createStatement();
         ResultSet trs = stmt.executeQuery(
             String.format(
                 "SELECT count(*) from (SELECT 1 FROM %s WHERE ROWNUM = 1) T", 
                 tableName
             )
         )
    ) {
        return trs.next();
    }
    catch (SQLException e) {
        if (LOG.isDebugEnabled()) {
            LOG.debug("SQLException occurs while checking the table " + tableName, e);
        }
        return false;
    }
}

NB: The standard approach is with connection.getMetaData().getTables(String catalog, String schemaPattern,String tableNamePattern, String types[]) in other words with database metadata but you will need to specify the schema name and you need additional privileges on database like oracle, with the approach above you don't have to specify the schema name as it will use the default one and you don't need any additional privilege. Even if it can be considered as a controversial approach, believe me it works pretty well, indeed we have hundreds of customers with it in production.

NB 2: If you are a little bit curious, We even generalized this approach in order to make it work on the most used databases, only the query changes a little bit as you can see here.

Upvotes: 2

Thomas
Thomas

Reputation: 366

I would use all_tables (dictionary) for that:

--your procedure
--some logic


--check the table in the dictionary
SELECT count(*)
  INTO v_count
  FROM all_tables  s
 WHERE s.table_name = p_table_name;


IF v_count = 0 THEN

....

END IF;


--the rest of logic

Upvotes: 3

Related Questions