-
Hi Everyone,
I'm trying to make a procudure that will drop an "object" w/o caring about the object type. I.e. It could be a table or a view or a materialized view.... but I'm running into a problem getting this working.
I get an error, but show errors says no errors. Very helpful.
Can anyone help?
Here's the pl/sql block:
CREATE OR REPLACE PROCEDURE "Easydrop" (tableview varchar) IS
table_or_view_not_found EXCEPTION;
PRAGMA EXCEPTION_INIT (table_or_view_not_found, -942);
BEGIN
execute IMMEDIATE 'DROP table ' || tableview || ' cascade constraints';
Exception
WHEN table_or_view_not_found THEN
execute IMMEDIATE 'DROP view ' || tableview;
Exception
WHEN table_or_view_not_found THEN
NULL;
When Others then
RAISE;
When Others then
RAISE;
END Easydrop;
(p.s. The preview post option, doesn't show a preview)
ora_newbie@yahoo.com
-
Try this:
CREATE OR REPLACE PROCEDURE Easydrop (tableview varchar) IS
table_or_view_not_found EXCEPTION;
PRAGMA EXCEPTION_INIT (table_or_view_not_found, -942);
BEGIN
execute IMMEDIATE 'DROP table ' || tableview || ' cascade constraints';
Exception
WHEN table_or_view_not_found THEN
begin
execute IMMEDIATE 'DROP view ' || tableview;
Exception
WHEN table_or_view_not_found THEN
RAISE;
When Others then
RAISE;
end;
When Others then
RAISE;
END Easydrop;
Or BETTER:
CREATE OR REPLACE PROCEDURE Easydrop (tableview varchar) IS
table_or_view_not_found EXCEPTION;
PRAGMA EXCEPTION_INIT (table_or_view_not_found, -942);
V_OBJ VARCHAR2(30);
BEGIN
Select object_type into v_obj
from user_objects
where object_name = upper(tableview);
execute IMMEDIATE 'DROP '|| v_obj||' '|| tableview || ' cascade constraints';
Exception
WHEN table_or_view_not_found THEN
RAISE;
When Others then
RAISE;
END Easydrop;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|