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 - https://redacted.com.au/vbs/wwv_flow.ajax

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
https://community.oracle.com/thread/4042054
https://community.oracle.com/thread/3971878
And while writing this post I found one that essentially described our solution
https://community.oracle.com/message/1409513

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
https://www.ctrl.blog/entry/how-to-httpd-reverseproxy-hostheader
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  images.id%TYPE) 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
  -- http://domain.com.au/pls/apex/GET_IMAGE?p_id=2
  -- If not found, check case, synonym
  -- If no permission, check execute priv, presence in wwv_flow_epg_include_mod_local
BEGIN
  IF p_id IS NOT NULL THEN
    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);
    OWA_UTIL.HTTP_HEADER_CLOSE;
    WPG_DOCLOAD.DOWNLOAD_FILE(l_lob);
  END IF;
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
,the_blob
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.

On Controlling Use of Minified Code in APEX

Today I encounted an issue during regression testing for 5.1. An older application that doesn't use the Universal Theme, and hasn't really been touched since 4.x, complained about the SkillBuilders modal page plugin.

The solution seemed pretty clear upon reading through this forum post - just update the relevant jquery.colorbox.js file to the latest version, no problem.

I just updated the minified version on my dev instance, jquery.colorbox-min.js, but saw no effect. After checking the code in my relevant plugin package (since all our plugin PL/SQL goes into packages), I saw it wasn't actually using the minified version, and it got me thinking.

apex_javascript.add_library(
      -- p_name      => 'jquery.colorbox-min',
      p_name      => 'jquery.colorbox',
      p_directory => p_plugin.file_prefix,
      p_version   => NULL
   );

Quite some time ago I remember learning about the #MIN# substitution string. The idea is you include files using my.file#MIN#.js, and it only used the minified code when you weren't in debug mode. (That double negative is just for Eddie.)

Item help when adding File URLs

My initial thought was to add it myself, based on the value of debug.

apex_javascript.add_library(
      p_name      => 'jquery.colorbox'||case when v('DEBUG') = 'NO' then '-min' end,      
      p_directory => p_plugin.file_prefix,
      p_version   => NULL
   );

Only to quickly realise the apex_javascript.add_library API has a dedicated parameter for such a task, though it expects dot notation in the filename, not a dash: my.file.min.js

apex_javascript.add_library(
      p_name      => 'jquery.colorbox',
      p_check_to_add_minified => true,
      p_directory => p_plugin.file_prefix,
      p_version   => NULL
   );

What surprised me though was looking through the other plugins. I couldn't find any in my current data source that used the p_check_to_add_minified parameter, they all just expected its use.

Do plugin developers not bother with p_check_to_add_minified?

Perhaps the newer plugins at apex.world use the parameter, if so I'll stand corrected.

Or perhaps there's little value for its use in our data driven pages, once the plugin is up and running. Any APEX plugin authors care to chime in?

Oracle APEX Self Improvement

There was a slide I considered in my Exploring AJAX presentation that was a little tangential, but I left it in as a little extra positivity for APEX.

I think this concept was explored in some facet during my 2015 Kscope visit, possibly even in a Marc Sewtz talk.

Forms has a ceiling. APEX has room to grow.

Oracle Forms, as wonderful as it is/was, was never used by the people who built it.

Ever built a back-end application you use youself? You get rid of all the little annoying things, don't you?

Now think about the packaged applications. They grow, extend, take advantage of new features. The APEX team need build these applications using the product they're developing. Heck, even APEX is built using APEX.

At some point long ago, there was a chicken and egg conundrum, solved with mod_plsql. ie, hand-write PL/SQL to generate a cute UI, that you can then use to build your meta-data with, and future pages rendered from the data.

Again, I think Marc described this piece of history far more elegantly with Juergen on The Oracle APEX Talk Show (apex.press), which I'm far behind on. Science podcasts are too irresistable.

Just recently, Scott (of the Spendolini variety) noted the top 4 collection of technologies in the (skewed) 2018 StackOverFlow survey are exactly what makes Oracle APEX tick.

Quick take on the @stackoverflow developer survey: top 4 technologies used are essentially what makes up #orclapex pic.twitter.com/bWhmiV5eAT

— Scott Spendolini (@sspendol) March 13, 2018

So now, the APEX team build APEX applications for APEX developers to build APEX applications, using core internet technologies.
There is a feedback loop here that can only be good for the future of the product.

