Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Check existence of table

 
Bill Earnhardt
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need to be able to test for the existence of a table in oracle using a java stored procedure. If it exists, I populate it, it doesn't, I create it and then populate.
Any suggestions would be appreciated.
Thanks,
Bill
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34839
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bill,
Most databases have a table that lists all the tables in the database. I think it is called ALL_TABLES in Oracle. You could query this table to check for the existence of a table. If the query returns zero rows, you create it as you described.
 
Bill Earnhardt
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeanne, thanks for the helpful information and found the table you were talking about. I'll give this a try and let you know the outcome.
Thank you!
 
Chinmay Bajikar
Ranch Hand
Posts: 159
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A generic way might be to use the
Connection.getMetaData(),which will return you a DatabaseMetaData object.
you can use the getTables() api in there to get the list of tables.
Why write some db specific code when you can solve the whole thing through
generic JDBC code???
Another question that I might think of here is which one would be more efficient.
Writing something like "select tname from tab" or using the DatabaseMetadata approach??
Thanks,
Chinmay
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34839
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Chinmay,
Bill mentioned he is using a stored procedure. So he is probably locked into Oracle anyway (stored procs tend to be db specific.) I think the SQL query would be more efficient because it only needs to return one item rather than the list of tables. This could make a difference if there are a lot of tables.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic