Tech, Oracle, user experience, coffee, design standards, and shameless ranting

Oracle Apex, jQuery and GIS integration

Oracle Apex, jQuery and GIS integration

I recently needed to provide mapping functionality in an APEX app I was developing.

In V3+ of Oracle apex, you can create interactive reports like this

Buildings_cog

Now when you click the cog, you geta map icon at the top:

I added a <li> element to the interactive report on-the-fly using jQuery (depending if it finds a div with an attribute of ‘gis’ on the page within the report).

I’ve written some jQuery for the onclick event of the map href to loop through the displayed rows, looking for a DIV with an invisible attribute called ‘gis’.  I then assemble these into a GEORSS file and store it in a public locations. The href from the map then calls a page showing the map and settings its source to the newly created file.

In reality, the file is actually a CLOB stored in the oracle database, and I use the ability of oracle to call a package from a URL to return the clob with the GEORSS file from my database table.

Buildings_cog_map

When clicked, the icon opens up an arcview  GIS map with the current interactive report data displayed.

I’ve used colorbox for jQuery to show the map in a lightbox style pop-up window.

Share

Phonegap geolocation is slow on Android

Whilst developing a geolocation based app for android using Cordova 3.3, I found that the geolocated position was not updated very fast.

Turns out that there is a setting in the geolocation plugin java files that control the minimum time that phonegap / Cordova will check for changes.

This is regardless of wether I use watchPosition or getCurrentPosition.

If I want it to check at least every 5 seconds, I can change the files as follows:

GeoBroker.java
Change line 75
from:
this.getCurrentLocation(callbackContext, enableHighAccuracy, args.optInt(2, 60000));
to:
this.getCurrentLocation(callbackContext, enableHighAccuracy, args.optInt(2, 5000));

GPSListener.java
Change line 44
from:
this.locationManager.requestLocationUpdates(LocationManager.GPS_PROVIDER, 60000, 0, this);
to:
this.locationManager.requestLocationUpdates(LocationManager.GPS_PROVIDER, 5000, 0, this);

Then remake your project.

Worked fine for me and the Nexus 7 (2013) is updating my map every second or two.

Hope this helps someone.

Mark

Share

Nexus 10 KitKat

Oh, joy of joy, my Nexus 10 has just received its OTA upgrade!
Installing currently.

Woop

Share

oracle apex checkbox select all

Recently I needed to add a check box column to an interactive

report on Oracle 4.2.

I used the APEX_ITEM functionin my report’s SQL to generate the checkbox, but I needed a Check All option on the column header.

Simple, just rename the column header:

[input type="checkbox" onclick="$f_CheckFirstColumn(this)" />

This was implemented after repeated requests in Apex 3.

Works a treat.

I have a delete button which ive placed to the right of the interactive report bar. and it submits the page where i have a process deleting the related records:

FOR I in 1..APEX_APPLICATION.G_F01.COUNT LOOP
DELETE FROM document WHERE resid = APEX_APPLICATION.G_F01(i);
END LOOP;

All good.
image

Share

Oracle XMLDB custom authentication for webdav / http

Oh what joy Oracle has brought me today!!

I’ve been struggling for a few days trying to work out how best to integrate Oracle and Active Directory to allow users to map a drive letter to Oracle XMLDB to use it as a file store, extract EXIF, lat / lon and so on…

I’d finally conceded that integrating Oracle and AD via OID was not feasible given the constraints in the organisations, so was about to write some scripts to create new Oracle users whenever AD was updated. (800 users) when I came across a PDF from Mark Drake, product manager at Oracle.

http://www.oracle.com/in/javaonedevelop/h3-2-levaraging-full-power-oracle-400288-en-in.pdf

Turns out you can (seems to be a beta feature at the moment, but carry on regardless!!) write a custom authentication scheme and tell XMLDB to use it whenever a particular resource path is requested.

Anyway, the PDF had quite a few typos and wasn’t complete, so here is my attempt at it.

Pseudo code:
1) Create a function that returns
Marky
or
Wrong username

2) Call addAuthenticationMethod to register the function

3) Call addAuthenticationMapping to link the scheme to a path in XMLDB

4) Run a little bit of code to enable the whole thing (temporary fix for now seemingly)

So here is the detail of my implementation:

1)

1
2
3
4
5
6
7
8
9
10
create or replace function myschema.doAuthenticate(URL varchar2, AUTHINFO VARCHAR2) return varchar2
is
V_USERNAME VARCHAR2(300);
V_PASSWORD VARCHAR2(300);
begin
--Id look at the AUTHINFO variable and compare the values against a table in my app, but for here I'm just pretending I've done that
return '<custom_authenticate><user>Sparky</user></custom_authenticate>';
end;
 
Grant execute on myschema.doAuthenticate to public;

2)

1
2
3
4
5
6
7
8
begin dbms_xdb.addAuthenticationMethod(
NAME=>'HTTP_REPO',
description=> 'Test authentication method, always return success',
implement_schema =>'MYSCHEMA',
implement_method =>'doauthenticate',
language =>'PL/SQL'
);
end;

3)

