dropping table or view and exception handling
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: dropping table or view and exception handling

  1. #1
    Join Date
    Mar 2001
    Posts
    46

    Talking

    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

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,443
    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
  •  



Click Here to Expand Forum to Full Width