Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Drop if exists

Posted on 2006-07-11
14
Medium Priority
?
23,421 Views
Last Modified: 2008-01-16
Hi,

I need to check if a table exisits and drop it if it does (without errors) so a DROP and error if it doesn't exist won't work.

Any ideas?

DROP TABLE IF EXITS <tablename>;

(I know you Oracle guys will probably want me put down for writing a MySQL command in here, but hey!)

doesnt exist in Oracle, so is there a fancy way so I can get a yes/ no answer back into a variable in Perl to do an IF in perl? or even better a fancy way of doing this direct in Oracle?

Thanks

I.
0
Comment
Question by:ibradshaw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17085843
http://oracle-base.blogspot.com/2006/04/drop-object-if-exists-in-oracle.html

Util_Mig.Table_Exists seems to be what you are looking for...
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17085856
set head off
set feed off
set define off
set echo off
spool c:\filename.sql
select 'DROP TABLE '  || table_name||';' from user_tables where table_name = '&TABLE_NAME';
spool off

Then run your filenam.sql using
@c:\filename.sql

0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17085907
Another way would to create aa procedure that takes your table_name as input. Something like

create or replace procedure drop_table (tab_name in varchar2)
is
declare
qry_string varchar2(4000);
cnt number;
begin
select count into cnt from user_tables where table_name = tab_name;

if cnt > 0 then
qry_string := 'EXECUTE IMMEDIATE ' || tab_name;
begin
EXECUTE IMMEDIATE qry_string
exception
when others then
dbms_output.put_line('Error occured while dropping' || sqlerrm);
end;
else
dbms_output.put_line('TABLE ' || tab_name ||' does not exist');
end if;
end;
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 14

Expert Comment

by:sathyagiri
ID: 17085921
Then call your procedure like this
set serveroutput on
exec drop_table('TABLE_NAME');
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17085945
Oops made some errors in the proc, use this
create or replace procedure drop_table (tab_name in varchar2)
is
declare
qry_string varchar2(4000);
cnt number;
begin
select count into cnt from user_tables where table_name = tab_name;

if cnt > 0 then
qry_string := 'DROP TABLE ' || tab_name;
begin
EXECUTE IMMEDIATE qry_string;
exception
when others then
dbms_output.put_line('Error occured while dropping ' || sqlerrm);
end;
else
dbms_output.put_line('TABLE ' || tab_name ||' does not exist');
end if;
end;
0
 
LVL 2

Author Comment

by:ibradshaw
ID: 17086098
Hi sathy,

I still can't get it to run (perhaps I'm being think!!!)...

I'm trying to run

create or replace procedure drop_table (tab_name in varchar2)
is
declare
qry_string varchar2(4000);
cnt number;
begin
select count into cnt from user_tables where table_name = tab_name;

if cnt > 0 then
qry_string := 'DROP TABLE ' || tab_name;
begin
EXECUTE IMMEDIATE qry_string;
exception
when others then
dbms_output.put_line('Error occured while dropping ' || sqlerrm);
end;
else
dbms_output.put_line('TABLE ' || tab_name ||' does not exist');
end if;
end;

in application express and its coming back with

ERROR at line 3: PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:

   begin function package pragma procedure subtype type use
     form
   current cursor external language
The symbol "begin" was substituted for "DECLARE" to continue.
1. create or replace procedure drop_table (tab_name in varchar2)
2. is
3. declare
4. qry_string varchar2(4000);
5. cnt number;
 
sorry if i'm on idiot level 10 here...! I usually get them to run in AE then butcher the perl scripts when it works. does this need to be direct in the script or should it work in AE?

Thanks for all ya help

I.

p.s. you too angel!
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17086122
oops sorry made some error again
try this
create or replace procedure drop_table (tab_name in varchar2)
is
qry_string varchar2(4000);
cnt number;
begin
select count into cnt from user_tables where table_name = tab_name;

if cnt > 0 then
qry_string := 'DROP TABLE ' || tab_name;
begin
EXECUTE IMMEDIATE qry_string;
exception
when others then
dbms_output.put_line('Error occured while dropping ' || sqlerrm);
end;
else
dbms_output.put_line('TABLE ' || tab_name ||' does not exist');
end if;
end;
0
 
LVL 14

Accepted Solution

by:
sathyagiri earned 2000 total points
ID: 17086165
Use this made one other error

create or replace procedure drop_table (tab_name in varchar2)
is
qry_string varchar2(4000);
cnt number;
begin
select count(1) into cnt from user_tables where table_name = tab_name;

if cnt > 0 then
qry_string := 'DROP TABLE ' || tab_name;
begin
EXECUTE IMMEDIATE qry_string;
exception
when others then
dbms_output.put_line('Error occured while dropping ' || sqlerrm);
end;
else
dbms_output.put_line('TABLE ' || tab_name ||' does not exist');
end if;
end;

0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17086186
The above should work for you. But remember to pass the table name in caps.

For example if you want to drop table emp, then call the proc as follows

exec drop_table('EMP');
0
 
LVL 2

Author Comment

by:ibradshaw
ID: 17086257
Hi

You are god.  its official. :)

I had to use

call drop_table('USERS')

instead of exec as the perl DBI / DBD driver didn't know what to do with exec.

Thanks a million for all ya help.

I.
0
 
LVL 2

Author Comment

by:ibradshaw
ID: 17086404
p.s.

If anyone reading this has a similar problem, managed to work out the sequence version as well based on the above... (took me a while to use sequences as opposed to sequence, so thought I'd post it here!)



create or replace procedure drop_sequence (seq_name in varchar2)
is
qry_string varchar2(4000);
cnt number;
begin
select count(1) into cnt from user_sequences where seq_name = sequence_name;

if cnt > 0 then
qry_string := 'DROP SEQUENCE ' || seq_name;
begin
EXECUTE IMMEDIATE qry_string;
exception
when others then
dbms_output.put_line('Error occured while dropping ' || sqlerrm);
end;
else
dbms_output.put_line('SEQUENCE ' || seq_name ||' does not exist');
end if;
end;
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17087812
you are welcome
0
 

Expert Comment

by:tozysentosa
ID: 23937634
Hi

I found this that worked for me...


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

Open in new window

0
 

Expert Comment

by:gafeta
ID: 24062998
hi Guys!
Maybe I missed the point with lon scripts but, This is workingch better with less code
Thank you tozysentosa for this tiny solution
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question