9 Replies Latest reply: Nov 9, 2013 11:03 PM by tparvaiz RSS

    can't pass parameters to a procedure

    tparvaiz

      Hi,

       

      I am having some issues when trying to pass parameters (Lat and Lng) to a procedure...

       

       

      here is the code

      -----------------Calling procedure-----------------------------------------------------

       

      MAPITB(:P1_CITY, :P1_LAT, :P1_LNG);

       

       

      --------------PROCEDURE-------------------------------------------------------------

      create or replace procedure "MAPITB"

      (citys IN VARCHAR2 default 'Toronto', Lat IN Number default 51.047617, Lng IN Number default -114.06435)

      is

       

      cursor c_klt is

       

      select 

      ("MARKERS"."LATITUDE" || ',' || "MARKERS"."LONGTITUDE") AS geoloc,

      "MARKERS"."MARKER_NAME" as "MARKER_NAME"

      from "MARKERS"

      where

          "MARKERS"."CITY" = citys;

       

      l_t number(3) := 0;

       

       

      begin

      htp.print('

      <script type="text/javascript">

      function initialize(Lat, Lng) {

      var mapOptions = {

        center: new google.maps.LatLng(Lat,Lng),

        zoom: 11,

        mapTypeId: google.maps.MapTypeId.ROADMAP

      };



      ---------------------


      above code works when I use static Lat and Lng values, something like this


          center: new google.maps.LatLng(54.047617,-113.06435),


      please advise

        • 1. Re: can't pass parameters to a procedure
          Mike Kutz

          1st problem

          you only have half of your procedure posted.

          You have a cursor any you aren't doing anything with it.

           

          2nd problem

          your input of the procedure takes in both City and LAT,LNG.

          However, you did not post any 'business rules' of how to handle the case when someone enters city,lat, and lng values on input.

           

          3rd -- confusion

          Are you saying, you want the brown LAT,LNG values that are inside the htp.print() to contain the values from the search result?

          If so, you need to incorporate the PL/SQL values as part of the string.


          htp.print('blahblahblah

             center: new google.maps.LatLng(' || LAT || ',' || LNG || '),

          blahblahblah' );

          • 2. Re: can't pass parameters to a procedure
            tparvaiz

            1) Here is the full script

             

            create or replace procedure "MAPITB"

            (citys IN VARCHAR2 default 'Toronto', Lat IN Number default 51.047617, Lng IN Number default -114.06435)

            is

             

             

            cursor c_klt is

             

             

            select 

            ("MARKERS"."LATITUDE" || ',' || "MARKERS"."LONGTITUDE") AS geoloc,

            "MARKERS"."MARKER_NAME" as "MARKER_NAME"

            from "MARKERS"

            where

                "MARKERS"."CITY" = citys;

             

             

            l_t number(3) := 0;

             

             

             

             

            begin

            htp.print('

            <script type="text/javascript">

            function initialize(Lat, Lng) {

            var mapOptions = {

              center: new google.maps.LatLng(Lat,Lng),

              zoom: 11,

              mapTypeId: google.maps.MapTypeId.ROADMAP

            };

             

             

                var map = new google.maps.Map(document.getElementById("map-canvas"), mapOptions("Lat", "Lng"));

            ');

             

             

            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;

             

            2) the above code is working fine with the exception of map center. EVENTUALLY, I would like my users to do the address search and SOME HOW it will return latitude and Longitude to pass through this procedure... for initial testing I've stored latitude in P1_LAT and longitude in P1_LNG APEX items... here is my code to call the procedure

             

            MAPITB(:P1_CITY, :P1_LAT, :P1_LNG);

             

             

            3) Can you please suggest what I am doing wrong in the above code

             

            Thanks again for your assistance

            • 3. Re: can't pass parameters to a procedure
              Mike Kutz

              I forgot to mention some coding suggestions:

              Liberally apply APEX_DEBUG.MESSAGE() throughout your code so that you can see what it is doing within the context of an APEX session.

              Also, 'comments' help ensure others know what you are thinking.

               

              If this reply doesn't help you, set up an example of this on apex.oracle.com so others (besides me) can look at it within a proper context.

              There may be more stuff going on than just "a problem with the code".

               

              It appears that the problem may be in the javascript that you are producing.

              At the top, you define the javascript function as "initialize(lat,lng)"

              But, at the bottom, you call it without any values

              onLoad="initialize();"

               

              To me, that means that the 'widget' is being initialized with latitude/longitude values of NULL,NULL

              (which sounds like something you didn't want)

               

              (also, lookup the difference between htp.print() and htp.p().   I've always used p())

              It appears that your bottom HTP.PRINT() code should look like this:

              htp.p('blah

                <body onLoad="initialize(' || lat || ',' || lng || ')" style="font-family: Arial;border: 0 none;">

              blah');

               

              Overall, this looks like it would be better off being developed as a Region Plugin.

              • 4. Re: can't pass parameters to a procedure
                tparvaiz

                I've created a test application @ apex.oracle.com for your review and comments

                 

                 

                Workspace: TPARVAIZ2

                Username: REDGREENRED@HOTMAIL.COM

                Password: kikuf6


                Application: Google Map (ID: 77232)

                Application ID/PW: TEST/TEST

                 

                -----------------------Recap-------------------

                As mentioned before, this is just a test application so I've used some fixed values to get it going.


                - Eventually, I will allow my users to enter the address and based on the address I will retrieve latitude and longitude information ( still need to figure out how).

                - once I'll have a latitude and longitude information in P1_LAT and P1_LNG items then I'll pass it to the procedure as follows MAPITB(:P1_CITY, :P1_LAT, :P1_LNG);

                - everything is working with the exception that I am unable to use parameters to set the center of the map...currently I have this statement


                center: new google.maps.LatLng(51.047617,-114.06435)

                and want to replace it with something like this

                center: new google.maps.LatLng(LAT,LNG)


                Thanks again for your assistance

                • 5. Re: can't pass parameters to a procedure
                  AndyH

                  tparvaiz wrote:

                   

                  I've created a test application @ apex.oracle.com for your review and comments

                   

                   

                  Workspace: TPARVAIZ2

                  Username: REDGREENRED@HOTMAIL.COM

                  Password: kikuf6


                  Application: Google Map (ID: 77232)

                  Application ID/PW: TEST/TEST

                           

                  I agree with MikeKutz - you weren't using the parameters in your MAPITB procedure - I've fixed that.

                   

                  Also, you didn't have a way for the page to be rerun when you made changes to the parameters - so I've added a submit button.

                   

                  Although playing with the Google stuff is fun, it looks like you should try and get a basic grasp of how APEX works before you investigating incorporating other code within it.

                  • 6. Re: can't pass parameters to a procedure
                    tparvaiz

                    Thanks a lot both of you... that's exactly what I was trying to do.

                     

                    any suggestions as to how I can allow my user to enter the address and based on the button click I get my latitude and longitude items set.

                     

                    Appreciate it again

                    • 7. Re: can't pass parameters to a procedure
                      Vite DBA

                      This can be done by calling a Google web service

                       

                      see

                       

                      https://developers.google.com/maps/documentation/geocoding/

                       

                      Regards

                      Andre

                      • 8. Re: can't pass parameters to a procedure
                        tparvaiz

                        Hi,

                         

                        I am able to use APEX-PLUGIN available as follows to get my Lat/Lng coordinates

                         

                        http://www.apex-plugin.com/oracle-apex-plugins/item-plugin/addressvalidate-display-map_88.html

                         

                        it's working fine with a small glitch.

                         

                        Scenario A)

                        - when I enter the address and press submit button then my Map get's refreshed before I even get the coordinates from the plugin (map uses default values)

                        - and then my coordinates item P1_COORDINATES gets updated (obviously, a step later)

                        - when I press the submit button again for the second time, then I get the map with right coordinates

                         

                        Seems like my processing sequence is wrong... somehow, when I press the button then I want my plugin to get refreshed first before anything else

                         

                        Scenario B)

                        - when I enter the address and click somewhere outside the box, my coordinates (item P1_COORDINATES) get refreshed immediately... probably the plugin is using "on Change" sort of function

                        - now, when I press the submit button then I get my correct coordinates on the map in the first attempt.

                         

                         

                        I've created a test application @ apex.oracle.com for your review and comments

                         

                         

                        Workspace: TPARVAIZ2

                        Username: REDGREENRED@HOTMAIL.COM

                        Password: kikuf6


                        Application 71508 - Google Map 2

                         

                        Application ID/PW: TEST/TEST

                         

                         

                        Thanks

                        • 9. Re: can't pass parameters to a procedure
                          tparvaiz

                          any thoughts?