Monday, 12 March 2018

Emulating touch devices for Oracle APEX

In my Exploring AJAX presentation I had a frame that mentioned the ability for the Chrome browser to emulate the behaviour of a mobile device.

Open up the browser tools, I use F12. I spend a fair it of time in the Elements + Console tabs of these tools.
Then find the relevant icon, that will no doubt change location in future. Ctrl+Shift+M does the trick when the browser tools are open.

This mode allows you to change the type (size) of the device you're emulating; the orientation; and the network speed. Even the cursor changes to a shaded circle, to more closely simulate a (thicker) finger press.

Second icon from the left - follow the blue arrow

If you open your APEX login page when the emulator is already turned on, the user_agent will be spoofed accordingly. You could use Christian Rokitta's adaptation of categorizr to determine if you are on a 4" device, and populate an application item. That way, if certain regions don't fold well into small devices, you could conditionally replace them with more appropriate regions.

For example, I had a badge list of 5 values that didn't squeeze well into 4 inches, so I replaced it with a simpler value attribute pair region when my IS_MOBILE application item was set.
Note: don't forget to use declarative conditions where you can.

The 5.0 Universal Theme actually does a good job rendering regions responsively. Issues usually arise when there's simply too many columns to fit. I'm very much looking forward to the 5.1 OracleJET charts cathing up to to the touch behaviour. AnyChart prior to HTML5 doesn't adjust well.

Also available is the ability to throttle the connection. No doubt you have a pretty fine connection where you're developing, but the users on devices out in the field might not have the best connection. This setting allows you check your application's behaviour as if it were on a slower connection, highlighting any asynchronous issues, or lag that affects the user experience.

Nothing is ever the same as the real thing, so while it's a practical unit testing tool, you should still thrash out mission critical devices on the real McKoy.

I hear whispers that APEX 5.2 is going to potentially shake up the User Interfaces a little, with the removal of the jQuery Mobile User Interface. Perhaps there will be accommodations here to help our applications transition between screens, without needing dedicated pages?
I haven't seen any of the sessions delivered by the Oracle team talking up 5.2, so I'll have to wait and see.

Update -  I wrote this last paragraph a while back, though I don't think the early adopter covers any changes in this regard.

Monday, 29 January 2018

APEX Programmatic Spectrum

I listened to a session by Joel Kallman, and found myself thinking hard about this slide on the programmatic spectrum, diving into the concept of “low code” vs “high control”.

(From around 14 minute mark

Such a good representation of the spectrum of APEX components

I guess you might say tasks in the yellow end take longer to configure, and are difficult to maintain, particularly for someone new coming in, for instance:
At the green end, defining Authentication is very click-click-click, done. No code required.
Around the middle, using Authorisation schemes can also be declarative, but with a little tweaking, can give a decent level of control.
And to the right, for Custom authentication, while a typical package may look similar to the next, it’s driven with raw PL/SQL, but gives the developer high level of control.

Some other interesting claims in the seminar include APEX meeting the NIST needs of cloud computing from 2002. Remember, we didn’t even have Google Suggest until 2005.

And an application exported from 2008 still runs in 2017, many APEX versions later, looking and behaving the same.
As a developer familiar with the aims of the APEX upgrade process, this does not surprise me at all. What I didn’t consider was how much of a feat this is in the world of development.

Pretty impressive when considering version difficulties I've heard with products like JDev.
I realise that's comparing apples to toaster ovens, but it's a high bar that's set. Onya APEX team.

And the IDE is delivered via a browser :)

Friday, 12 January 2018

Data driven APEX icons

We have an application written with a heavily customised Theme 25 built for 10" tablets, and we feel the Universal Theme justifies the move, in part because of the surrounding ecosystem. Check out this forum discussion on the topic.

We've been looking through the packaged applications for applied ideas, and using the Universal Theme sample application as a component reference.

I came across a requirement where we had a list of items that indicated completion level out of 100.
Then I thought about a group of pie icons I saw in Dick Dral's Font APEX icon reference, and knew what I wanted to do.

All I needed to do was round a bunch of numbers to the nearest 5.

To solve such as problem, I usually start by giving myself a bunch of numbers to play with
select rownum rn from dual connect by level <= 100

