Re: Is this the sanctioned way to ascertain a table's existence?
From: Noons <wizofoz2k_at_yahoo.com.au>
Date: Sat, 17 Oct 2009 23:33:41 +1000
Message-ID: <hbcdid$g1i$1_at_news.eternal-september.org>
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?
Date: Sat, 17 Oct 2009 23:33:41 +1000
Message-ID: <hbcdid$g1i$1_at_news.eternal-september.org>
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.
Received on Sat Oct 17 2009 - 08:33:41 CDT