Step 1:
=====
Create a Static Container Region.
Name: Saler Visiting Map
HTML Code:
<!-- Leaflet CSS & JS -->
<link rel="stylesheet" href="https://unpkg.com/leaflet/dist/leaflet.css" />
<script src="https://unpkg.com/leaflet/dist/leaflet.js"></script>
<div id="map" style="height: 700px;"></div>
Step 2:
=====
Create Page Items.
- P66_SALESREP_NUMBER
- P66_FROM_DATE
- P66_TO_DATE
And Create a Submit Button.
Step 3:
=====
=====
Create an Ajax Callback process.
Name: GET_LOCATIONS_JSON
Name: GET_LOCATIONS_JSON
PLSQL Code:
DECLARE
l_json CLOB;
l_buffer VARCHAR2(32767);
l_amount BINARY_INTEGER := 32000;
l_pos INTEGER := 1;
l_length INTEGER;
BEGIN
-- ✅ Build JSON into CLOB explicitly
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'lat' VALUE LATITUDE,
'lng' VALUE LONGITUDE,
'title' VALUE 'Retailer Location'
)
RETURNING CLOB -- ✅ Force return as CLOB
)
INTO l_json
FROM XX_RETAILER
WHERE SALESREP_NUMBER = :P66_SALESREP_NUMBER
AND (:P66_FROM_DATE IS NULL OR TRUNC(CREATION_DATE) >= TO_DATE(:P66_FROM_DATE, 'YYYY-MM-DD'))
AND (:P66_TO_DATE IS NULL OR TRUNC(CREATION_DATE) <= TO_DATE(:P66_TO_DATE, 'YYYY-MM-DD'))
AND LATITUDE BETWEEN -90 AND 90
AND LONGITUDE BETWEEN -180 AND 180
AND LATITUDE IS NOT NULL
AND LONGITUDE IS NOT NULL;
-- যদি ডেটা না থাকে, তাহলে ফাঁকা JSON পাঠান
IF l_json IS NULL THEN
l_json := '[]';
END IF;
-- HTTP Header
owa_util.mime_header('application/json', FALSE);
htp.p('Cache-Control: no-cache');
owa_util.http_header_close;
-- ✅ Send CLOB in chunks (JSON Data দেখতে পারেন এবং debugging করতে পারেন)
l_length := DBMS_LOB.getlength(l_json);
WHILE l_pos <= l_length LOOP
DBMS_LOB.READ(l_json, l_amount, l_pos, l_buffer);
htp.p(l_buffer);
l_pos := l_pos + l_amount;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
owa_util.mime_header('application/json', FALSE);
htp.p('Cache-Control: no-cache');
owa_util.http_header_close;
htp.p('[]');
DBMS_OUTPUT.put_line('Error: ' || SQLERRM); --- SQL ERROR SHOW
END;
---Note: এটা SQL Commands এ চালিয়ে দেখুন JSON করে LATITUDE , LONGITUDE দেখায় কিনা। যদি না দেখায় তাহলে কোড রিভিউ করুন।
Step 4:
=====
=====
Create Page Load Dynamic Action.
Action Name: Ajax call by JavaScript
Action: Execute JavaScript Code
Code:
Action Name: Ajax call by JavaScript
Action: Execute JavaScript Code
Code:
function drawMap(locations) {
var map = L.map('map').setView([23.8103, 90.4125], 7); // Center: Dhaka
L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
maxZoom: 18,
}).addTo(map);
locations.forEach(function(loc) {
L.marker([loc.lat, loc.lng]).addTo(map).bindPopup(loc.title);
});
}
// AJAX Call to Server-side Callback
apex.server.process('GET_LOCATIONS_JSON', { //Ajax Name
pageItems: '#P66_SALESREP_NUMBER, #P66_FROM_DATE, #P66_TO_DATE'
}, {
success: function(data) {
let locations = JSON.parse(data);
drawMap(locations);
},
dataType: 'text'
});
Tags:
Oracle Apex (NPG)