22 Apr 2013

Oracle APEX & Google Maps Integration

I've read so many blogs about how to integrate Google Maps into Oracle Application Express, but most of them were very old and maybe needed updating for the current versions of both Apex and Maps.

With that in mind, this was using Oracle Apex v4.2.1 and Maps API version 3

It actually isn't too hard to achieve, but there's one or two points not clear in the sites I read.

Once you have Apex running, all you need then is an access key from Google API. In version 3 you do not need to put the key reference in, so I'm not sure how it knows it's your key! Perhaps through the referrer sites.

This example assumes many markers, on a single map, and the marker details are kept in a database table

Anyway, here goes....
  1. SQL Workshop > Object Browser
  2. Create Procedure
  3. Give your Procedure a name "MAPIT"
  4. You can uncheck Arguments, we don't need any for this example
  5. Next we'll need to enter some PL/SQL code ..
  6. cursor c_klt is
    ("MARKERS"."LATITUDE" || ',' || "MARKERS"."LONGTITUDE") AS geoloc, 
    from "MARKERS";
    l_t number(3) := 0;
    <script type="text/javascript">
     function initialize() {
     var mapOptions = { 
      center: new google.maps.LatLng(-33.771214,151.080967),
      zoom: 11,
      mapTypeId: google.maps.MapTypeId.ROADMAP
    var map = new google.maps.Map(document.getElementById("map-canvas"), mapOptions);
    for r_klt in c_klt
     var geocoder = new google.maps.LatLng('||r_klt.geoloc||''||');
     var marker = new google.maps.Marker(
      {position: geocoder,map: map,animation:google.maps.Animation.DROP,title:"Marker: '||r_klt.MARKER_NAME||''||'"});
    l_t := l_t + 1;
    end loop;
    <body onload="initialize()" style="font-family: Arial;border: 0 none;">
     <div id="map-canvas" style=" height: 400px; width: 800px;"></div>
    Couple of things to note: (i.e. the things I did wrong)

    • The ; at the end of the SQL statement
    • The canvas name, just make sure it's consistent
    • The height and width of the DIV "map-canvas". Change to match your page

  7. Now create your page, keep it blank for now
  8. Edit Page Properties (Right Click Page name - first item under page rendering)
  9. In the HTML Header Section, HTML Header input box, add the API link:
  10. <script src="http://maps.googleapis.com/maps/api/js?sensor=false" type="text/javascript">
  11. In the same section, the Page HTML Body-Attribute:
  12. onload="initialize()"
  13. Now, apply changes and go back to Page overview
  14. Right Click Region, to create a new region
  15. Choose PL/SQL Dynamic Content
  16. Give it a name, and on the next page, enter the Procedure name we created above. Don't forget the ;
  17. MAPIT;
That's it. Make sure you've got geo coded data in your tables. (There's plenty of blogs about geo-coding on the fly)

Good Luck