aspose file tools*
The moose likes JDBC and the fly likes Check existence of table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Check existence of table" Watch "Check existence of table" New topic
Author

Check existence of table

Bill Earnhardt
Greenhorn

Joined: Nov 15, 2003
Posts: 9
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30309
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Bill Earnhardt
Greenhorn

Joined: Nov 15, 2003
Posts: 9
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

Joined: Dec 08, 2001
Posts: 159
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


The strength of the Wolf is the pack & the strength of the pack is the wolf....Rudyard Kipling
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30309
    
150

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Check existence of table