14 Replies Latest reply: May 5, 2012 6:06 AM by Jritschel-Oracle RSS

    Converting html and javascript into PL/SQL procedure

    Andyindo
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            Hi,

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

                            regards,
                            Richard
                            • 11. Re: Converting html and javascript into PL/SQL procedure
                              Andyindo
                              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
                                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-Oracle
                                  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-Oracle
                                    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