And all this happens within the browser.
Driven from data in the database.
That's winning, across a short, yet effective hardware stack.

I wonder if other contemporary IDEs are similar?

Friday Fun SQL Lesson - union all

Our office kitchen is unavailable this Friday, so the call was put out for pub lunch.

After a couple of replies I decided to enter my reply, in full nerd mode.

select * from people_coming_to_lunch;

People
--------
Kate
Scott
Karolina

3 rows selected.

And of course one of the other SQL geeks (name redacted) replied to extend the data set.

select * from people_coming_to_lunch
union 
select 'Shanequa'
from dual;

And I couldn't help myself. I had to play the performance card and suggest that UNION ALL would be the appropriate usage, and should be the default you type out. Always. Until you decide otherwise.

That's because sorts are expensive. And a UNION will need sorting to check for duplicates.

That sort of all the rows isn't necessary

And it will sort the data set even if there is a unique key on the data.

create table people_coming_to_lunch (people varchar2(30));
insert into people_coming_to_lunch values ('Scott');
insert into people_coming_to_lunch values ('Kate');
insert into people_coming_to_lunch values ('Karolina');

create unique index lunch_people on people_coming_to_lunch(people);

select * from people_coming_to_lunch
union all
select 'Shanequa' from dual

By using UNION ALL instead of UNION, you're telling the database not to even bother sorting the set to eliminate any potential duplicates, since your advanced human brain knows there will be no duplicates.

With only a few rows, the timing of sheer throughput is barely noticable.

iterations:1000
     0.30 secs (.0003 secs per iteration)  -- UNION
     0.25 secs (.00025 secs per iteration) -- UNION ALL
 
iterations:10000
     1.72 secs (.000172 secs per iteration)
     1.09 secs (.000109 secs per iteration)
 
iterations:50000
    10.94 secs (.0002188 secs per iteration)
     8.48 secs (.0001696 secs per iteration)

So I turned it up a notch and added about 5000 rows to the table.

insert into people_coming_to_lunch
select table_name from all_tables;
 
5000 rows inserted

Here's the explain plan without the sort.

That's one less chunk of 5000 rows to process

Now the differences in performance stand out.

iterations:1000
     6.79 secs (.00679 secs per iteration) -- UNION
     2.85 secs (.00285 secs per iteration) -- UNION ALL
 
iterations:5000
    42.91 secs (.008582 secs per iteration)
    19.89 secs (.003978 secs per iteration)
 
iterations:5000
    31.70 secs (.00634 secs per iteration)
    22.83 secs (.004566 secs per iteration)
 
iterations:5000
    30.75 secs (.00615 secs per iteration)
    16.76 secs (.003352 secs per iteration)

Upto twice as long for the same statement?
No thanks, not when I could just type 4 extra characters to get an easy performance win.

Turns out this topic formed my first technical post. Back in 2009, after almost 10 years of using SQL, that was the first thing I blogged about. How about that.

Exporting an APEX page plus Shared Components

Imagine, if you will, a really large application. Perhaps it's a reporting application, and you've just created a new page (report) and an associated breadcrumb. 

Now for migration. You have a choice between exporting the entire application, or you could just export the page. But what about the associated breadcrumb? What about the LOV?

This is where the Component Export can help.

You'll find it in the Tasks menu when you start to export your application.

Tip: Keep an eye out for these Task menus, they can 'hide' interesting features

From there you can find, then check the most recently modified components to your application, then press "Add to Export" to add them to what I would imagine would be an apex_collection.

Creating this type of page would be a good learning exercise

The final confirmation, with the additional option to export as of x minutes ago.

Upon export you'll receive a file that looks just like a normal export, just with your selected components.

f102_components.sql

There are advantages to exporting only selected components. For instance, during a full application install, existing users will remove existing user sessions for the application unless you use apex_application_install.set_keep_sessions on the command line.

Though are a few reasons to take care.

For example, when I exported/imported the List for my left sidebar menu, the menu disappeared from my runtime view.

I had to re-assign the list as the navigation menu.

Shared Components - User Interface - Nagivation Menu - Navigation Menu List.

Exporting without the list was ok, so for all good for data driven (dynamic) menus. In that case the page should only appear in the menu when the relevant security tables say so.

And the export may not be as granular as you need. Or perhaps you'll forget components. Or perhaps your changes overlap with others.

Component export is fit for some purposes, and has been around forever. Give it a go.

And wouldn't you know, turns out it was a year ago today that I also felt compelled to blog about this very topic.

