-- Drop the ACS Reference US States data
--
-- @author jon@jongriffin.com
-- @cvs-id $Id: ref-us-states-drop.sql,v 1.1.1.1 2002/07/09 17:35:11 rmello Exp $

set serveroutput on

-- drop all associated tables and packages
-- I am not sure this is a good idea since we have no way to register
-- if any other packages are using this data.

-- This will probably fail if their is a child table using this.
-- I can probably make this cleaner also, but ... no time today

declare
    cursor refsrc_cur is
	 select   table_name,
                  package_name,
                  repository_id
	 from     acs_reference_repositories
	 where upper(table_name) = 'US_STATES';
begin
    for rec in refsrc_cur loop
	 dbms_output.put_line('Dropping ' || rec.table_name);
	 execute immediate 'drop table ' || rec.table_name;
	 if rec.package_name is not null then
	     execute immediate 'drop package ' || rec.package_name;
         end if;
         acs_reference.delete(rec.repository_id);
    end loop;
end;
/
show errors