Oracle XE 18c and #OracleRAD

This post is one of a series on what I learned while not at Kscope18.

Oracle XE (Express Edition) 18c - which is more than just a free, limited db.
Combined with the notion of #OracleRAD, you've got a mongo killer ;p

#oraclerad

Quite a few people had a shot of the XE 18c feature list.

New Oracle XE 18c coming soon and might have yearly revisions - this is huge... if your #orclapex app can fit in 12 GB you have almost EE edition for free #oraclerad pic.twitter.com/HPDQP0v6kt

— Dimitri Gielis (@dgielis) June 10, 2018

Impressive as that is, when you start putting it into context of the sort of things that can now be achieved with the technology available today, it's mind boggling.
And Connor knows just how to amaze us the potential of this technology, making amazing concepts sound just within reach.

and so much more.... unplug central dev environment pdb shared by team, plug into your own XE, work in isolation, then merge into source code control. Then when central dev is updated, re-plug to get others work...its like Git for db

— Connor 🙃 McDonald (@connor_mc_d) June 11, 2018

But I think the best way to think about it is how this mini-stack adds up.

.@mikehichwa1 introducing the #oraclerad stack at #KScope18 pic.twitter.com/nDwIIUuVBA

— Chris Saxon (@chrisrsaxon) June 10, 2018

We've got a free Database; with a middle tier that can handle all our web services needs (REST), while still using SQL; and APEX in the middle, giving us access to pretty much every capability the web offers today, where the productive IDE lives within the browser. Simply, wow.

I'd like to end this post with this tweet I saw just before Kscope18 began

Observation:

Apple is losing developer mindshare.
No developers, no platform.

— Henrik Joreteg (@HenrikJoreteg) June 4, 2018

This XE 18c will be magnitudes more advanced than XE 11g, and I think I'm understanding the true potential of this. I can't wait to find a stream of Mike Hichwa expressing his passion for this release.

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.

2015 Blog Review

Time for my look back on 2015 and what I might aim for in the coming orbit around the sun. The highlighted word is dedicated to the #flatearth people I've been conversing with on Twitter recently. Wow.

Like many people, part of the reason for doing these sorts of things really what every person should do semi-regularly -> write down your aspirations. I remember being taught goal setting from a young age and I think it's a life skill that many leave behind.

Same topics as last year, with a different order by, but they're all tightly coupled anyway ;p

Tools

Ahh, so many things to play with, so little time.

APEX

There will be a lot going on for APEX again this year. No doubt you've already read Joel's summary of 2016. Oh to live in the US/Europe (or have fast, cheap, efficient modes of transport).

Content for APEX at Kscope16 looks amazing, it's a shame it's a touch early to see stuff about OracleJET and the Interactive Grid in 5.1. That's OK, I'm still learning the full impact of life in 5.0.

I'll continue to work on Universal Theme projects, which pleases me. Plenty still to learn and absorb, adjusting to our evolving development framework. Thanks again to the development team for the Page Designer, it has vastly improved productivity.

I would like to see Share Components integrated into the Page Designer, certainly for viewing but I'm sure even editing could be cleverly integrated with modal dialog pages. I often operate with two browsers running the builder, Chrome for Page Designer; Firefox for Shared Components.

I'd like to hear more about how the Universal Theme will change, what sort of life cycle it will have, and how information about change is relayed. I know it's changed since inception, and no doubt within patches. I wonder if any tweaks fix the little issues we've been finding. On my list is to copy an app, verify the them subscription, and try it out. Maybe export the themes and do a diff?

Anyway, I've found CSS use has cut greatly since running UT, I've cut plenty from converted applications. I've even modified the balance I'm looking for between Dynamic Actions and jQuery.

I truly could rattle on for ages. My three developer buddies at work will testify to that.

ORDS

Personally I would  like to see more on ORDS. I feel related documentation is still maturing and there only seem to be a small handful of experts sharing their experiences & techniques.

As someone with a prominent development hat, I'm thankful to have a colleague that understands that middle tier better than I. Unfortunately we're a small team and setting up JMeter for load testing is still on the list.

I saw a bit of chatter online regarding mixed content issues on version 3.0.3, and performance issues after 3.0.1, both of which we experienced empirically. The ORDS team must feel what Android software authors face were there's a zillion combinations of infrastructure.

Even though ORDS is sprouting it's own wings, I think the APEX and ORDS team need to stay tightly coupled. At this point I'd guess most ORDS deployments at least serve APEX. I've already overheard the observation that most people see it as a just that, and clients don't split hairs when something goes wrong.

"APEX is down", and nothing worse than middle tier experts saying "not us" when it's certainly outside the database layer. We can't wreck this together unless the arm supporting ORDS gets more support.

That's my plea for the (fantastic) Oracle developer advocate group in 2016 - more aid for ORDS. Maybe a hashtag would help? ;p #ORDShelp

12c

Our current client upgraded to 12c over the festive period, so no doubt I'll have a few development experiences to share with this newer environment. Probably on topics related to JSON and SQL syntax. Look out for #db12c