Exploring AJAX presentation

Wow, what a busy year. My blog archive is pretty thin this year, isn't it?

I'm moving house next week, but I'm itching to get back into blogging about my tech journey.
I need to get back into learning mode, I have some Kscope ideas to foster!

Perth had a humble but successful conference last week. It was impressive to see Oracle APEX dominating the developer track, with thanks to ACE Director Dimitri stepping up and delivering four engaging sessions in one day.

A few months ago I drafted a session exploring the concept of AJAX, and how it fits within the world of web application development. I was aiming for a more widespread crowd, hoping to fill some gaps by doing something as non-technical as possible.

Next minute, among what seems like many other things, I'm preparing to move house, so I came in a little underprepared.
Here's my slide-deck, but it's missing the passionate space-nerd adding enthusiasm to the content ;p
https://prezi.com/dwibb_dismt5/exploring-ajax-from-afar/



I'll probably post about certain frames that are worth a mention.

I'll have my regular hands on a 5.1 environment soon, I've got some catching up to do before 5.2 arrives. Bring on OracleJET.

Cassini. So long and thanks for all the pics

Recently I stumbled upon an amazing documentary called The Farthest about the Voyager spacecraft and their grand tour of the solar system. You know the hub-hub caused as New Horizons flung past Pluto and we got to see that proto-planet close up for the first time? Voyager did that with Jupiter, Saturn, Uranus, and Neptune. With two spacecraft shot out during a convenient syzygy.

It was an amazing tale, even if you geek out to astronomy quite the way I do. I grew up being rather aware of space and the various spacecraft being sent far beyond our world. One of few, beyond the short news cycles:



I suspect there will be another spike for Cassini over the next week, since it crashes into Saturn on Friday night, twelve years after it's companion Huygens decended onto Saturn's moon, Titan.

I perceived Voyager through the lens of a growing boy. Cassini I have appreciated while I've grown as, and I'll use this term today, a software engineer. One involved with project management.

Some major events on the Cassini timeline paired with my life events:
- mission conceived when I was a toddler
- mission approved when I was in primary school
- rocket launched after I learned to drive
- (as the IT industry scrambled over Y2k, the Mars Climate Orbiter was lost due to a feet-metres calculation error. Cassini approaches the most massive planet in the solar system)
- probes flew by Jupiter when I left started my first programming job
- probes arrived at Saturn when HTMLDB was born
- huygens descended Titan soon after arrival, floated through the thick nitrogen haze and landed on a lake strewn surface
- Cassini conducted most of it's science operations during my APEX career (to date)
- and now it's about to be scuttled into the clouds of Saturn

I've been reading about Cassini science for a long time, enjoying stunning photos as Cassini loops around the Saturnian system, and learning from from this project during my entire programming career.

This is quite possibly one of my favourite images from space of all time. Of all time. Eat it, Kanye.

Titan's Haze PIA07774

I find crescents poetic; interesting information can be garnered from such perspectives; the atmospheric haze; the exotic colours; the distance involved; the engineering required; the timing.

I stand in completer awe of the planning, project management, engineering, communications, politics, and pure passion that has been involved in making this one of the most successful multi-decade projects in humanity's history.

That's a mighty statement, but I believe it shares space with some undeniably society changing projects across the ages. Multiple nations were involved - NASA built Cassini while ESA built the Huygens probes that landed successfully on Titan soon after arrival.

Titan's surface PIA07232

All this while communicating information back to Earth with a 70 minute communication lag.
With 1990's technology.
And this is the sort of project that can inspire the future generation of STEM students. And it's not alone, there are more amazing projects on that way destined to keep doing so.

What can replace this satiation? Hubble is aging, and next year NASA will launch a telescope that makes Hubble look small, and will be the one to stun us with new information for years to come, no doubt related to exoplanets as it studies the infrared spectrum it will see while hiding in the Earth's shadow, 1.5 million kms away.  No corrective lens surgery from charismatic spacemen for this one..

Hubble vs JWST

Not only that, the James Webb Space Telescope reminds me a little of a star destroyer from Star Wars.

JWST. Bringing sexy back for telescopes

Space exploration is a nett gain for humanity. If you're not convinced by that statement, listen to this interview with David Grinspoon, and pay attention when he describes the lessons learned about CFCs while studying Venus, or the concept of nuclear winter while observing dust storms on Mars.

But back to Cassini.

