Procedure to drop tables [message #37581] |
Thu, 14 February 2002 17:22 |
diaz
Messages: 58 Registered: October 2001
|
Member |
|
|
hello,
i have this procedure :
create or replace procedure drop_table as
does_not_exist exception;
tab varchar(20);
begin
tab=&1;
execute immediate 'drop table '|| tab ||' cascade constraints';
exeption
when does_not_exist exception then null;
when others then
raise;
end;
/
it said that the procedure is built with error compilation
when i execute it : what ever i put as an argument always said as undefine
could somebody explain it and give me solutions ?
|
|
|
Re: Procedure to drop tables [message #37582 is a reply to message #37581] |
Thu, 14 February 2002 18:22 |
Suresh Vemulapalli
Messages: 624 Registered: August 2000
|
Senior Member |
|
|
create or replace procedure drop_table(tab varchar2) as
does_not_exist exception;
pragma exception_init(does_not_exist,-00942);
begin
execute immediate 'drop table '|| tab ||' cascade constraints';
dbms_output.put_line(tab||' table dropped');
exception
when does_not_exist then
dbms_output.put_line('how can u drop non existing table?');
when others then
dbms_output.put_line(sqlerrm);
end;
/
SQL>set serveroutput on
SQL>exec drop_table('SOME_TABLE_NAME');
|
|
|
|