Re: Is this the sanctioned way to ascertain a table's existence?
From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 17 Oct 2009 15:24:58 +0200
Message-ID: <4AD9C5AA.9060400_at_gmail.com>
Noons wrote:
> Ramon F Herrera wrote,on my timestamp of 17/10/2009 9:32 AM:
>
> 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.
Date: Sat, 17 Oct 2009 15:24:58 +0200
Message-ID: <4AD9C5AA.9060400_at_gmail.com>
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).
_at_OP
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