Do yourself a favour. Set some time aside and let Phil Plait's passionate & informative writing guide you through the journey, and use a big screen:
http://www.syfy.com/syfywire/commemorating-cassini-at-saturn-a-gallery-of-wonders

NASA/JPL also have a great hall of fame.

To get a sense of the now, I've been watching related Twitter feeds over this past week, and I feel for all the people involved in the Cassini/Huygens project.

Today is @CassiniSaturn flight team's final 8 AM status & scope meeting where reports are about a live spacecraft. I'm expecting tears.

— Jo Pitesky (@friedsky) September 12, 2017

Expecting tears? Heck, I shed a tear for you.
Need humour? Check out this interaction between the dude with the mohawk, Bobak Ferdowsi and Sarcastic Rover. Yep, that's a thing, and it's a great feed. Need to believe scientists can be more than just boffins, here, same guy.
And I've just discovered Australia's geographical location will be handy again to receive the final communications #CassiniAus

Stand proud, Cassini team, and thanks for all the pics.

#CassiniInspires

Educational Eclipse

Last night around midnight I laid in bed hoping that thousands of people on the other side of the world took the opportunity to experience the once in a life time experience that would be a total solar eclipse.

If you agree that a wonderful piece of prose can help recreate the experience, I recommend this literary non-fiction from 1982 by Annie Dillard.

Today I'd like to share three things I learned about the solar eclipse, considering I'm somewhat of an astronomy geek.

1) Shadow Bands

One of the amazing things about total solar eclipses is because they are relatively rare, there are still phenomena that science is trying to explain.

This video of an eclipse (which I recommend with a little sound) actually shows none of the classic imagery of a solar eclipse, but it captures so much of the awe.



And should I be lucky enough to witness one in future, I would have to ensure I experienced all that's available, even if it meant sticking up some big sheets of paper!

I think I might have a look like that of Katie Mack in the second photo, and probably the third.

2) Gravitational Lensing

In addition to the fact these eclipses are so awe inspiring, they do offer a bit of science, beyond helping calculate the dimensions of the solar system.

This example includes one of my favourite phenomena, more frequently associated with deep space imagery - gravitational lensing.

It turns out when the total eclipse happens, astronomers can take the opportunity to locate stars prominent enough to shine through. Compare distance between stars as observed at night, and astronomers can see how the sun's mass has nudged light, thereby confirming general relativity.
Stunning.

A concise article here with two great images, or google "Eddington 1919 eclipse"

3) Flight of the Concorde

All speeds involved with the eclipse are pretty wild, but back in the 70s, long before SpaceX started landing rockets on sea barges, there was a curious supersonic jet about to go commercial that took the public relations opportunity to fly faster than the speed of sound across Africa to accompany a 1973 eclipse.
Curious Droid - NASA chasing a shadow at 50k feet | Concorde 1973

I've been quite addicted to that channel recently, some great videos to watch during a lunchtime subway.

The first two I learned directly from Skeptoid Episode #584 by Brian Dunning. It's a great 12 minute podcast (now with ads) that I might describe as mixing science with anthropology. Very easy to pick a few interesting topics, then get hooked.

Did you see #Eclipse2017? Did you travel to get there?

Revising jQuery with Oracle APEX

A few years ago I managed to bust out a book that explored my experiences using jQuery within Oracle APEX to solve various problems, many originally explored when building some "backend" apps for use on tablets.

For those who've read my book, thank you and I hope it was useful to you.
(I also hope you read the revised chapter 9 on calling PL/SQL from JavaScript.)
Please let me know what you thought of it with a review on Amazon.

The reason I bring this up is because I was asked on Twitter whether I would do a revision of my book for APEX 5.1.

@swesley_perth do you plan to write 2nd edition of your book "Pro jQuery in Oralce Application Express" to incorporate apex5.1 feature?

— Kevin Zhang (@kzhangkzhang) August 16, 2017

I'm not sure if you've noticed, but my extra-curricular output has been down this year, despite my best intentions. That's not my answer, but it does weigh into what I think about the answer to that question.

Half of that book was written prior to APEX 5.0 being released. While a lot of the content is agnostic to the version of APEX you have, I must admit my techniques have evolved since the advent of the Page Designer.

That's not to say learning how to apply jQuery within APEX isn't useful. Having these fundamentals are still very much applicable, and useful to know when constructing dynamic actions; and even when defining CSS. But I think because it's much quicker to navigate through your dynamic actions in the Page Designer, I think there is less need to bundle logic within a JavaScript block.

