Re: Is this the sanctioned way to ascertain a table's existence?

From: Maxim Demenko <>
Date: Sat, 17 Oct 2009 15:24:58 +0200
Message-ID: <>

Noons wrote:
> Ramon F Herrera wrote,on my timestamp of 17/10/2009 9:32 AM:
>> All my database accesses are done through Pro*C/C++.
>> Sometimes my code needs to verify whether a table exists. Years ago, I
>> was about to post that particular  questions here. I then realized
>> that the following statements achieve the desired results:
>> EXEC SQL SELECT COUNT(1) INTO :howMany FROM user_tables WHERE
>> table_name = :table_name;
>> if (howMany == 1)
>>     doThis();
>> else
>>    doThat();
>> Is that "the" proper way?

> It is most definitely not the proper way.
> If you want to check for table existence, you check its meta-data in the
> dictionary exists. You do not count the rows!
> Imagine what that would do to the performance of your application if all
> tables had billions of rows, for example.
> Select the table name from the view USER_TABLES or ALL_TABLES, depending
> on if the table is owned by the current logon or not, respectively. And
> it's either found or not found, period. There is a specific return code
> to check for that in Pro*C. It's also a lot faster than counting rows.

I got the impression, he *is* checking metadata (more precise - user_tables).

i would probably not verify at all whether the table exists. Look at the   demo examples ansidyn1.pc and ansidyn2.pc ($ORACLE_HOME/precomp/demo/proc) - your goal looks for me as typical use case for dynamic sql. Just parse
the statement, what you intend to execute with the table of interest (assuming , this table exists) and if the parse fail, do the else branch of your code. Imho, doing like that, you reduce the work for your engine to the minimum.

Best regards

Maxim Received on Sat Oct 17 2009 - 08:24:58 CDT

Original text of this message