Text Editor

For a long time I've used Textpad for my text editing needs, but I'm tinkering on the edge of leaping to either Sublime or Atom. This needs to happen this year. Eddie, get me on it!

Community

Last year I said to Juergen that I'd help out with apex.world. So far I've written the intro post for Slack, but I still haven't honoured the other task he sent my way. I could blame the book, tendinitis, the holiday period, but I know there's procrastination in there, too. Sorry mate, soon.

I doubt I'll get to any international conferences this year, but I'm going to start paying more attention to the Asia-Pacific region. I'm on the lookout for communities I'm yet to discover. That sounds like a tautology, something self evident, but let's call it a known unknown.

I'll certainly hang around on Twitter and Slack, and I might get back onto the PL/SQL Challenge. I gave it a break for a while, but I need some 12c tips. I asked Tom Connor a question recently, I might spend some time perusing the headline questions, too.

Blogging

I intend to spend a bit of time finishing all sorts of semi-drafted posts, technical and otherwise. I'm not longer writing a book and this yearn to write has to go somewhere. I still have plenty of notes from my experience at Kscope15, which was amazing. I look forward to the time where I can travel more.

I have fun when analysing the statistics from the previous year. I think many of us unknowingly love assessing numbers and statistics, which is my hypothesis as to why cricket and baseball are so popular.

Readership growth has tapered, which I find interesting. Does that mean I've hit most people out there, looking for stuff, or have I just filled up my current channels?

I must say it was fascinating at Kscope to hear about people who've read my blog or heard I was there and sought me out. Rambling on the keyboard to a faceless audience is weird sometimes, so I was thankful for the positive feedback. People compliment on my writing style, and I humbly accept and strive to improve.

Back to the stats, the most telling of which is browser usage

• Chrome up 16%
• Firefox down 11%
• IE down 23%
• Safari up 27%
• Opera down 18%

Big differences, no real surprises, is there?

Mostly read on desktop, but mobile up 64%, but that's relative, not absolute. 3k mobile readers vs 53k on the desktop. Similar relative growth for social media vs organic searches. Twitter certainly is a growing factor in disseminating (and finding) technical content.

Plenty of interest in APEX 5, 1500 visits alone from Patrick's meta post aggregating APEX 5 articles
http://www.inside-oracle-apex.com/oracle-apex-5-0-articles/

I find this curious every time but only two articles from 2015 made it into top 10 most visited. One was Sample community applications, which makes me want to progress this idea further with the gang at apex.world. Second was my reference to Carsten's listagg clob.

To add perspective, the reigning top page represents 4.7% of visits. If I interpret this correctly, the top 10 pages represent over a quarter of the year's page visits, which is quite the heuristic.



Strangest search term?
apex 3.x dynamic actions

Books

My first book is complete. What a journey that was, with Kscope and APEX 5 thrown in between. A little shame about chapter 9, more on that to come, likely in some form of errata. I'll hopefully learn from that lesson.

Anyone who has read it, thank you and I hope I can get the updated chapter to you soon. I hope any reviewers hold out until they've had a chance to read through ;p

I bought a few myself with the Cyber Monday sales that I need to start reading. A have a couple on node.js but I look forward to learning heaps in the revised Expert Oracle APEX.

I need to fit a novel or two in as well. We're have a short holiday in Bali soon, I no doubt have a good sci-fi ready to go.

Videos

As I suggested in Monty's post about my APEX game, I've considered recording little videos showing I build the game, in addition to the presentation I did for the local user group. Nothing fancy, certainly not like Connor and Tim's videos, just little tutorials that watch me work. Even little things like rebuilding my sample application in the Universal Theme.

I've also got a lot of video watching I want to do. I have a few set to go from ODTUG already, such as webinars I've missed because they've been on at 1am. I've barely seen many of Connor & Tim's yet, I just don't watch many videos, I prefer to read my content. So I've got to schedule some video watching time, certainly before Kscope16 videos flood in. They're my best alternative to being there, that and participating in the Twitter stream during the conference.

Science

As with any year, 2016 has plenty of interesting science to look out for, and I love it. I will continue to also tweet science stuff I find interesting, in addition to the usual #orclapex #db12c #jQuery hashtags.

I've been planning to post a list of my favourite podcasts for a while. For those interested I have plenty to recommend. They make great listening during transit to work. Exercise that brain.

Health

Having a toddler has introduced a lot of viruses and some bacterial infections to the house. This made exercise a little difficult, but I have managed to swim regularly with a small group of guys at work. We do a steady 1km at lunch, aiming for once twice a week.

That's awesome, but I have equipment at home I need to use more often in the morning, particularly with the amount of chocolate I eat, which has to come down!

But first, I have to wait for what's apparently tendinitis in my right hand to calm down. I've done a decent job at resting it, but maybe that leaves room to help improve mental health.

Take a walk, enjoy the sunset. Rest your eyes and let your brain do some awesome stuff in the background while you're admiring the world.

On that note, enjoy your fresh start!