This discussion is archived
14 Replies Latest reply: May 5, 2012 4:06 AM by jritschel RSS

Converting html and javascript into PL/SQL procedure

Andyindo Newbie
Currently Being Moderated
Hi, I'm trying to incorporate google maps into my apex report. I've managed to get it working with version3 of the API, when I call it in HTML as a header, however I need to add markers from a table, so I need to wrap it up in PL/SQL and loop through. However when I try and wrap the basic HTML code it fails to work as a PL/SQL region.
Original HTML code
<!DOCTYPE html>
<html>
  <head>
    <meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
    <style type="text/css">
      html { height: 100% }
      body { height: 100%; margin: 0; padding: 0 }
      #map_canvas { height: 100% }
    </style>
    <script type="text/javascript"
      src="http://maps.googleapis.com/maps/api/js?key=AIzaSyA387n7efUcis5emZlSNcOmwwZkhsFW8Qc&sensor=false">
    </script>
    <script type="text/javascript">
      function initialize() {
        var latLng = new google.maps.LatLng(-34.890542, 150.274856);
          var myOptions = {
          center: new google.maps.LatLng(-34.397, 150.644),
          zoom: 8,
          mapTypeId: google.maps.MapTypeId.ROADMAP
        };
        var map = new google.maps.Map(document.getElementById("map_canvas"),
            myOptions);
  var image = 'http://code.google.com/apis/maps/documentation/javascript/examples/images/beachflag.png';
  var myLatLng = new google.maps.LatLng(-34.890542, 150.274856);
  var beachMarker = new google.maps.Marker({
    position: latLng,
    map: map,
    icon: image
  });
  
  var marker = new google.maps.Marker({
      position: new google.maps.LatLng(-34.890542, 150.474856),
      map: map
    });
    
      }
    </script>
  </head>
  <body onload="initialize()">
    <div id="map_canvas" style="width:100%; height:100%"></div>
  </BODY>