1
exec dbms_xdb.addAuthenticationMapping( PATTERN=>'/repository/*', NAME => 'HTTP_REPO');

–/repository is a folder I created on the root of XMLDB with the ‘/sys/acls/all_all_acl.xml’ ACL

4)

1
2
3
 
--Run this as SYS
alter system set event='31098 trace name context forever, level 0x8000' scope=spfile

In the middle of debugging it, will let you know if I have any other problems.

Loving it, but wish it were documented somewhere, or a good complete example given.

This will save a load of effort maintaining and synchronising between AD and Oracle, although if you already implement this syncing, then obviously ignore everything i’ve just said!!!

Good luck
Mark

Share

Oracle on Amazon RDS with APEX listener

Oracle on Amazon RDS with APEX listener

I’m in the process of setting up Oracle 11g with APEX and XMLDB on Amazon Web Services. I thought i’d document the process and what i do to get it all (hopefully) working together. Also, this will act as reference document for me when I do it again later this week.

So here we go…

Logon to Amazon AWS and choose the RDS section

 

 

 

 

 

 

 

Choose the Option Group menu option

Click ‘Create Group’

Call it anything you like I called mine APEX-XMLDB

Give it a description

Choose which database engine and version you are going to apply this option group to

Click ‘Create’

Click on the Option group Menu

Select your newly created option group

