How do I call PL/SQL variables from within a Javascript function?

Question

I would like to display coordinates - retrieved from an Oracle database - as markers on a Google Map by calling pl/sql variables from within a javascript function. This is all done in APEX 5. Any help would be greatly appreciated!

What works: I select GEOMETRY (SDO_GEOMETRY) in the PHOTO table into l_lng (longitude) and l_lat (latitude) variables:

SELECT t.x Longitude,
  t.y Latitude
FROM photo,
  TABLE(sdo_util.getvertices(photo.geometry)) t;

Using DBMS_OUTPUT.put_line(l_lng); and DBMS_OUTPUT.put_line(l_lat);, I can see that l_lng and l_lat successfully stored the values because the output is:

-4.083714
50.315475

Specifically, the javascript variable myLatLng needs to pull the coordinates from l_lng and l_lat.

var myLatLng = {lat: l_lat, lng: l_lng};

My full code:

DECLARE

l_lng NUMBER;
l_lat NUMBER;


begin

SELECT t.x Longitude,
  t.y Latitude 
  INTO l_lng, l_lat
FROM photo,
  TABLE(sdo_util.getvertices(photo.geometry)) t
  WHERE photo.id=39;


htp.print('

<html>

<head>
    <style>
        #map {
            width: 500px;
            height: 400px;
        }
    </style>
    <script src="https://maps.googleapis.com/maps/api/js"></script>
    <script>
        function initialize() {

                var myLatLng = {
                    lat: l_lat,
                    lng: l_lng
                };



                var map = new google.maps.Map(document.getElementById("map"), {
                    zoom: 17,
                    center: myLatLng
                });
                var marker = new google.maps.Marker({
                    position: myLatLng,
                    map: map,
                    title: 'Hello World!'
               });
        }



    </script>

</head>

<body>
    <div id="map"></div>
</body>

</html>

');
end;

As suggested by @vmachan, I modified my ht.print statement to read:

htp.print('<html>');
htp.print('<head>');
htp.print('<style>
    #map {
        width: 500px;
        height: 400px;
        }
    </style>
');

htp.print('<INPUT TYPE="hidden" NAME="p_lng" VALUE="'||l_lng||'">');
htp.print('<INPUT TYPE="hidden" NAME="p_lat" VALUE="'||l_lat||'">');
htp.print(' 

    <script src="https://maps.googleapis.com/maps/api/js"></script>
    <script>
        function initialize() {

                var myLatLng = {
                    lat: p_lat,
                    lng: p_lng
                };



                var map = new google.maps.Map(document.getElementById("map"), {
                    zoom: 17,
                    center: myLatLng
                });
        }
    </script>
');
htp.print('</head>');
htp.print('
    <body>
        <div id="map"></div>
    </body>
');
htp.print('</html>');

I think you would need to break up the htp.print statement into multiples and then concatenate the lat, long values to your HTML. This example should help you, scroll to the instruct_personal_info PROCEDURE.. to see how it is done in that example — vmachan
– vmachan, Commented Dec 30, 2015 at 18:12
@vmachan I used your link - I believe I have done the right thing (the code validates and if I change the input type to text, it prints the correct coordinates on the page), but the map does not display though. It works if I hard code the coordinates to myLatLng like so: var myLatLng = { lng: -4.083714, lat: 50.315475 }; suggesting that the javascript is not picking up the values! I'll add my htp.print statement to my question. — Matt Mac
– Matt Mac, Commented Dec 30, 2015 at 18:51
You are outputting the values in text input elements, not as javascript variables. Either write code to extract the value from the input element into the javascript variables or output it as javascript to start with. — geocodezip
– geocodezip, Commented Dec 30, 2015 at 19:21

vmachan · Accepted Answer · 2015-12-30 19:22:10Z

1

Below is a snippet that I think if applied would work, this is what I was trying to suggest in my comment earlier.

htp.print('<script src="https://maps.googleapis.com/maps/api/js"></script>
<script>
    function initialize() {

            var myLatLng = {
                lat: ' || l_lng || ',
                lng: ' || l_lat || '
            };

            var map = new google.maps.Map(document.getElementById("map"), {
                zoom: 17,
                center: myLatLng
            });
    }
</script>');

You would need to address the string continuation on multiple lines when sending to htp.print, or just concatenate the whole html into one line which would work but may not be very readable.

Hope this helps.

answered Dec 30, 2015 at 19:22

vmachan

1,6821 gold badge10 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Matt Mac Over a year ago

It really did help thanks! Is it a simple task to do the opposite? In another part of my app I am using Google's Geolocation to get current location, and I need to take those coordinates and assign them to an item in my application.

vmachan Over a year ago

You mean take the items from Javascript and send it to the database? Unclear from your comment, could you elaborate?

Matt Mac Over a year ago

Assuming it's the best way, to take the lat and lng coordinates from JavaScript and assign them to PL/SQL variables which can in turn be used to set the value of a text field in the application.

vmachan Over a year ago

I think one way that usually works is call a stored procedure or SQL statement and send the Javascript values as parameters to it and then let the SQL update the underlying tables which can be used by other parts of your application.

Matt Mac Over a year ago

Thank you, I'll give that a whirl.

geocodezip · Accepted Answer · 2015-12-30 19:23:47Z

0

You are outputting the values in text input elements, not as javascript variables. Either write code to extract the value from the input element into the javascript variables or output it as javascript to start with. Replace:

htp.print('<INPUT TYPE="hidden" NAME="p_lng" VALUE="'||l_lng||'">');
htp.print('<INPUT TYPE="hidden" NAME="p_lat" VALUE="'||l_lat||'">');

with

htp.print('var p_lng='||l_lng||';');
htp.print('var p_lat='||l_lat||''');

answered Dec 30, 2015 at 19:23

geocodezip

162k14 gold badges228 silver badges256 bronze badges

Collectives™ on Stack Overflow

How do I call PL/SQL variables from within a Javascript function?

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related