Home » SQL & PL/SQL » SQL & PL/SQL » Procedure to drop tables
Procedure to drop tables [message #37581] Thu, 14 February 2002 17:22 Go to next message
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 Go to previous messageGo to next message
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');
Re: Procedure to drop tables [message #37584 is a reply to message #37581] Thu, 14 February 2002 18:42 Go to previous message
diaz
Messages: 58
Registered: October 2001
Member
thank you mr.suresh
it works fine.
Previous Topic: How to fetch record from Cursor and store them in memory -URGENT
Next Topic: PARTITION PROBLEM
Goto Forum:
  


Current Time: Fri Mar 29 08:07:21 CDT 2024