Oracle 10g SQL for non-existent table and no rows

Oracle SQL - check for no table, no rows

I was recently tasked with an interesting issue.  The report that I created connected to a database table that would be refreshed regularly.  During the refresh process (sometimes taking minutes, sometimes longer), the report would fail to show any data, since the table was empty at that exact moment.  I needed a means to determine if the report had no rows, then I would display a message that the data was being updated.  The report was then to be migrated to another database, where the table didn't even exist yet.  In this case, I wanted to also display the same wait message.  Oracle doesn't have an obvious method to determine if a table exists.  Attempting to run Oracle SQL against a non-existent table results in an Oracle 904 error. 

I figured out that this code will do the trick, whether the table exists or not and whether the table has any rows in it or not.  It returns a binary 1 if the table doesn't exist or doesn't have any rows in it, otherwise a zero is returned.

   CASE WHEN (select num_rows from all_tables where table_name = '<your table name>') IS NULL
   THEN 1
   ELSE 0
from dual


Copyright© Brad Earle


1 + 4 =  Solve This To Prove You are a Real Person, not a SPAM script.
Site Map | Printable View | © 2008 - 2022 KB Earle Associates LLC | |