I'm not sure revising this particular book for a later version of APEX would add value.

NASA Cassini

My video course had no particular theme and just explored various APEX techniques, hence the name. My book wasn't much different, and if I was going to do something again in future, it would possibly be along the same lines. Or something
even more applied. I do have ideas.

But I've been enjoying an awful lot of science in the time being. So much going on in space news!
It will be sad to see Cassini finish... Don't be surpised, I already have a post in mind for that amazing project. Correction, I already mentioned it during my 2016 blog review. Heck, I might do another anyway.

In Memory Session State - Simple Example

Not too long ago I encountered an interesting behaviour with session state, turns out not long after Dick Dral posted a very similar article that I had not yet seen.

Recently I encountered an even easier scenario.

I had a bunch of columns where I wanted to use the same prefix across all headers, but I wanted to define the value once, referring to it many times. I'm betting on the fact someone may change their mind.

Interactive Report with pivoted data

So I created a hidden item called P222_PREFIX and gave it a static source of "Ch".

Page item source

If I substitute this item as part of the column heading for each column, it will use the value found session state - not the database value, but the value based on the defined source, placed only in memory for the duration of page rendering.

Column heading

So while the output may appear as desired after the page renders, if the region is partially refreshed by hitting "Go" on an interactive report or just triggering region refresh, the prefix will disappear because there is no value for P222_PREFIX in session state once the page finishes rendering.

Desired output

To fix the problem we add a computation on the hidden item to set the value, instead of using a static source. This ensures the value is in session state for current and later usage.

An alternative solution to the task at hand might be to use Shortcuts.

APEX Login Background Image Cover

For a while I've wanted to play with cover photos on login pages, and when Keegan asked a similar question on Twitter, I was curious enough to ultimately have a play.

Someone mentioned relevant a reference to CSS-Tricks Perfect Full Page Background Image, but after quickly finding the video Keegan must have screenshotted in her tweet, I realised us APEX developers need to use the following instead of 'html' as our selector.
.t-PageBody--login

Update for APEX 19.2: Days after I had a discussion about limiting the references to Universal Theme (UT) classes, particularly nested ones. I find that my original extended reference of .t-PageBody--login .t-Body is no longer valid in 19.2 UT.
As Peter suggested, it would be great to have a set of release notes to supplement the living document that is apex.oracle.com/ut.
I possibly wouldn't expect this particular example to be itemised, but knowing where to look would be great.

Plugin extraordinaire Daniel suggests

using 16:9 1920x1080 for standard UT use css media queries for different screen sizes with a pool of 2 / 3 images

The example I applied uses a CSS media query to not use a background image for smaller screens, as it may look too busy.

