CONNECT username/password@<host>
SPOOL <filename.txt>

set serveroutput on
set feedback on

prompt -- Deleting street ids and related information for streets
prompt -- For Counties cavan,Monaghan and Tipperary

declare 
cursor c1 is 
SELECT DISTINCT street_id FROM GEO_TB_POST_TOWN_STREETS 
WHERE county_id IN (15,18,24)
UNION
SELECT DISTINCT street_id FROM 
GEO_TB_LOCALITY_STREETS 
WHERE county_id IN (15,18,24);

cursor c2 is
SELECT DISTINCT building_group_id 
FROM 
GEO_TB_STREET_BUILDING_GROUPS
WHERE street_id in
(SELECT DISTINCT street_id FROM GEO_TB_POST_TOWN_STREETS 
WHERE county_id IN (15,18,24)
UNION
SELECT DISTINCT street_id FROM 
GEO_TB_LOCALITY_STREETS 
WHERE county_id IN (15,18,24));

cursor c3 is
SELECT DISTINCT building_id 
FROM 
GEO_TB_STREET_BUILDINGS
WHERE street_id IN
(SELECT DISTINCT street_id FROM GEO_TB_POST_TOWN_STREETS 
WHERE county_id IN (15,18,24)
UNION
SELECT DISTINCT street_id FROM 
GEO_TB_LOCALITY_STREETS 
WHERE county_id IN (15,18,24));

begin
     
for c1_rec in c1 loop
  
     delete from GEO_TB_STREET_BUILDINGS sb WHERE 
     sb.street_id = c1_rec.street_id;

     delete from GEO_TB_STREET_ALIASES sa WHERE 
     sa.street_id = c1_rec.street_id;

     delete from GEO_TB_STREET_BUILDING_GROUPS sbg WHERE 
     sbg.street_id = c1_rec.street_id;

     delete from GEO_TB_STREET_ORGANISATIONS sorg WHERE 
     sorg.street_id = c1_rec.street_id;
      
     delete from GEO_TB_STREET_HOUSE_NUMBERS shnr WHERE 
     shnr.street_id = c1_rec.street_id;
commit;
end loop;
      
for c2_rec in c2 loop

     delete from GEO_TB_BUILDING_GROUP_HOUSE_NR bghnr WHERE
      bghnr.building_group_id =c2_rec.building_group_id;
      
      delete from GEO_TB_BUILDING_GROUP_ALIASES bgal WHERE
      bgal.building_group_id =c2_rec.building_group_id;

      delete from GEO_TB_BUILDING_GROUP_ORGS bgorg WHERE
      bgorg.building_group_id =c2_rec.building_group_id;

      delete from GEO_TB_BUILDING_GRP_BUILDINGS bgbld WHERE
      bgbld.building_group_id =c2_rec.building_group_id;

commit;
end loop;

for c3_rec in c3 loop
  delete from GEO_TB_BUILDING_HOUSE_NUMBERS bhnr WHERE
  bhnr.building_id = c3_rec.building_id;


  delete from GEO_TB_BUILDING_ALIASES bal WHERE
  bal.building_id = c3_rec.building_id;

  delete from GEO_TB_BUILDING_ORGANISATIONS borg WHERE
  borg.building_id =c3_rec.building_id;
  
  delete from GEO_TB_HNR_ORGANISATIONS bhorg WHERE
  bhorg.building_id =c3_rec.building_id;

commit; 
end loop;
     
for c1_rec in c1 loop
     delete from GEO_TB_POST_TOWN_STREETS pts WHERE 
     pts.street_id = c1_rec.street_id;
    
     delete from GEO_TB_LOCALITY_STREETS ls WHERE 
     ls.street_id = c1_rec.street_id;

commit;
end loop;
end;
/

prompt --Deleting all the data from geo_tb_subloc_county_aliases table

TRUNCATE TABLE GEO_TB_SUBLOC_COUNTY_ALIASES;
spool off
exit;