</html>
Wrapped in PL/SQL and placed into an PL/SQL region on the same page.
htp.print('<html>');
  htp.print('<head>');
    htp.print('<meta name="viewport" content="initial-scale=1.0, user-scalable=no" />');
    htp.print('<style type="text/css">');
      htp.print('html { height: 100% }');
      htp.print('body { height: 100%; margin: 0; padding: 0 }');
      htp.print('#map_canvas { height: 100% }');
    htp.print('</style>');
    htp.print('<script type="text/javascript"');
      htp.print('src="http://maps.googleapis.com/maps/api/js?key=AIzaSyA387n7efUcis5emZlSNcOmwwZkhsFW8Qc&sensor=false">');
    htp.print('</script>');
    htp.print('<script type="text/javascript">');
      htp.print('function initialize() {');
        htp.print('var latLng = new google.maps.LatLng(-34.890542, 150.274856);');
          htp.print('var myOptions = {');
          htp.print('center: new google.maps.LatLng(-34.397, 150.644),');
          htp.print('zoom: 8,');
          htp.print('mapTypeId: google.maps.MapTypeId.ROADMAP');
        htp.print('};');
        htp.print('var map = new google.maps.Map(document.getElementById("map_canvas"),');
            htp.print('myOptions);');
  htp.print('var image = 'http://code.google.com/apis/maps/documentation/javascript/examples/images/beachflag.png';');
  htp.print('var myLatLng = new google.maps.LatLng(-34.890542, 150.274856);');
  htp.print('var beachMarker = new google.maps.Marker({');
    htp.print('position: latLng,');
    htp.print('map: map,');
    htp.print('icon: image');
  htp.print('});');
htp.print('  ');
  htp.print('var marker = new google.maps.Marker({');
      htp.print('position: new google.maps.LatLng(-34.890542, 150.474856),');
      htp.print('map: map');
    htp.print('});');
htp.print('    ');
      htp.print('}');
    htp.print('</script>');
  htp.print('</head>');
  htp.print('<body onload="initialize()">');
    htp.print('<div id="map_canvas" style="width:100%; height:100%"></div>');
  htp.print('</BODY>');
htp.print('</html>');
Anyone have any ideas?

Edited by: Andyindo on Mar 23, 2012 5:11 PM
  • 1. Re: Converting html and javascript into PL/SQL procedure
    369690 Newbie
    Currently Being Moderated
    Hello Andyindo,

    Try using BEGIN .. END.

    I have used a pl/sql package to generate menus like this. For better control i have just called the procedure in region source so that I can change it from outside.

    So in region source it is just

    msutil.generate_menu ;_
    PROCEDURE generate_menu IS 
    l_user_id NUMBER ;
    
    BEGIN 
     IF nvl( v('PRINTER_FRIENDLY'), 'NO') = 'YES' THEN 
        htp.p('<li><a href="f?p='||v('APP_ID')||':1:'||v('APP_SESSION')||'">Home</a>');
    ELSE 
    
    htp.p('<div id="myslidemenu" class="jqueryslidemenu">');
    
    htp.p('<ul>');
    
    for c1 in ( select ms_tabs_pk, tab_label, page_id           from ms_tabs 
                WHERE ((ms_tabs_pk IN ( SELECT ms_tabs_id FROM ms_tab_access 
                                      WHERE ms_users_id = ( SELECT ms_users_pk FROM ms_users WHERE upper(username) = v('APP_USER')) 
                                    )
                                        OR v('APP_USER') = 'INTERNAL') ) -- so internal can always ACCESS ALL the tabs!
                                -- version  1.04, following condition will ensure that the menu IS shown IN the LANGUAGE same AS the application.
                                        AND lang  = v('FSP_LANGUAGE_PREFERENCE')
                ORDER BY seq_num
         ) loop 
        htp.p('<li><a href="f?p='||v('APP_ID')||':'||c1.page_id||':'||v('APP_SESSION')||'">'||c1.tab_label||'</a>');
        htp.p('<ul>');
    ..
    ..
    ..
    ..
    ..
    
    htp.p('</ul>');
    end loop ;
    htp.p('</li>');
    
    htp.p('</ul>');
    htp.p('<br style="clear: left" />');
    htp.p('</div>');
    END IF ;
    END;
    Regards
    Sanjeev

    Edited by: sanjeevsapre on Mar 23, 2012 11:04 AM
  • 2. Re: Converting html and javascript into PL/SQL procedure
    Andyindo Newbie
    Currently Being Moderated
    Sorry, I should have mentioned I am using BEGIN and END;
    Ideally I want to create a procedure so its easier to manage, just want to get it working first...
  • 3. Re: Converting html and javascript into PL/SQL procedure
    jariola Guru
    Currently Being Moderated
    Hi,

    Check single quotes.
    I think this is one of problem lines
    htp.print('var image = 'http://code.google.com/apis/maps/documentation/javascript/examples/images/beachflag.png';');
    Try change it to
    htp.print('var image = ''http://code.google.com/apis/maps/documentation/javascript/examples/images/beachflag.png'';');
    Regards,
    Jari

    http://dbswh.webhop.net/dbswh/f?p=BLOG:HOME:0
  • 4. Re: Converting html and javascript into PL/SQL procedure
    Andyindo Newbie
    Currently Being Moderated
    Thanks, that was an error on the code I pasted in. I already had that double quoted. I'm pretty sure its to do the the html ,divs and how they are referenced, as if I take the HTML code and put it into the page footer, it doesnt work. Only in the page header.
  • 5. Re: Converting html and javascript into PL/SQL procedure
    Luis Cabral Pro
    Currently Being Moderated
    The HTML code you are trying to use has tags like html, head etc. Maybe inserting those tags into a region is messing up things? You should have only one head section per page, for instance.

    That would also explain why it works in the header but not in the footer...
  • 6. Re: Converting html and javascript into PL/SQL procedure
    Andyindo Newbie
    Currently Being Moderated
    Thanks, I removed the HTML and HEAD tags but the map will still only display in the HEADER section of the page. My code now looks like this:
    <script type="text/javascript"
          src="http://maps.googleapis.com/maps/api/js?key=AIzaSyA387n7efUcis5emZlSNcOmwwZkhsFW8Qc&sensor=false">
        </script>
        <script type="text/javascript">
          function initialize() {
            var latLng = new google.maps.LatLng(-34.890542, 150.274856);
              var myOptions = {
              center: new google.maps.LatLng(-34.397, 150.644),
              zoom: 8,
              mapTypeId: google.maps.MapTypeId.ROADMAP
            };
            var map = new google.maps.Map(document.getElementById("google_map"),
                myOptions);
      var image = 'http://code.google.com/apis/maps/documentation/javascript/examples/images/beachflag.png';
      var myLatLng = new google.maps.LatLng(-34.890542, 150.274856);
      var beachMarker = new google.maps.Marker({
        position: latLng,
        map: map,
        icon: image
      });
      
      var marker = new google.maps.Marker({
          position: new google.maps.LatLng(-34.890542, 150.474856),
          map: map
        });
        
          }
        </script>
    
    <body onload="initialize()">
        <div id="google_map" style="width:100%; height:100%"></div>
    </BODY>
    Edited by: Andyindo on Mar 27, 2012 12:02 PM
  • 7. Re: Converting html and javascript into PL/SQL procedure
    d_wilhelm Pro
    Currently Being Moderated
    Hello,

    why do you want to wrap all that in pl/sql?

    Why don't you initalize the map normally using javascript (or if you are using apex 4, try a google maps plugin) and then load your markers as a vector layer using for example geojson?

    Regards,
    Dirk
  • 8. Re: Converting html and javascript into PL/SQL procedure
    Andyindo Newbie
    Currently Being Moderated
    I thought wrapping it in pl/sql was the only way. How do you layer the vectors on top with json?
  • 9. Re: Converting html and javascript into PL/SQL procedure
    d_wilhelm Pro
    Currently Being Moderated
    Hello,

    ok, this is the code i'm using in a recent project, although it uses openlayers and not pure google maps.

    1. create a html region and put this as source
    < div style="width:100%; height:500px" id="map"></ div>
    2. add this to the page html header:
    <script src="http://maps.google.com/maps/api/js?v=3.6&amp;sensor=false"></script>
    <script src="http://openlayers.org/api/OpenLayers.js"></script>
    3. add this code to the javascript section of your page
    function init() {
    
      var map = new OpenLayers.Map({
        div: "map",
        projection: "EPSG:900913",
        numZoomLevels: 21
      });
    
    
      // create Google Mercator layers
      var gphy = new OpenLayers.Layer.Google(
          "Google Physical",
          {type: google.maps.MapTypeId.TERRAIN}
      );
      var gmap = new OpenLayers.Layer.Google(
          "Google Streets", // the default
          {numZoomLevels: 20}
      );
      var ghyb = new OpenLayers.Layer.Google(
          "Google Hybrid",
          {type: google.maps.MapTypeId.HYBRID, numZoomLevels: 20}
      );
      var gsat = new OpenLayers.Layer.Google(
          "Google Satellite",
          {type: google.maps.MapTypeId.SATELLITE, numZoomLevels: 22}
      );
    
      map.addLayers([
            gphy, gmap, gsat, ghyb
      ]);
    
      map.addControl(new OpenLayers.Control.LayerSwitcher());
      map.zoomToMaxExtent();
    
      //this gets the marker coords as geojson
      var ajaxRequest = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=getObjs',0);
      var gReturn = ajaxRequest.get();
    
      // this adds the features as vector layer
      var featurecollection = gReturn;
      var geojson_format = new OpenLayers.Format.GeoJSON();
      var vector_layer = new OpenLayers.Layer.Vector();
      map.addLayer(vector_layer);
      vector_layer.addFeatures(geojson_format.read(featurecollection));
    
    }
    4. add this to "Execute when Page Loads"
    init();
    5. create an application process called "getObjs" as
    begin
      geojson.getObjectCoordAsGeoJSON;
    end;
    6. create pl/sql package "geojson"
    create or replace PACKAGE GEOJSON AS 
    
      g_debug boolean := false;
      
      procedure getObjectCoordAsGeoJSON;
      
    END GEOJSON;
    create or replace PACKAGE BODY GEOJSON AS
    
      procedure printHeader(pMime in varchar2) as
      begin
        if not g_debug then
          owa_util.mime_header( nvl(pMime,'text/html') , FALSE );
          owa_util.http_header_close;
        end if;
      end printHeader;
    
      procedure print(p_text in varchar2) as
      begin
        if g_debug then
          dbms_output.put_line(p_text);
        else
          htp.prn(p_text);
        end if;
      end print;
      
      procedure print(p_clob in clob) as
        v_out varchar2(32000 char);
        v_clob clob := p_clob;
      begin
        while length(v_clob) > 0 loop
          if length(v_clob) > 3000 then
            v_out := substr(v_clob,1,3000);
            print(v_out);
            v_clob:= substr(v_clob,length(v_out)+1);
          else
            v_out := v_clob;
            print(v_out);
            v_clob := '';
            v_out := '';
          end if;
        end loop;
      end print;
    
      FUNCTION getXY (p_geom SDO_GEOMETRY) RETURN VARCHAR2 IS
        l_coords varchar2(32767);
      BEGIN
        for l_xy in (
          SELECT t.X, t.Y from
          TABLE(SDO_UTIL.GETVERTICES(p_geom)) t
        ) loop
          l_coords:= l_coords || '['|| trim(to_char(l_xy.X, '999999999D999', 'NLS_NUMERIC_CHARACTERS = ''.,''')) ||','|| trim(to_char(l_xy.Y, '999999999D999',  'NLS_NUMERIC_CHARACTERS = ''.,''')) ||'],';
        end loop;
        RETURN( substr(l_coords, 1, length(l_coords)-1) );
      END getXY;
    
    
      procedure getObjectCoordAsGeoJSON as
        v_json clob;
        v_obj clob;
      begin
        null;
        
        v_json := q'#{"type": "FeatureCollection","features": [#';
        
        for cur in (
          /* modifiy this query to get your own coords */
          select titel, geom from dkobj
        ) loop
          
          if length(v_obj) > 0 then
          v_obj := v_obj || q'#,#';
          end if;
          
          v_obj := v_obj || q'#{ "type": "Feature", "geometry": {"type": "Point", "coordinates": #' || getXY(cur.geom) || q'#}, "properties": {"Titel": "#' || cur.titel || q'#"}}#';
          
        end loop;
        
        v_json := v_json || v_obj;  
        v_json := v_json || q'#]}#';
      
        print(v_json);
      
      end getObjectCoordAsGeoJSON;
    
    END GEOJSON;
    In the package you will have to change the select query in the loop in the procedure getObjectCoordAsGeoJSON so that it will return your own coordinates.

    Now when the page loads it will initalize the openlayers client with google maps and load the marker coordinates from the database using the application process and the pl/sql package.

    I'm sure you can find other examples using pure google maps and geojson on google.

    Hope this helps,
    Dirk
  • 10. Re: Converting html and javascript into PL/SQL procedure
    rima Pro
    Currently Being Moderated
    Hi,

    there are plugins available at www.apex-plugin.com

    regards,
    Richard
  • 11. Re: Converting html and javascript into PL/SQL procedure
    Andyindo Newbie
    Currently Being Moderated
    If anyone is interested I managed to solve this. The main issue was in the HTML and I had to seperate the call to the div from the main script and rather create a new region and call the div and function in there. If anyone is also having issues with this then I'm happy to help.

    Thankyou everyone for your replies though and I've marked them as appropriate.
  • 12. Re: Converting html and javascript into PL/SQL procedure
    Andyindo Newbie
    Currently Being Moderated
    Regarding the plugins. I did use some of them but none of them suited my needs.
  • 13. Re: Converting html and javascript into PL/SQL procedure
    jritschel Newbie
    Currently Being Moderated
    I'm having this exact issue. Can you send me your code?

    Thanks,
    Jeff
  • 14. Re: Converting html and javascript into PL/SQL procedure
    jritschel Newbie
    Currently Being Moderated
    I ended up going the route as described above, using PL/SQL procedure calls to get JSON and then parse it in the javascript to build out the markers. One thing I did find useful was a freeware PL/SQL package called PLJSON (http://sourceforge.net/projects/pljson/) that helped to build properly formatted JSON. One word of warning, depending on the number of markers your map will display, performance can degrade rapidly. My map has about 2000 markers total and anything above 20 took 10+ seconds to load. you may want to take a look at https://developers.google.com/maps/articles/toomanymarkers to get some ideas of how to deal with this issue.

    I ended up changing my approach. I am now using a Google Fusion Table to contain my marker information and building my map from there. I am then using the Google API to keep the data synchronized between my application and the Fusion Table. The map now loads in 1-2 seconds. You can view my map page at http://www.dualrates.com/pls/apex/f?p=rc:30.

    -Jeff

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points