<style>
@media (min-width:400px)  { /* anything but mobile */
  .t-PageBody--login .t-Body {
    background: url(#IMAGE_PREFIX#cover_images/&P101_IMAGE.) no-repeat center center fixed;
    -webkit-background-size: cover;
    -moz-background-size: cover;
    -o-background-size: cover;
    background-size: cover;
  }
}
</style>

I defined this CSS within a region so I could apply a 'dev only' build option. Alternatively, this could be a PL/SQL region, with the code abstracted/encapsulated within a PL/SQL package, possibly utilising the htp package.

Style within Region

This also means the image location and file name are easily parameterised. So your images could be located anywhere, and you could programmatically decide which image to display. And/or use media queries to determine which image should display on the relevant device.

As an experiment, I wondered if I could rotate through a number of images, so each time someone visited the login page, they would see one image from a pool of many. So I defined an item with the following calculation:
'beauty'||floor(dbms_random.value(1,8))||'.jpg'

We can confirm an evenly distributed calculation by running that computation many times and counting the results.

select count(*), val from (
  select floor(dbms_random.value(1,8)) val
  from dual connect by level < 10000
) group by val

COUNT(*) VAL
1413  1
1445  6
1412  2
1420  5
1411  4
1415  3
1483  7

This would randomly select from a small suite of photos in the folder. I've used a selection I've collected from APOD.

And voila, an inspirational login page.

APEX Login with background cover image

I think an improvement would be to show a consistent image while attempting to login. A brief experiment suggests only running the computation when P101_IMAGE is null, and only clearing login fields (not the entire page cache) during page processing.

This could be the final bling you need after pimping your login page.

Don't forget, you can style the backend login, too.

Once again, thank you #orclapex community for making this a breeze.

Sans Kscope

A few years ago I was lucky enough to attend Kscope15, and while I wasn't there this year, I have bizarre feeling of involvement.

The Buzz

I knew it was coming because the Twitter engine told me. I could have ignored Twitter for a week, or I could try garner some interesting information.

I could also live vicariously through all those Oracle fanatics sharing their Kscope experience on Twitter. I joke, but actually a lot of the gurus regularly share knowledge and interact in forums such as Twitter, Slack and OTN. If you're not involved in one of these feeds, you're probably missing out on a valueable source of contemporary tech information.

You may notice some of the most recent #Kscope17 tweets are from the after-party. It turns out this party is actually before the final 2 hour sessions on Thursday morning. Kudos to all those party animals that turned up to my jQuery deep dive in '15. 

Content

People share key slides. This gives us all a head's up for something photo worthy. Discussions start, interesting news and techniques get explored.

Key sessions were live streamed, and (I believe all) sessions will be available to ODTUG members as screen/voice recordings in September. For $99US a year, that's bargain training value. It's just setting aside an hour or so a week to knock off the relevant sessions.

Party

The Kscope party is amazing. I obviously wasn't there, but half a world away I experienced my own night out and I had a little epiphany, of sorts.

I was going to a music gig. No big name band, moreso a collaboration of local musicians performing a tribute to A Perfect Circle's Mer de Noms album. I know a few of you out there will have some clue as to what those words mean, or may like similar progressive rock. For me it was seeing a few mates perform an album I love live.

The gig was on at the same time as the Thursday morning live stream, but I wasn't missing this gig. It turned out to be an amazing gig. 

I've seen the singer (Dez) perform a number of times and he does a brilliant job emulating not only the original singer's voice, but also brings an amazing on stage presence. He smashed Tool's Aenima a few months ago at the same club, renowned for seasonal tribute gigs.
Another good friend was up there, pretending to know what to do with a six-string bass ;p, plus some other talented guys I've seen playing around before. And then out comes another dude up with an electric violin for a few songs!

Sea of Names - A Perfect Circle tribute

The sound was amazing, the small crowd was pumped, I had a lift to the gig so I actually enjoyed a few beers for a change. They did so well I had an early call of best gig ever, which is hard to judge & compare, but then I remembered seeing Roger Waters perform The Wall a few years ago and that will be hard to beat.

It ticked a lot of boxes, boys, well done. Encore.

Reflection

We're a long way from iconic music meccas like Seattle, but Perth kicks out some brilliant musicians, and I'm a grateful music lover.

For a few moments I thought about the Kscope attendees, having a fine time, letting off some steam after some solid days soaking up a lot of information.

I also thought about the dedication and skill required for people to learn a craft, then share it with others, be it music or code.

Congratulations ODTUG on creating an annual conference with such an impact.

Future

Many things afoot in this household at the moment, and if all goes smooth, we could find ourselves moved into a new home by the time abstracts close for Kscope18 in October.

That's the plan ;p

Science Friday: Collect rocks, plant flag

Every year that goes by firms my realisation that putting people on the moon was an absolutely stunning achievement.

Almost 50 years ago a massive team of engineers helped put 3 highly skilled men in orbit around that giant rock in our sky, that is stunningly distant, yes infinitesimally close, then land, then take off again.

While still doing the math by hand.

I think the best perspective of the distance in a scale overhead.

Ranging the moon

It's best behind a bunch of black, or a view from a telescope orbiting Mars., but consider it in light seconds.

Wiki

Then put some men in a hunk of metal utop a chemical missile rocket over a period of days.

Last year the twitter account @ReliveApollo11 recreated the days surrounding the landing, to help those of us too young to have experienced the timeline over the wireless as it happened.

The Moon is in both shadow and earthshine, filling 3/4 of the hatch window. Neil says, “it’s a view worth the price of the trip.” #Apollo11

— Apollo 11 (@ReliveApollo11) July 19, 2017

Anyone can view it, and I highly recommend you try transport yourself to another time.

Blog title thanks to the 10c New York Times from that day #Apollo11

Removing Outliers using stddev()

A colleague asked me about removing outliers from reports and I knew just the function to give a go.

Some time ago I had to prove that some data we had conformed to a normal distribution. Remember those from high school?

A normal distrubition (bell curve)

That request was about 2007 and I ended up using stddev() and lpad() to produce a vertical shaped bell curve in SQL*Plus. That was pretty cool.

As for removing outliers, we can use this tool to exclude a small percentage of whatever data you have, so that it doesn't skew averages.

If you want to look at the code in context, I've created a test case on livesql.oracle.com

But as an inline reference, first I created a simple table, and populated it with a small set of random data between known range.
(I would like to add this is exactly what Tim Hall recently described here in regard to learning and asking questions.)

Importantly, I also inserted an outlier that was an order of magnitude higher than the rest.
My aim was to ignore this outlier in my SQL that calculated average.

create table outlier_demo  (a number, val number);

insert into outlier_demo  
select rownum -- we don't care  
  , round(dbms_random.value(20,50),1) -- some random numbers between 20 and 50  
from dual   
-- let's create 49 rows  
connect by level < 50

insert into outlier_demo values (0,500)

select * from outlier_demo;

A       VAL
---     ----
1       27.9
2       42.5
3       49.7
4       22.3
5       42.3
6       20.2
7       33.9
8       46.8
...
48      45.7
49      21.8
0       500

The average is higher than expected because of that outlier. I've included a column that calculates the average without the outlier, albiet hardcoded. Notice how close it is to the median? When considering usage statistics from APEX, I've often paid more attention to the median (most rows) than the average (affected by outliers).

select count(*) c   
   -- what's our average?   
  ,round(avg(val)) the_avg   
   -- what would the average be without the known outlier?   
  ,round(avg(case when val < 500 then val end)) no_outlier   
  ,round(stddev(val)) std   
   -- what's the median  
  ,round(median(val)) the_median  
from outlier_demo 

C    THE_AVG NO_OUTLIER    STD   THE_MEDIAN
---  ------- ----------  -----   ----------
50        46         37     66           36

We can take the same function and get the result for every row, that's one way we can use it to filter outlier data.

select a, val  
   -- what is 2 standard deviations away  
   -- provide result for each row  
   ,round(2*stddev(val) over (order by null)) local_std  
from outlier_demo

Re-written as a from clause, we can now filter any rows that have a value higher than x standard deviations from the norm.

select count(*) c   
  ,round(avg(val)) the_avg   
  ,round(avg(case when val < 500 then val end)) no_outlier   
  ,round(stddev(val)) new_stddev  
  ,round(avg(local_std)) filter  
from    
  (select a, val   
   -- what is 2 standard deviations away   
   -- provide result for each row   
   ,round(2*stddev(val) over (order by null)) local_std   
   from outlier_demo   
  )   
-- only average those values within two standard deviations   
where val < local_std;

C   THE_AVG NO_OUTLIER  NEW_STDDEV  FILTER
--  ------- ----------  ----------  ------
49       37        37           9      132

This could be re-written in a form that may be more recognisable to some.

select count(*) c
  ,avg(val) the_avg
  ,avg(case when val < 500 then val end) no_outlier
  ,stddev(val) std
from outlier_demo
where val < (select 2*stddev(val) from outlier_demo);

There are probably a number of variations on how to apply this solution, and possibly a few alternative methods.
It will probably depend on the SQL in your particular report.

This is not to say you should be ignoring outliers completely, but stddev() can help identify them where necessary. And it scales.

Thank Kate for this post.

ps - If you want to dive deeper, Jonathan Gennick (my patient book editor) has two in depth posts on the topic, with some diagrams to help understand the mathematical concepts.

Related post:
Filtering outliers from APEX workspace activity logs

Annual Oracle release cycle coming?

Those of you on Twitter at the end of May, and certainly those at DOAG might have seen this announcement.

Oracle changes the whole release cycle of the @OracleDatabase
There will be a yearly major release and a quarterly RU und RUR.#DOAGDB17 pic.twitter.com/HZhESl7b9Q

— Tobias Deml (@TobiasDemlDBA) May 30, 2017

I'm no DBA, but that first sentence in the picture has the potential to be rather game changing.

Annual Feature Release of Oracle Database Software

More information is to come (from official channels), as so eloquently put in a comment on this only other post I've encountered on the topic, but I've been pondering this glimpse of change with some colleagues, here in the suburbs of our isolated Australian city.

We're navigating our way through a few upgrades now, and I've seen the same patterns before. Without a clear idea on event what month a major release may arrive, it can be difficult to engineer time into the schedule for an upgrade. It's like launching rockets, miss a window and you may have to wait months for another opportunity.

Other software vendors have set release schedules, and it an annual release cycle will no doubt influence the operations of Oracle consumers (and conferences) around the world, I think for the better.

And they probably won't need to worry about how to market the superstitiously tainted #13.

Presentation: Dynamic Actions, Javascript, & CSS for APEX Beginners

My first webinar didn't do so well, perhaps my new home will have suitable internet capability, in this "innovate nation" of ours...

My second one on my Boggex game went ok, though the room the locals were in was like an sauna.

My third attempt had it's own set of issues.
1) Ubuntu as an OS doesn't seem to enable broadcasting via GoTo Meeting.
2) The wireless policy in the Oracle building is effective, but not great for hastily setting up a second laptop
3) The navigation keys on Lino's keyboard was just different enough to be disruptive
4) While I use on Chrome's save password feature, I'm pretty confident with most of my APEX passwords. It's the username I was stuffing up on the foreign PC.
5) The think tank at HP thought it would be a good idea to map Aeroplane Mode to F12, so when I proceeded to display the Chrome browser console...

