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

From: Ramon F Herrera <>
Date: Fri, 16 Oct 2009 17:25:48 -0700 (PDT)
Message-ID: <>

On Oct 16, 7:56 pm, joel garry <> wrote:
> On Oct 16, 4:48 pm, Shakespeare <> 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.
> jg
> --
> 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
% 1242

% nrec table_that
% 5639

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

Original text of this message