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
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.
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
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.