if table exists Options

[foracle]

Hi,
I am a sql server developer and have been asked to create a script for drop and create tables.

For example this is what I would like to have:
if table1 exists
drop table1
create table1...

if table2 exists
drop table2
create table2

if table3 exists
drop table3
create table3

I am ok with the drop and create scrip but need help on if exists please.
Thanks

[burleson]

Hi,

Welcome to Oracle!

You need to learn the data dictionary! Start with the dictionary poster:

http://www.dba-oracle.com/poster.htm

I would recommend the Oracle script collectio, over 600 ready-to-run Oracle scripts for every DBA act:

http://www.oracle-script.com

>> but need help on if exists please.

I would create a list of existing tables from DBA_TABLES, and make the drop syntax, like this

spool runme.sql
select "drop table "||table_name||";" from dba_tables where xxx;
spool off
@runme.sql

[foracle]

This is what I have comeup with but it seems there is an error on the first drop.
Thanks

declare cnt number;

begin
select count(*) into cnt from all_tables
where table_name = 'test.table1';

if cnt = 1 then
drop table test.table1;
end if;

select count(*) into cnt from all_tables
where table_name = 'test.table2';

if cnt = 1 then
drop table 'test.table2';
end if;

select count(*) into cnt from all_tables
where table_name = 'test.table3';

if cnt = 1 then
drop table test.table3;
end if;

CREATE TABLE test.table1
(
tcountry VARCHAR2 (50) NOT NULL
, tdatatype VARCHAR2 (50) NOT NULL
, dteffectivedate DATE
, ifamilysize NUMBER (2)
, tgrade VARCHAR2 (50)
, damount NUMBER (15,2)
);



CREATE TABLE test.table2
(
thomecountry VARCHAR2 (50) NOT NULL
, thostcountry VARCHAR2 (50) NOT NULL
, tdatatype VARCHAR2 (50) NOT NULL
, dteffectivedate DATE
, ifamilysize NUMBER (2)
, tgrade VARCHAR2 (50)
, damount NUMBER (15,2)
);


CREATE TABLE test.table3
(
ilocid VARCHAR2 (50) NOT NULL
, tdatatype VARCHAR2 (50) NOT NULL
, dteffectivedate DATE
, ifamilysize NUMBER (2)
, tgrade VARCHAR2 (50)
, damount NUMBER (15,2)
);

end

commit;

[Marc L]

The error message you are receiving would be helpful.

Also ALL_TABLES has the schema owner in a different column.

I'm pretty sure you should be checking table_name='TABLE1' and owner='TEST'

Also, you need to use Execute Immediate for DDL statements within PL/SQL.



So something like

declare

v_cnt number :=0;

begin

select count(*) into v_cnt from all_tables where table_name='TABLE1' and owner = 'TEST';

if v_cnt = 1 then
execute immediate 'DROP TABLE TEST.TABLE1';

execute immediate 'CREATE TABLE test.table1
(
tcountry VARCHAR2 (50) NOT NULL
, tdatatype VARCHAR2 (50) NOT NULL
, dteffectivedate DATE
, ifamilysize NUMBER (2)
, tgrade VARCHAR2 (50)
, damount NUMBER (15,2)
);'

end if;

end;

Hope this helps

Marc

[Justin Clareburt]

I found a simple solution to the drop table if exists:

Simply try to drop the table and catch the exception if it does not exist.
Like this:

begin execute immediate 'drop table TABLE1'; exception when others then null; end;

I hope that helps.

[limsk]

Any reason for needing to drop and recreate the tables repeatedly? To clear the rows? Are they the same tables being dropped everytime ?

If dropping and recreating the same set of tables everytime, you could create a set of custom scripts that have the DROP command before the CREATE for each table. Don't really need to check if they exist since they will be dropped if they are present and if they are not, then there will be an error "ORA-00942: table or view does not exist" but you can ignore it.

For example you can create a script file named create_test1.sql to create table TEST1:

CODE

-- create_test1.sql
-- Drops and recreates table TEST1
DROP TABLE TEST1;
CREATE TABLE TEST1 (
...
);

Then create a master script file named run_create.sql to call all those individual table scripts:

CODE

-- run_create.sql
-- Master script to drop and recreate tables
@create_test1.sql;
@create_test2.sql;
@create_test3.sql;
...

This would allow you to adjust the list to add or remove individual scripts if the list of tables to be recreated changes.

When you are ready, you just run the master script file from SQL*Plus everytime you want to drop and recreate those tables. Let us say all the scripts are in a folder called E:\database\scripts :

CODE

SQL> @E:\database\scripts\run_create.sql

[Rengeek]

Thanks - this was PERFECT!
Don

I found a simple solution to the drop table if exists:

Simply try to drop the table and catch the exception if it does not exist.
Like this:

begin execute immediate 'drop table TABLE1'; exception when others then null; end;

I hope that helps.