Path: news.easynews.com!easynews!sn-xit-02!supernews.com!postnews1.google.com!not-for-mail
From: gwhubert@hotmail.com (Gene Hubert)
Newsgroups: comp.databases.oracle.server
Subject: Re: How to conditionally copy and then create it using SQL scripts
Date: 10 Oct 2001 06:24:29 -0700
Organization: http://groups.google.com/
Lines: 35
Message-ID: <7e3fa619.0110100524.2ccbcace@posting.google.com>
References: <de83f985.0110100042.43dac84f@posting.google.com>
NNTP-Posting-Host: 192.58.204.121
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1002720269 5333 127.0.0.1 (10 Oct 2001 13:24:29 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 10 Oct 2001 13:24:29 GMT
Xref: easynews comp.databases.oracle.server:120806
X-Received-Date: Wed, 10 Oct 2001 07:21:02 MST (news.easynews.com)

Use a pl/sql anonymous block and native dynamic sql.  Something like:

declare
  cnt pls_integer := 0;
begin
  select count(*) into cnt from user_tables where table_name='XXX';
  if cnt = 0 then
    execute immediate 'create table junk (x number);
  end if;
dbms_output.put_line(cnt);
end;
/

Gene Hubert
Durham, NC


lzhang@bj.bexcom.com (zl) wrote in message news:<de83f985.0110100042.43dac84f@posting.google.com>...
> In SQL server, the following SQL statements can be used to create a
> table if it is not existed.
> 
> 
> IF EXISTS  (SELECT * FROM sysobjects 
>                                WHERE name = 'BusinessDocIdGenerator' 
>                                        AND type = 'U')
>            DROP TABLE BusinessDocIdGenerator
> 
> CREATE TABLE BusinessDocIdGenerator (
>        NumericGeneratorId   int NULL,
>        .....
> )
> 
> 
> How can I do this in Oracle? Oracle doens't allow "if" appears in
> sqlplus.