Re: Is this the sanctioned way to ascertain a table's existence?
Date: Fri, 16 Oct 2009 17:25:48 -0700 (PDT)
On Oct 16, 7:56 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Oct 16, 4:48 pm, Shakespeare <what..._at_xs4all.nl> wrote:
> > Ramon F Herrera schreef:
> > > 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?
> > > -Ramon
> > I always get a bit worried if an application has to check whether a
> > table exists.... should they not be there just by design?
> > Shakespeare
> I think it is reasonable for many applications, they can ascertain
> where in a process things are or whether they are being run for the
> first time. Now, I'm a great believer in the DBA being in control of
> DDL, and I think you are right to worry, I'm just saying it isn't
> necessarily in the nightmare realm.
> As far as the proper way, TIMTOWTDI.
> _at_home.com is bogus.
> yay, boss is going fishing! That means I can... have more work to
> do... boo.
I obviously agree with joel, and would like to put the Great Bard's worries to rest.
My applications create a whole bunch of SQL statements on the fly, using C++. Assembling those SQL statements is an expensive task, computationally speaking. Therefore, even before my code embarks into the task of preparing a set of complex query(es), it should make sure that at least the basic table (called 'google' in my previous example) exists.
Let me use an actual real example. I hate getting inside the "sqlplus" shell with the fury of 1K suns. I am a clumsy typist and am used to modern shells. Therefore I wrote a handy utility called 'nrec', which I run from the Unix prompt:
% nrec table_this
% nrec table_that
Internally, the command 'nrec' actually is a simple:
SELECT COUNT(1) from :someTable;
The user can type (or mistype) any table name, which should be checked before attempting to perform the actual COUNT. Let's say that I prefer to handle errors on my own instead of feeding crap to Oracle.
-Ramon Received on Fri Oct 16 2009 - 19:25:48 CDT