So balance was restored after the first 10 or 15 minutes, which rattled my first attempt at presenting using just an APEX app - no powerpoint/prezi.

Though I think the concept was fine. It's just like training people, jumping between Page Designer and runtime.

I'd give it another go, and it's less presentation prep. I learned a bit from the first session, and explored some layout options while I was at it.

I still baulk a little at the difference between talking with a live crowd, and a community of ears at the other end of my microphone. I wonder if (finally) creating a few more videos will fix that? Once my ultrabook is back from computer hospital, perhaps. In the new house?

As for the topic, I explored dynamic actions, JavaScript and CSS for those trying to understand the fundamentals. It's a favourite topic of mine and I think there are a few basic patterns that get me a long way in creating interactive applications.

No doubt I'll refine these example further, and how to communicate them more concisely. Stay tuned.

I've made the app available here. Please feel free to provide any feedback, either here or through the app.
https://apex.oracle.com/pls/apex/f?p=27882
My favourite page is P50 specifically on Dynamic Actions, probably the most versatile utility within APEX.
If you scroll down you'll see the relevant code I used to sustain an interactive page that doesn't need to submit.
Also look out for things like conditional buttons, conditional colours (foreground and background), APEX behaviour considerations, among others.

If you're in Australia and you'd like training on this topic, I could demonstrate cool stuff for hours ;p

