ibradshaw
asked on
Drop if exists
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.
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.
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
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
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('Erro r occured while dropping' || sqlerrm);
end;
else
dbms_output.put_line('TABL E ' || tab_name ||' does not exist');
end if;
end;
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('Erro
end;
else
dbms_output.put_line('TABL
end if;
end;
Then call your procedure like this
set serveroutput on
exec drop_table('TABLE_NAME');
set serveroutput on
exec drop_table('TABLE_NAME');
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('Erro r occured while dropping ' || sqlerrm);
end;
else
dbms_output.put_line('TABL E ' || tab_name ||' does not exist');
end if;
end;
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('Erro
end;
else
dbms_output.put_line('TABL
end if;
end;
ASKER
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('Erro r occured while dropping ' || sqlerrm);
end;
else
dbms_output.put_line('TABL E ' || 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!
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('Erro
end;
else
dbms_output.put_line('TABL
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!
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('Erro r occured while dropping ' || sqlerrm);
end;
else
dbms_output.put_line('TABL E ' || tab_name ||' does not exist');
end if;
end;
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('Erro
end;
else
dbms_output.put_line('TABL
end if;
end;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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');
For example if you want to drop table emp, then call the proc as follows
exec drop_table('EMP');
ASKER
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.
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.
ASKER
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('Erro r occured while dropping ' || sqlerrm);
end;
else
dbms_output.put_line('SEQU ENCE ' || seq_name ||' does not exist');
end if;
end;
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('Erro
end;
else
dbms_output.put_line('SEQU
end if;
end;
you are welcome
Hi
I found this that worked for me...
I found this that worked for me...
begin
execute immediate 'drop table TABLE1';
exception when others then null;
end;
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
Maybe I missed the point with lon scripts but, This is workingch better with less code
Thank you tozysentosa for this tiny solution
Util_Mig.Table_Exists seems to be what you are looking for...