The Garden Shed: April 2013

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....

SQL Workshop > Object Browser
Create Procedure
Give your Procedure a name "MAPIT"
You can uncheck Arguments, we don't need any for this example
Next we'll need to enter some PL/SQL code ..

cursor c_klt is

select  
("MARKERS"."LATITUDE" || ',' || "MARKERS"."LONGTITUDE") AS geoloc, 
"MARKERS"."MARKER_NAME" as "MARKER_NAME"
from "MARKERS";

l_t number(3) := 0;

begin
htp.print('
<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
loop
htp.print('
 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;

htp.print('};
</script>;

<body onload="initialize()" style="font-family: Arial;border: 0 none;">
 <div id="map-canvas" style=" height: 400px; width: 800px;"></div>
</body>;
');

end;

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

Now create your page, keep it blank for now
Edit Page Properties (Right Click Page name - first item under page rendering)
In the HTML Header Section, HTML Header input box, add the API link:

<script src="http://maps.googleapis.com/maps/api/js?sensor=false" type="text/javascript">
</script>

In the same section, the Page HTML Body-Attribute:

onload="initialize()"

Now, apply changes and go back to Page overview
Right Click Region, to create a new region
Choose PL/SQL Dynamic Content
Give it a name, and on the next page, enter the Procedure name we created above. Don't forget the ;

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

The Garden Shed

22 Apr 2013

Oracle APEX & Google Maps Integration