This discussion is archived
9 Replies Latest reply: Nov 9, 2013 9:03 PM by tparvaiz RSS

can't pass parameters to a procedure

tparvaiz Newbie
Currently Being Moderated

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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    any thoughts?

Legend

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