I also aim to get some organisation around github sorted when I get my laptop back, but I've at least put the export here so you can check it out directly.
https://github.com/swesley/apex-beginner-css-jquery

I hope you learn something new.

Scott

I'm still here.

It has been some time since my last confession.

It's been a while, but sometimes you get busy, ya know?

And a different sort of busy, the sort that left me wanting to escape to a different place. I spent a bit of time playing an old favourite, Civilisation.

I really like to write, consolidate ideas, and share insights in this forum, but it's rare that I have such a gap.

I also miss it.

I always have plenty of unfinished drafts, but I also usually have a few recent experiences that are like an itch waiting to come out.

There are also a few other factors. My laptop is just within warranty and I'm finally getting a line of pink pixels fixed. I'm also gonna wipe Ubuntu off it and go back to Windows.

I feel my OS sojourn was successful in that I realised in a career of learning such as development, some of us need to learn to swim in a pool, not the ocean. Not the best analogy, but close enough.

My workplace is always (usually) Windows, any exploration on my laptop can be within a VM from the comfort of Windows. I've also got accumulated decades worth of keyboard shortcuts, and the muscle memory alone keeps me to the original style keyboard, not the weird ones that Macs use.

And it turns out we're also gearing up to move house, well, start by putting our place on the market. That sounds like fun, right?

You'll find me escaping occasionally, back here, writing with passion about what I find to be an interesting career. That, and reading/listening to stuff about science. There is some amazing stuff going on in this world!

Frequent APEX questions

I've put a call out on the OTN APEX forum asking for suggestions from the community on what they think are frequent APEX questions, be it within the forum or elsewhere.

In part, I'm chasing ideas for content in future presentations, or blog posts. I welcome others to do the same. I think if we share our perception of common questions, better resources will come.

So please, feel free to contribute in any way, shape, or form.

What questions do you see or hear all the time?

If for some bizarre reason you don't have an Oracle account for the OTN forum, just reply to this post, ... then register an account. It covers you for the forums, livesql.oracle.com, the Dev Gym, entitles you to download stuff like SQL Developer, and no doubt more.

Though probably no steak knives.

Geek & Poke