Path: news.easynews.com!easynews!cyclone.swbell.net!pln-e!spln!dex!extra.newsguy.com!newsp.newsguy.com!drn From: Thomas Kyte Newsgroups: comp.databases.oracle.server Subject: Re: How to conditionally copy and then create it using SQL scripts Date: 10 Oct 2001 11:27:59 -0700 Organization: Oracle Lines: 41 Message-ID: <9q23vf0209f@drn.newsguy.com> References: NNTP-Posting-Host: p-809.newsdawg.com X-Newsreader: Direct Read News 2.90 Xref: easynews comp.databases.oracle.server:120852 X-Received-Date: Wed, 10 Oct 2001 11:51:02 MST (news.easynews.com) In article , lzhang@bj.bexcom.com says... > >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, > ..... >) begin for x in ( select * from user_tables where table_name = 'FOO' ) loop execute immediate 'drop table foo'; end loop; end; / create table foo ( x int ); > > >How can I do this in Oracle? Oracle doens't allow "if" appears in >sqlplus. -- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle Corp