Add Options (if this menu item is not visible then you must be using an incompatible Oracle engine (SE1 for instance)

I added

  • APEX
  • APEX-DEV
  • XMLDB
I didn’t need Enterprise Manager, so I left that one out.

Now choose ‘DB Instances’ and click ‘Launch DB Instance’

Choose the Oracle SE instance type

Fill in the oracle database configuration details

If like me you never select ‘No’ in the multi A-Z deployment field, do it now.

Click ‘Continue’

Enter your chosen SID

Choose the apex-xmldb option group you created earlier from the drop down

Click ‘Continue’

Choose a retention period for backups (I choose zero days for now)

Click ‘Continue’

Confirm all your setting on the final page

Click ‘Launch DB Instance’

This will take about 40 minutes or so.

Ok, step 1 done.

 

Now for the APEX listener

Im using my local machine for this part, but you could start a new EC2 instance and configure it from there.

 

Ok, so my config at this point is:

I have an Oracle 11 RDS instance up and running

I’ve downloaded Apex Listener 2.0 and extracted to my local harddrive as c:\apexlistener

I’ve downloaded SQLDEVELOPER with the built in JDK

I’ve downloaded APEX 4.1 and extracted to the C:\ folder (to get the images folder) so I have C:\APEX and C:\apex\images etc…

 

Log into SQLdeveloper and connect to your RDS instance using the master username / password

Log in to SQL Developer with my AmAZON RDS MASTER username I setup when I created my instance.

alter user apex_public_user account unlock;

alter user apex_public_user identified by mypassword;

grant APEX_ADMINISTRATOR_ROLE to master;

@c:\apex\apxchpwd.sql

[Now choose a new apex admin password]

I typed:  myadminpassword

So now load a command prompt [Start button / Run / CMD]

cd c:\apexlistener

type: JAVA -jar APEX.war

You will be prompted for the name of the database server

Enter the Public DNS name of your Amazon Oracle RDS instance

Enter the port number of your Oracle listener – mine was 1521

Choose option [2] to specify a SID and enter the SID you chose when you started the Oracle Instance

Enter the name of the apex_public_user (I just pressed enter)

Enter the password for the apex_public_user

next choose the passwords for the apex_listener and apex_rest_public_user users (I pressed [1] to accept teh same as the apex_public_user

Click [1] to enter stand alone mode

Type the location of the Apex images folder

C:\APEX\IMAGES

Choose a port to access apex listsner on

8080

Now the server will start and you will be able to open a browser and enter :

http://localhost:8080/apex

You will enter

INTERNAL

ADMIN

myadminpassword

And we’re in!

Next stop is to get xmldb up and running.

Share

ESRI offline cached map files on an Android tablet

Well, this is a challenge.

Requirement:

  • To have an offline app on a tablet that contains a full set of tiles for offline viewing.
  • As we are an ESRI house, we must use ESRI technology
  • Must consume shapes files, show aerial layers and OS layers
  • Must show different zoom levels

I’m still working all this out and wasnt at all stuck on the ESRI javascript API as I didnt think I could store tiles offline.

I’ve tried

Cloudmade Leaflet

Open Layers

ESRI javascript API

Maybe some more APIS, im not sure

 

I though about using MBTILES and writing some code to fetch the base64 data based on what the map API needed, but this was very tricky to deploy to the devices. I also wanted to be able to update the data regularly.

Probably I’m warbling a bit here, but I’ve been at this for the last few days, and have been thinking about it for about three months now.

Anyway – this is what I think I have working:

Download map tiles to .png from an ARCGIS cache server

Store these on a folder on the android device

(or bundle them with Phonegap in a subfolder of assets/WWW)

Grab the json from the ESRI mapserver endpoint

i.e. http://myESRIserver/MapServer?f=pjson

Put this is a text file on the local disk

Point the var tiledMapServiceLayer to the text file

Extend ArcGISTilesMapServiceLayer to fetch the .png from the local folder based on the x y z parameters passed to this method rather than the server.

Phew. Does that make sense? I’ll try firm up the instructions when I get free of it.

For now, I have a semi-working; I haven’t copied all the tiles over to the local folder yet, for now im using a static jpg file, so the entire Scotland area is built up of a 250px * 250px view of central scotland!

More soon.

has anyone else done this? i couldn’t find much online. I think its a really handy thing to have offline ESRI based maps.

Once I’ve done it in ESRI, i intent to port it to openlayers and leaflet.

I’m an Oracle specialist  normally, but its nice to get my teeth into some mapping. i love the visual aspect of development.

Isn’t GIS wonderful?

Mark

Share

How to set Oracle Apex Checkbox default value

By Craig Glendenning

As often is the case when I blog about something, I spent waaaaay too much time trying to figure out how to do something that is seemingly very simple. In this case my trivial requirement was to create a single checkbox, that when checked, puts “Yes” in the database, and when unchecked, puts “No” in the database. Here is how to do it:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
- Create your Checkbox item.
- Make sure that the following are set in the "List of Values" section of the checkbox item:
 
Named LOV: - Select Named LOV -
Display Extra Values: No
Dynamic Translation: - Not Translated -
Number Of Columns: 1
Display Null: No
Null Display Value:
Null Return Value:
List of values definition: STATIC:;Yes
 
Save these changes, if you made any and then add a computation to the page as follows:
 
Click the "Create" icon.
Choose "Item on This Page"
click next...
 
Compute Item: (The checkbox you want to modify)
Sequence: (choose the default)
Computation Point: After Submit
Computation Type: Static Assignment
click next...
 
Computation: (Value you want to return) for example: No
click next...
 
Condition Type: Value of Item in Expression 1 is NULL
Expression 1: (name of checkbox you want to modify. Example: P1_DATA_FG
click create...

This article was created by Craig Glendenning

I found this article at his Oracle Ramblings web site

Thanks for the tip Craig

Share

Oracle Apex Interactive reports URL filters

Quick post to remind you all of the interactive report url parameter filters available in Oracle Apex.

  • EQ = Equals
  • LT = Less than
  • LTE = Less then or equal to
  • GT = Greater Than
  • GTE = Greater than or equal to
  • LIKE = SQL Like operator
  • N = Null
  • NN = Not Null

Usage: http://myserver/pls/mydad/f?p=1:12:3318977423147915::NO:CIR:IREQ_INSIGHT:complaint

This code filters the IR on page 12 to only show insights with a value of complaint

If you want to filter a report with PLSQL then use the following APEX_UTIL procedure.

apex_util.ir_filter(
p_page_id in number, -- Page that contains an Interactive Report
p_report_column in varchar2, -- Name of the report SQL column to be filtered
p_operator_abbr in varchar2 default null, -- Filter type
p_filter_value in varchar2); -- Value of filter, not used for N and NN

Share

Sammy.js

Sammy.js


If you haven’t checked sammy.js out yet, please do, you wont be disappointing.
Its a little JavaScript framework that lets you build sites as single page applications, and provides routing and event handling for syncing your page with json data feeds.

I use it with Knockout.js for dynamically updating my UI with the backend data model. i.e. MVVM.

Share

Vertical text in javascript

Vertical Grid using Raphael

Vertical text in a table

I’ve just recently been given a Word document with a mock up of a report that I needed to reproduce in HTML.

Well, i looked at various options like using transforms, rotate, DX filters in IE, but none of them worked properly, or without major headaches.

Whilst searching around, I found a JS library called RaphaelJS which lets you draw text and shapes on screen as SVG and after a bit of tinkering produced a really nice implementation of the grid with vertical headers et al.

Initial HTML looks like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<table class="summarygrid" cellspacing="0" cellpadding="0px">
<thead>
<tr style="text-align:center;">
  <th>Feature</th>
  <th><div id="rotate1"><span>Cycle</span></div></th>
  <th><div id="rotate2"><span>Assessment Type</span></div></th>
  <th><div id="rotate3"><span>Condition</span></div></th>
</tr>
</thead>
<tbody>
  <tr><td>Feature 1</td><td>U</td><td>N</td><td>N</td></tr>
  <tr><td>Feature 2</td><td>Y</td><td>Y</td><td>U</td></tr>
  <tr><td>Feature 3</td><td>U</td><td>N</td><td>Y</td></tr>
</tbody>
</table>

And the javascript looks like this:

?View Code JAVASCRIPT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
$(document).ready(function(){
    var font = {font: '13px Helvetica, Arial'};
//    var fill = {fill: "hsb(199deg, 1, 1)"}
    var fill = {fill: "rgb(255,255,255)"}
    $('tr th div').each(function (index, div){
        R = Raphael($(div).attr('id'), 20, 180);
 
        R.text(9, 175, $(div).find('span').text())
            .attr(font)
            .attr(fill)
            .rotate(-90, true).attr({'text-anchor': 'start'});
        $(div).find('span').hide();
    });
});

Great little find

Share

Switch to our mobile site