CREATE OR REPLACE PACKAGE pk_drop IS ------------------------------------------------------------------------------------------- -- Procedure Purpose -- ------------- ----------------------------------------------------------------------- -- drop_if_there Checks for an object and deletes it if present -- tab_cons Drops all constraints and associated indexes on the given table -- -- REVISIONS: -- Date Author Description -- ---------- ------ ------------------------------------ -- 12-Jun-02 DW Created -- 19-Sep-06 DW Added TYPE to pk_drop -- 07-Sep-07 DW Added DROP INDEX to cater for keys with explicit indexes -- 16-Jun-08 DW Added tab_cons procedure ------------------------------------------------------------------------------------------- PROCEDURE drop_if_there (obj_name VARCHAR2, obj_type VARCHAR2, parent_table VARCHAR2); PROCEDURE tab_cons (p_tab_name VARCHAR2); END pk_drop; / CREATE OR REPLACE PACKAGE BODY pk_drop IS PROCEDURE drop_if_there (obj_name VARCHAR2, obj_type VARCHAR2, parent_table VARCHAR2) IS -- Drops the passed object if it exists -- obj_type should be one of: -- -- 'CONSTRAINT' (requires parent_table) -- 'FUNCTION' -- 'INDEX' -- 'PACKAGE' -- 'SEQUENCE' -- 'SYNONYM' -- 'TABLE' -- 'TRIGGER' -- 'VIEW' -- 'TYPE' cursor_handle INTEGER; return_value INTEGER; ThisObject VARCHAR2(30); ThisConsType VARCHAR2(1); sql_statement VARCHAR(200); missing EXCEPTION; BEGIN DBMS_OUTPUT.ENABLE(1000000); cursor_handle := DBMS_SQL.OPEN_CURSOR; ThisObject := NULL; BEGIN IF UPPER(obj_type) = 'CONSTRAINT' THEN SELECT constraint_name, constraint_type INTO ThisObject, ThisConsType FROM user_constraints WHERE constraint_name = UPPER(obj_name); ELSE SELECT object_name INTO ThisObject FROM user_objects WHERE object_name = UPPER(obj_name) AND object_type = UPPER(obj_type); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; IF ThisObject IS NULL THEN RAISE missing; -- not present END IF; IF UPPER(obj_type) = 'CONSTRAINT' THEN IF ThisConsType IN ('P','U') THEN sql_statement := 'ALTER TABLE '||parent_table||' DROP CONSTRAINT '||obj_name||' DROP INDEX'; ELSE sql_statement := 'ALTER TABLE '||parent_table||' DROP CONSTRAINT '||obj_name; END IF; ELSIF UPPER(obj_type) = 'TABLE' THEN sql_statement := 'DROP TABLE '||obj_name||' CASCADE CONSTRAINTS'; ELSIF UPPER(obj_type) IN ('INDEX','FUNCTION','PACKAGE','PROCEDURE','SEQUENCE','SYNONYM','TRIGGER','VIEW','TYPE') THEN sql_statement := 'DROP '||obj_type||' '||obj_name; ELSE DBMS_OUTPUT.PUT_LINE('Error using pk_drop, obj_type '''||obj_type||''' not recognised'); RETURN; END IF; DBMS_SQL.PARSE (cursor_handle,sql_statement,DBMS_SQL.NATIVE); return_value := DBMS_SQL.EXECUTE (cursor_handle); DBMS_SQL.CLOSE_CURSOR(cursor_handle); DBMS_OUTPUT.PUT_LINE('Dropped '''||obj_name||'''.'); EXCEPTION WHEN missing THEN DBMS_OUTPUT.PUT_LINE(obj_type||' '''||obj_name||''' not present.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: '||SQLERRM); DBMS_SQL.CLOSE_CURSOR(cursor_handle); END drop_if_there; -------------------------------------------------------------------------------------------- PROCEDURE tab_cons (p_tab_name VARCHAR2) IS CURSOR TabCons (TabName VARCHAR2) IS SELECT table_name, constraint_name, constraint_type FROM user_constraints WHERE table_name = UPPER(TabName); sql_string VARCHAR2(200); BEGIN DBMS_OUTPUT.ENABLE(1000000); FOR ThisRow IN TabCons(p_tab_name) LOOP BEGIN IF ThisRow.constraint_type IN ('P','U') THEN sql_string := 'ALTER TABLE '||ThisRow.table_name||' DROP CONSTRAINT '||ThisRow.constraint_name||' DROP INDEX'; ELSE sql_string := 'ALTER TABLE '||ThisRow.table_name||' DROP CONSTRAINT '||ThisRow.constraint_name; END IF; DBMS_OUTPUT.PUT_LINE(sql_string); EXECUTE IMMEDIATE sql_string; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END tab_cons; -------------------------------------------------------------------------------------------- END pk_drop; /