Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

performance Tuning 10g reference poster
 
Oracle training in Linux 

commands
 
Oracle training 

Excel
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> if table exists
foracle
post Nov 9 2006, 10:27 AM
Post #1


Newbie
*

Group: Members
Posts: 6
Joined: 10-August 06
Member No.: 4,795



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
Go to the top of the page
 
+Quote Post
burleson
post Nov 9 2006, 10:34 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,088
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
foracle
post Nov 9 2006, 11:44 AM
Post #3


Newbie
*

Group: Members
Posts: 6
Joined: 10-August 06
Member No.: 4,795



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;
Go to the top of the page
 
+Quote Post
Marc L
post Nov 9 2006, 12:58 PM
Post #4


Newbie
*

Group: Members
Posts: 8
Joined: 9-November 06
Member No.: 5,558



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
Go to the top of the page
 
+Quote Post
Justin Clareburt
post Jun 25 2008, 08:02 PM
Post #5


Newbie
*

Group: Members
Posts: 2
Joined: 25-June 08
Member No.: 26,987



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.
Go to the top of the page
 
+Quote Post
limsk
post Nov 24 2008, 09:54 PM
Post #6


Advanced Member
***

Group: Members
Posts: 76
Joined: 20-January 08
From: Kuala Lumpur, MY
Member No.: 16,279



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


Go to the top of the page
 
+Quote Post
Rengeek
post Jul 20 2009, 12:05 PM
Post #7


Newbie
*

Group: Members
Posts: 1
Joined: 20-July 09
Member No.: 41,293



Thanks - this was PERFECT!
Don

QUOTE (Justin Clareburt @ Jun 25 2008, 07:03 PM) *
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.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 17th April 2014 - 01:23 PM