Geek asks and answers

How to check if table with given name exists in oracle via Java?

I have a csv file and I want to import this data into an oracle database. but before that I want it to check if the xyz table exists in the database or not.

I want to do this via java. Does anyone know how to do this through java?

+3
source share
4 answers

Wikipedia has good information on obtaining 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 a table.

+2
source

You can use the available metadata:

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 .

+5
source

SQL :

select from * 'xyz' where 1 = 2

, , " ORA-00942 ", .

Or you choose the elegant way:

select * from USER_OBJECTS where OBJECT_TYPE = 'TABLE' and OBJECT_NAME = 'xyz';
+2
source 
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();
  }
}
+1
source

More articles:


All Articles