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:



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

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.

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.

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

On Oct 16, 4:48 pm, Shakespeare <what... at (no spam) 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.

jg
--
at (no spam) home.com is bogus.
yay, boss is going fishing!  That means I can... have more work to
do... boo.

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

On Oct 16, 7:48 pm, Shakespeare <what... at (no spam) 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


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

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

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.

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?

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.

at (no spam) 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.