Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Create table in if-statement
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;
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