CODEWITHSUNDEEP
java
Sachin Doiphode
Sachin Doiphode

Reputation: 433

How to check whether table with given name exists in oracle through Java?

I have csv file and i want to import that data to oracle database. but before that i want it to check whether table 'xyz' exist in database or not.

I want to do it through java .. anyone knows how to do it through java ?

Upvotes: 3

Views: 6302

Answers (4)

Ankur
Ankur

Reputation: 788

public class Main 
{

  public static void main(String[] args) throws Exception 
  {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@//server.local:1521/prod", "scott", "tiger");
    conn.setAutoCommit(false);
    DatabaseMetaData dbmd = conn.getMetaData();
    ResultSet rset = dbmd.getTables("", "SCOTT", "EMP", null);
    while (rs.next()) 
    {
      System.out.println(rs.getString(3));
      //OR use whatever progmatically you want to do if table exist
    }  
    stmt.close();
  }
}

Upvotes: 1

CloudyMarble
CloudyMarble

Reputation: 37566

You can just run a dummy SQL like:

select from * 'xyz' where 1 = 2

If you don't get any Exception like "ORA-00942 Table or View doesnt exist" then the Table exists.

Or you choose the elegant way like:

select * from USER_OBJECTS where OBJECT_TYPE = 'TABLE' and OBJECT_NAME = 'xyz';

Upvotes: 2

Umesh Kacha
Umesh Kacha

Reputation: 13666

You can use the available meta data:

DatabaseMetaData meta = con.getMetaData();
  ResultSet res = meta.getTables(null, null, null, 
     new String[] {"TABLE"});
  while (res.next()) {
     System.out.println(
        "   "+res.getString("TABLE_CAT") 
       + ", "+res.getString("TABLE_SCHEM")
       + ", "+res.getString("TABLE_NAME")
       + ", "+res.getString("TABLE_TYPE")
       + ", "+res.getString("REMARKS")); 
  }

See here for more details.

Upvotes: 5

krock
krock

Reputation: 29619

Wikipedia has some good info on getting at oracle metadata.

 SELECT
 COUNT(1)
 FROM
 ALL_TABLES
 WHERE
 TABLE_NAME = 'NAME_OF_TABLE'

Replace NAME_OF_TABLE with the name of your table and if you get a result of 1, you have your table.

Upvotes: 2

Related Questions