Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Create table in if-statement

Re: Create table in if-statement

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: 2000/07/16
Message-ID: <3971ED62.39BFA508@0800-einwahl.de>#1/1

Two main reason not to drop a table and recreating it is that objects using that table are invalidated and that recreating them recreates them with the default storage unless you fully specify all storage parameters (the default storage parameters may have changed in the meantime). Further, all object grants are dropped and have to be recreated.

The solution, however, is to conditionally create the table like


set echo on
set serveroutput on size 1000000

describe test

create or replace procedure condcreate is

        l_cmd varchar2 (2000);
begin

	for r in (
		select 'create table TEST (n integer)' as cmd from dual
		where not exists (select null from user_tables where table_name = 'TEST')
		union
		select 'truncate table TEST' from user_tables where table_name = 'TEST'
	) loop
		l_cmd:= r.cmd;
		dbms_output.put_line ('l_cmd = "' || l_cmd || '"');
	end loop;
	execute immediate l_cmd;

end;
/
show errors

drop table test cascade constraints;

describe test

execute condcreate

describe test

execute condcreate


Martin

Kathinka Diehl wrote:
>
> <poluha_at_my-deja.com> schrieb:
> >
> > What I want to do is to check whether a table already exists. If it
> > doesn't I want to create it, otherwise I want to empty it.
>
> Why you don't do it in this way: just drop it first if it exists or not and
> then create a new one?
>
> Regards,
> Kathinka
Received on Sun Jul 16 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US