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

Ramon F Herrera schreef:
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

On Oct 16, 7:32*pm, Ramon F Herrera <ra... (AT) conexus (DOT) net> wrote:

Since the following question is conceptually similar to the previous,
I will tag it along this thread.

I need to write code which creates a new record if it doesn't exist
and modifies it otherwise. It seems that Oracle (actually, the SQL
language) has two different statements for doing this, one being
INSERT and the other UPDATE, right? (IOW, there is no single command
like "create or replace" - I have to roll my own "create or replace").

Therefore, my code should probably look like this:

SELECT COUNT(1) INTO howMany FROM mytable WHERE <some constraint>

if (howMany == 1)
UPDATE mytable ();
else
INSERT INTO mytable();

Right?

I just want to make sure that that is the correct way.

TIA,

-Ramon

On Oct 16, 4:48*pm, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
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
--
@home.com is bogus.
yay, boss is going fishing! That means I can... have more work to
do... boo.

In comp.databases.oracle.misc Ramon F Herrera <ramon (AT) conexus (DOT) net> wrote:
Take a look at the MERGE statement.

--
__________________________________________________ _____________________
Dan Blum tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

On Oct 16, 4:51*pm, Ramon F Herrera <ra... (AT) conexus (DOT) net> wrote:

http://asktom.oracle.com/pls/asktom/...61865893444475

jg
--
@home.com is bogus.
http://www.theregister.co.uk/2009/10...sider_trading/

On Oct 16, 7:48*pm, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:

Good point. The thing about my design is that I have "a table of
tables". The code in question is for my internal use, and I type it
from the Unix prompt. Let's say I have a bunch of company clients and
the command in question tells me when was the last time such company
made a payment or something.

For policy and other reasons I treat every company as a separate
entity with their own tables. Some companies demand not to share table
space with other companies.

Therefore the interaction would look like this:

% last_payment google

The program -after checking that a table named "google" does not
exist- will retort:

"Are you kidding me, Ramon? `google' is not your client"

-RFH

On Oct 16, 7:56*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:

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

Ramon F Herrera schreef:
Ok, fair enough. This seems a legitimate way of checking, as long as
your own scripts are involved. You convinced me!!

Shakespeare

Out of principle I would never use COUNT() to check for existence.
Imagine a bowl of rice. does it require counting the rice grains inside
the bowl to know whether it is empty or not?
One glance should be enough.
In SQL Terms that would be an EXISTS predicate.
Or if you want to keep it simple in your case

SELECT 1 FROM USER.TABLES WHERE table_name = :table_name AND ROWNUM < 2

In teh case of USER_TABLE by design you may only get one row, but if you
use COUNT() here chances are someone will copy your code and use it
somewhere else where there are a million buggers to count.

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab