Re: How to conditionally copy and then create it using SQL scripts

From: Gene Hubert <gwhubert_at_hotmail.com>
Date: 10 Oct 2001 06:24:29 -0700
Message-ID: <7e3fa619.0110100524.2ccbcace@posting.google.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_at_bj.bexcom.com (zl) wrote in message news:<de83f985.0110100042.43dac84f_at_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.
Received on Wed Oct 10 2001 - 08:24:29 CDT