Then defining that as a WITH statement, so I can refer to the derived column as often as I like.
with nbrs as (
  select rownum rn from dual connect by level <= 100
select rn
 , floor(rn/100 * 20) / 20*100 rnd
 ,'fa fa-pie-chart-'||floor(rn/100 * 20) / 20*100 icon
from nbrs
where mod(rn,5)=0
And I used a divide/multiply by 20 math trick to do the rounding.

We can then feed the result as the relevant icon into whatever region template we need.

I like pie.

Simple, yet effective.

Tuesday, 9 January 2018

Modify OracleJET Property at Runtime in APEX

OracleJET has attributes galore, but some are are (not yet) available to change at design time, so JavaScript code can be added to the chart attributes to set relevant attributes.
function(options) {
options.styleDefaults.threeDEffect = "on";

return options;
See my previous post about modifying these attributes on render.
We can also do this at runtime, perhaps as response to a button click, such as the 2D/3D button in the cookbook.

First, set a static ID on the chart, possibly one of the most common "advanced" properties I use.

Static ID property available on many components

Use this ID in the browser console at runtime to see what JSON was generated for the chart definition.
Any missing properties will use the default specified in the documentation.

Browser Console results

The documentation on these attributes is thorough, but I'd love some examples to help keep me moving.

We can modify the orientation property by supplying a name/value pair as a JSON string.
$("#p95_skew_jet").ojChart({'orientation': 'horizontal'});
Note the selector has is the static ID with a suffix: $("the_static_id"+"_jet")

It took a little while to find the correct punctuation for the nested properties, so this is really one of those blog posts I created so I don't forget. You've seen Alex's blog by-line, right?
$("#p95_skew_jet").ojChart({'styleDefaults':{'threeDEffect': 'off'}});
Not all runtime tweaks behaved as expected, however. The following property behaves as expected when setting on render, but at runtime it squishes the width of the entire chart.
$("#jet1").ojChart({'styleDefaults':{'barGapRatio': 0.2}});

Refreshing the chart region afterwards did not help in this case.
While looking for answers, I came across this post from Riaz describing similar customisations.

OracleJET JavaScript Customisation in APEX

I've finally got some regular hands on a 5.1 instance, and the shiniest tool in the box for me is OracleJET.

Some months ago I spent a few days learning about OracleJET and the knockout framework with Chris Muir. I doubt I'd ever get to that nitty gritty, but it sure is handy to know some of the finer details now that I'm using them in APEX.

I wanted to have a play with the funnel chart. I took an existing query, and quickly busted out a working chart.

Upon playing with the sample chart in the OracleJET Cookbook, I decided I wanted the 3D option.
In the case of this chart style, I think the subtle effect made a big difference.

The Cookbook is a great guide to what you can play with
I couldn't find the relevant attribute defined declaratively in APEX, but that's fine - I know all of them aren't mapped, and we have a special JavaScript section in the chart attributes to help us customise the content. This expands upon attribute selections, not replacing them like the custom XML did for AnyCharts.

The attribute help in the Page Designer is a good start, and always worth checking when playing with a new field.

Page Designer Attribute Help

I noticed the Sample Charts application did the same thing, but I figured I could take this sample from the help, combined with information from the impressive JET documentation, and try work out the JavaScript myself.

Not all properties are mapped to APEX attributes

This is what I came up with.
function(options) {
  options.styleDefaults.threeDEffect = "on";
  return options;

Only to find the customisation from the sample application looked same same, but different.
function( options ){
    options.styleDefaults = {
        threeDEffect: "on" 
    return options;
Turns out both are effective, once again demonstrating there's always a few different ways of doing the same thing in JavaScript.
I suspect the first option honours the object notation mentioned in the documentation, while the second assigns the value as a JSON name/value pair. I've seen similar behaviour in jQuery:


You can find other examples in your workspace with this query on the dictionary view.
select application_id, page_id, page_name, region_name, chart_type
from apex_application_page_charts 
where javascript_code is not null;
You may also enjoy changing series colours with similar treatment by Colin Archer of Explorer UK.
The German APEX community also has a thorough rundown of JET charting in 5.1

In my next post I'll explore how to play with these attributes at runtime, not just on render.

Thursday, 21 December 2017

ORDS Cross Origin Complaint

A few years ago while upgrading to APEX 5.0, we had a few issues when upgrading ORDS.

My loose understanding is that from about ORDS 3.0.3, it started enforcing some security policy regarding cross-origin requests. The big browsers handle this differently, for instance Chrome returns 403 Forbidden and won't let you log in.

Cunning Chrome Cross Origin Complaint
Amusingly, Edge let me in, even after advising me otherwise. No doubt there is minutiae I do not yet understand.

HTTP403: FORBIDDEN - The server understood the request, but is refusing to fulfill it. (XHR)POST -

It seems depending on what you're trying to accomplish, and your middle tier framework, your problem/solution may be different.

I found these threads brimming with leads
And while writing this post I found one that essentially described our solution

Now most discussions involving proxies are word soup to me, especially when they're described as reverse proxies.
But I think this experienced helped me wrap my head around it. A little.

For us, outside requests got remapped to an internal domain. This effectively meant the domain looked different, and somewhere along the line some bit of software drew the line and said this was a security threat.

However, there is a setting that allows the Proxy Host name to be preserved.
It's aptly named ProxyPreserveHost, or preserveHostHeader, depending on your middle tier kit.
And it's described well here
No complaints since.

So for anyone migrating to ORDS 3.0.3+, perhaps don't lay blame on ORDS for your problems.
There may be some proxy voodoo your network engineer can fix in a jiffy, with that golden screwdriver. Thanks, Harry.

ORDS web services returning BLOBs

When it comes to deliving blobs from the database, I'm sure many of us have used, or came across a procedure that look like the one described here.

This sample includes some commentary on how the surrounding infrastructure should look, but it's a little out of date.
create or replace procedure get_image(p_id  IN IS
  l_mime        images.image_type%TYPE;
  l_length      NUMBER;
  l_lob         BLOB;
-- This procedure needs
  -- Grant to apex_public_user
  -- Public synonym, so not reliant on #OWNER#
  -- Function wwv_flow_epg_include_mod_local needs to include this procedure name
  -- Test rendering using call such as
  -- If not found, check case, synonym
  -- If no permission, check execute priv, presence in wwv_flow_epg_include_mod_local
    SELECT image_type, the_blob, DBMS_LOB.GETLENGTH(the_blob)
    INTO   l_mime, l_lob, l_length
    FROM   images
    WHERE  id = p_id;

    OWA_UTIL.MIME_HEADER(COALESCE(l_mime, 'application/octet'), FALSE);
    HTP.P ('Content-length: '||l_length);
END get_image;
There are alternative methods available, so you can make ORDS more inherently secure, as descibed by Kris Rice. In fact, this was the reason I needed to do something. Regression testing with a newer version of ORDS uncovered a difference with requestValidationFunction parameter.

For instance, you could use an application process, thanks to Joel Kallman for yet another solution ;p.

We've previously delivered other images using web services, thanking Kris again for the inspiration. The web service is a basic query on the images table, and it also means referencing the image within the HTML is easy
<img src="/ords/rest/image/fetch/3141592" />

Often this would translate to an expression like this within your report
<img src="/ords/rest/image/fetch/#IMAGE_ID#" />

Or perhaps
<img src="#YOUR_IMAGE_PATH##IMAGE_ID#" />

I recently upgraded SQL Developer to a relatively shiny 17.3, so I could use the fancy REST navigator in the connections window.

I've also been meaning to note down the process, and typical values, since I still only understand web services at a relatively basic level.
(secret: the real reason we write blogs is so we can google ourselves later.)

I new based on existing web services that the following values were required, so I just had to match these to the steps in the create wizard.

Module: image
URI Prefix: /image/
URI Pattern: fetch/{id}

The module being a group of templates, which are perhaps difference sources of images -> image table, user table, product table. Then a handler is created to get or post data.

The first step is a good start. I've always had trouble working out what value goes where in the URL equation, so it's great to see the translation in the first step.

Step 1 - Define Module

Step 2 finalises the URL by defining how the identifier for the database record will be provided.

Step 2 - Define Template

And we're at the summary already, noting we can create web services without initially publishing them for use (from step 1).

Step 3 - Summary

We can flip over to the SQL tab to show all that SQL Developer is doing behind the scenes - calling supplied APIs.

Step 3b - Review SQL

Now the web service should be visible in the SQL Developer Connections navigator.

SQL Developer UI

Righ click on the template to create a handler, in this case to GET the image.

Step 4 - Add Handler

All the source types return some form of text, except Media Resource, which delivers a binary representation of our blob.

Handler Type

And the handler is just a SQL statement fetching the blob from the table, filtering with a bind variable mapped to the {id} named in the URI pattern.

Handler SQL

The query in the image:
select image_type as mimetype
from images
where id = :id
All pretty simple so far, but we had another similar procedure that delivered binary content using bfilename(). ie - files in the file system, resolved via an Oracle Directory.
I figured I could still write a query that delivered this using a table function:
select mime_type, document
from table(get_job_doc(:code));
Where get_job_doc returns an object type. The function doesn't need to be pipelined, because it only returns the one row. I describe the relevant pseudo-code in this forum post.
It works, but I had trouble adding content-disposition in the header, so .msg files don't download well. I bet there is some tidy ORDS feature/solution I'm yet to discover.

In the end, one procedure was replaced with a web service, another with an application process. Both more secure options that don't require custom PL/SQL to be available via the URL. A whitelist is safer when there's no need for one.

In the era of cloud, web services are king.
Now we just need to wait for ORDS & APEX to talk the same data set when it comes to manipulating these services.