Oracle and Yandex Maps integration

I faced with a requirement to add yandex integration. Yandex was chosen because Google Maps suck (no Maps for my region). And this is my solution.

Don't forget to add http://geocode-maps.yandex.ru to ACL if you are running Oracle 11g.

Example:

SELECT * from table(tb.YMAPS_API.GET_GEOCODE('Kazakhstan Almaty Klochkova', 'ru-RU'))

CREATE OR REPLACE TYPE "GEOLOCATION_TAG_T" AS OBJECT
( address_line            varchar2(4000 char),
    country_code            varchar2(2 char),
    country_name            varchar2(200 char),
    locality_name           varchar2(500 char),
    dependent_locality_type varchar2(500 char),
    dependent_locality_name varchar2(500 char),
    street_prefix             varchar2(200 char) ,
    street_name             varchar2(1000 char) ,
    house_number            varchar2(20 char),
    apartment_number        varchar2(20 char),
    position_x              number,
    position_y              number
)

/



  CREATE OR REPLACE TYPE "GEOLOCATION_TAGS_T"
AS TABLE OF GEOLOCATION_TAG_T;

/

  CREATE OR REPLACE PACKAGE "YMAPS_API"
as

  function get_geocode(
      p_search_base in varchar2,
      p_lang        in varchar2 := 'ru-RU')
    return geolocation_tags_t pipelined;
end ymaps_api;

/

create or replace
PACKAGE BODY      "YMAPS_API"
as
function get_geocode(
    p_search_base in varchar2,
    p_lang        in varchar2 := 'ru-RU')
  return geolocation_tags_t pipelined
is
  l_http_request utl_http.req;
  l_http_response utl_http.resp;
  l_clob clob;
  l_text varchar2(32767);
  l_xml xmltype;
  l_object geolocation_tag_t;
  l_dependent_locality_type varchar2(500 char);
  l_street_prefix           varchar2(200 char) ;
begin
  dbms_lob.createtemporary(l_clob, false);
  -- Make a HTTP request and get the response.
  l_http_request := utl_http.begin_request(utl_url.escape(url => 'http://geocode-maps.yandex.ru/1.x/?lang='||p_lang||
  '&results=100&format=xml&geocode='|| p_search_base , url_charset =>'UTF8' ), 'GET',utl_http.http_version_1_1);
  l_http_response := utl_http.get_response(l_http_request);
  -- Copy the response into the CLOB.
  begin
    loop
      utl_http.read_text(l_http_response, l_text, 32766);
      dbms_lob.writeappend (l_clob, length(l_text), l_text);
      dbms_output.put_line(l_text);
    end loop;
  exception
  when utl_http.end_of_body then
    utl_http.end_response(l_http_response);
  end;
  l_xml := xmltype.createxml(l_clob);
  for i in
  (
    select
      extract(addressdetails, '/Country/AddressLine/text()', 'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"').getstringval() addressline
    , extract(addressdetails, '/Country/CountryNameCode/text()', 'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"').getstringval()
      countrynamecode
    , extract(addressdetails, '/Country/CountryName/text()', 'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"').getstringval() countryname
    , extract(addressdetails, '/*/*/LocalityName/text()', 'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"').getstringval() city
    , extract(addressdetails, '/*/*/*/DependentLocalityName/text()', 'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"').getstringval()
      district
    , extract(addressdetails, '/*/Locality/Thoroughfare/ThoroughfareName/text()', 'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"')
      .getstringval() street
    , extract(addressdetails, '/*/Locality/Thoroughfare/Premise/PremiseNumber/text()', 'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"')
      .getstringval() house
    , extract(addressdetails, '/*/*/DependentLocality/Thoroughfare/ThoroughfareName/text()',
      'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"').getstringval() street1
    , extract(addressdetails, '/*/*/DependentLocality/Thoroughfare/Premise/PremiseNumber/text()' ,
      'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"').getstringval() house1
    , (substr(pos, 1,instr(pos, ' ')-1)) x
    , (substr(pos, instr(pos, ' ')  +1)) y
    , text
    from
      (
        select
          column_value
        , extract(column_value, '/*/*/*/GeocoderMetaData/*/*',
          'xmlns="http://maps.yandex.ru/geocoder/1.x" xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"' ) addressdetails
        , extract(column_value, '/*/*/*/GeocoderMetaData/text/text()', 'xmlns="http://maps.yandex.ru/geocoder/1.x"').getstringval() text
        , extract(column_value, '/*/*/*/pos/text()', 'xmlns="http://www.opengis.net/gml""') pos
        from
          table(xmlsequence(extract(l_xml,'/ymaps/GeoObjectCollection/*',
          'xmlns:gml="http://www.opengis.net/gml" xmlns="http://maps.yandex.ru/ymaps/1.x" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.opengis.net/gml"'
          )) )
      )
    where
      addressdetails is not null
  )
  loop
    l_object := geolocation_tag_t( i.countryname||', '|| i.addressline, i.countrynamecode , i.countryname, i.city , null, i.district,null,
    nvl(i.street,i.street1) , nvl(i.house,i.house1) , null , i.x , i.y );
    pipe row (l_object);
  end loop;
  dbms_lob.freetemporary(l_clob);
  return;
end;
begin
  null;
end ymaps_api;

Oracle user DDL

To get user DDL in oracle you need to run this statement:


select dbms_metadata.get_ddl('USER', 'JRL') from dual
union all
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'JRL') from dual
union all
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'JRL') from dual
union all
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'JRL') from dual
union all
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'JRL') from dual;

SQL Developer or JDeveloper and Oracle XE Database

If you ever tried to connect to Oracle Database XE 11g using Oracle SQL Developer or Oracle JDeveloper then you possibly has got an error like:

ORA-00604: error occurred at recursive SQL level 1
ORA-12705: Cannot access NLS data files or invalid environment specified.


To fix it you need to add following lines into \<ide folder>\ide\bin\ide.conf file:


AddVMOption -Duser.language=en
AddVMOption -Duser.region=us

Hello World

System.out.println("Welcome to my blog.");