Rem    NAME 
Rem      ckupgdrp.sql
Rem    DESCRIPTION
Rem      Drop Upg_<tables> used for Specific PreUpgrades
Rem    RETURNS
Rem 	 None
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem    CREATED
Rem	  12/04/1999

DECLARE

  procedure drop_table (l_table IN varchar2) is
    csr NUMBER;
    table_does_not_exist EXCEPTION;
    PRAGMA EXCEPTION_INIT(table_does_not_exist, -00942);
  begin 
    csr := DBMS_SQL.OPEN_CURSOR;
    DBMS_OUTPUT.PUT_LINE('Dropping table '||l_table);
    DBMS_SQL.PARSE(csr,'drop table '||l_table, DBMS_SQL.V7);
    DBMS_SQL.CLOSE_CURSOR(csr); 
  exception
    when table_does_not_exist then
        DBMS_OUTPUT.PUT_LINE('... table does not exist');
        DBMS_SQL.CLOSE_CURSOR(csr); 
    when others then
      DBMS_OUTPUT.PUT_LINE (substrb(sqlerrm, 1, 200));
      DBMS_SQL.CLOSE_CURSOR(csr); 
      RAISE;
  end;

BEGIN

   update ck_migration_objects set ci_created = 'N' where ci_stage = 'CKUPGDRP';

   drop_table('UPG_APP');
   drop_table('UPG_MOD');
   drop_table('UPG_CI_IGR');
   drop_table('UPG_CI_ITEMS');
   drop_table('UPG_CI_MDTU');
   drop_table('UPG_CI_MUN');
   drop_table('UPG_LVI');
   drop_table('UPG_RM_TEXT_LINES');
   drop_table('UPG_CI_NAI');
   drop_table('UPG_CI_LAN');
   drop_table('UPG_APPLICATION_LOGIC');
   drop_table('UPG_RM_TEXT_LINES');
   drop_table('UPG_CI_PRU');
   drop_table('UPG_CI_MODMOD');
   drop_table('UPG_CI_DIA');
   drop_table('UPG_CI_DEU');
   drop_table('UPG_CI_UPR');
   drop_table('UPG_GLOBAL_NAMES');
   drop_table('UPG_DEPENDENCIES');
   drop_table('UPG_SDD_UEE');
   drop_table('UPG_SDD_UES');
   drop_table('UPG_SDW_ACCESS_RIGHTS');

   update ck_migration_objects set ci_created = 'Y' where ci_stage = 'CKUPGDRP';

   commit;

EXCEPTION 
   when others then
   dbms_output.put_line (' ** Exception Raised ** ');
   dbms_output.put_line ('...ERROR: '||substrb(sqlerrm, 1, 200));
   raise;

END;
/
