rem fetch them from internet


as sys
begin
  dbms_network_acl_admin.create_acl 
  ( acl => 'IsoCountriesList.xml',
    description => 'Countries list on www.iso.org',
    principal => 'SCOTT',
    is_grant => true,
    privilege => 'connect',
    start_date => null,
    end_date => null
  );

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE
  ( acl => 'IsoCountriesList.xml',
    principal => 'SCOTT',
    is_grant => true,
    privilege => 'resolve'
  );
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL
  ( acl => 'IsoCountriesList.xml',
    host => 'www.iso.org'
  );
end;



create type country_t
as object
( name varchar2(200)
, code varchar2(2)
)
/

create type countries_table_t
as table of country_t
/


create or replace
package countries
is

function get_countries
return countries_table_t
;

end;
/


create or replace
package body countries
is

g_countries countries_table_t;

procedure initialize_countries
is
  l_clob clob;
  l_pos  integer;
  l_sc_pos  integer;
  l_country country_t;
  l_end_of_line varchar(2):= chr(13)||chr(10);
begin
  g_countries:= countries_table_t();

  l_clob:= httpuritype('http://www.iso.org/iso/list-en1-semic-3.txt').getClob();

  -- start processing with AFGHANISTAN
  l_pos:= instr( l_clob, 'AFGHANISTAN');
  
  -- find country (between l_pos and chr(13) - while l_pos < length(l_clob) there should be a semi-colon between the name and the code of a country
  loop
    l_sc_pos := instr( substr(l_clob, l_pos), ';');
    exit when l_sc_pos = 0;
    l_country := country_t
                 ( substr(l_clob, l_pos, l_sc_pos-1)
                 , substr(l_clob, l_pos + l_sc_pos , 2)
                 );
    g_countries.extend;
    g_countries( g_countries.last):= l_country;
    l_pos:= l_pos + instr( substr(l_clob, l_pos), l_end_of_line) + 1;
  end loop;
  
end initialize_countries; 


function get_countries
return countries_table_t
is
begin
  return g_countries;
end get_countries;


begin
  initialize_countries;

end countries;
/

create or replace
view countries_vw
as
select code
,      name
from   table(countries.get_countries)



select code, name 
from  table(countries.get_countries)
where code like 'SX%'

select code, name 
from   countries_vw
where  code like 'SX%'/