2.Drop_Table_Script.sql

set SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE DROP_EXISTING_TABLE(p_table_name IN VARCHAR2) IS
v_control NUMBER(1);
TABLE_DONT_EXIST EXCEPTION;
BEGIN
SELECT COUNT(1) 
  INTO v_control 
  FROM user_tables a
  WHERE UPPER(a.table_name) = UPPER(p_table_name); 
IF v_control >= 1 THEN 
  EXECUTE IMMEDIATE ('DROP TABLE '||p_table_name|| ' cascade constraints');
  dbms_output.put_line('Table deleted successfully...');
  commit;
ELSIF v_control < 2 THEN
    RAISE TABLE_DONT_EXIST;
END IF;
EXCEPTION WHEN TABLE_DONT_EXIST THEN 
DBMS_OUTPUT.PUT_LINE('TABLE OR VIEW WHICH YOU WISH TO DELETE DOES NOT EXIST!!!!!');
END DROP_EXISTING_TABLE; 
/



call DROP_EXISTING_TABLE('ADMIN');
call DROP_EXISTING_TABLE('DEPARTMENT');
--select * from user_tables;
call DROP_EXISTING_TABLE('PROGRAM');
call DROP_EXISTING_TABLE('COURSE');
call DROP_EXISTING_TABLE('COUNTRY');
call DROP_EXISTING_TABLE('STATE');
call DROP_EXISTING_TABLE('CITY');
call DROP_EXISTING_TABLE('ADDRESS');
call DROP_EXISTING_TABLE('STUDENT');
call DROP_EXISTING_TABLE('PROFESSOR');
call DROP_EXISTING_TABLE('PROFESSOR_SCHEDULE');
call DROP_EXISTING_TABLE('CLASS');
call DROP_EXISTING_TABLE('REGISTRATION');
call DROP_EXISTING_TABLE('FEE');
call DROP_EXISTING_TABLE('GRADE');