name is already used by an existing object

Question

In this code I am trying to delete the tables if they already exist every time I run the program, but the control is not going inside the if statements. table1 and table2 are present in the database. I have checked that in my database. Since its not going in the if statements, it's giving the following error in the last line, when I try to create the tables: ORA-00955: name is already used by an existing object. What am I doing wrong?

    Statement statement = connection.createStatement();
    DatabaseMetaData md = connection.getMetaData(); 
    String[] types = {"TABLE"};
    ResultSet rs1 = md.getTables(null, null, "table1",types );
    if (rs1.next()) {
        System.out.println(rs1.getString(3));
        statement.executeUpdate("drop table table1");
    }
    rs1.close();

    ResultSet rs2 = md.getTables(null, null, "table2", types);
    if (rs2.next()) {
        statement.executeUpdate("drop table table2");
    }
    rs2.close();
    statement.executeUpdate("create table table1(" +.....

Kathrinkathrine

Answer 1

If the tables table1 and table2 were created by your program above, then their names will have been capitalised by Oracle. Try

    ResultSet rs1 = md.getTables(null, null, "TABLE1", types);

(and similarly for table2) instead.

Alternatively, if you want the tables to be created with lower-case letters in their names instead, enclose their names in double-quotes, i.e.

    statement.executeUpdate("create table \"table1\" (....)");

Colostrum

Answer 2

I would replace this line

ResultSet rs1 = md.getTables(null, null, "table1",types );

with this one

ResultSet rs1 = md.getTables(null, null, "TABLE1",types );

Tatter