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

From: Ramon F Herrera <ramon_at_conexus.net> Newsgroups: comp.databases.oracle.server,comp.databases.oracle.misc Subject: Re: Is this the sanctioned way to ascertain a table's existence? Date: Fri, 16 Oct 2009 16:51:39 -0700 (PDT) Organization: http://groups.google.com
Lines: 45
Message-ID: <2a14cfd5-257c-4f1b-b710-01e07225dbe9_at_a31g2000yqn.googlegroups.com> References: <77fe2001-5e40-4e38-8398-7d4e45586115_at_k33g2000yqa.googlegroups.com> NNTP-Posting-Host: 190.37.71.130
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1255737099 27134 127.0.0.1 (16 Oct 2009 23:51:39 GMT) X-Complaints-To: groups-abuse_at_google.com NNTP-Posting-Date: Fri, 16 Oct 2009 23:51:39 +0000 (UTC) Complaints-To: groups-abuse_at_google.com Injection-Info: a31g2000yqn.googlegroups.com; posting-host0.37.71.130;

        posting-account=X64iTAkAAADcZFDPVkvH-m-qXOfEepKk User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.3)

        Gecko/20090824 Firefox/3.5.3 (.NET CLR 3.5.30729),gzip(gfe),gzip(gfe) Xref: textnews.cambrium.nl comp.databases.oracle.server:88185 comp.databases.oracle.misc:28486

On Oct 16, 7:32 pm, Ramon F Herrera <ra..._at_conexus.net> wrote:
> 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

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 Received on Fri Oct 16 2009 - 18:51:39 CDT