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.

Wednesday, 20 December 2017

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.
      -- 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.
      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
      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 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?

Thursday, 14 December 2017

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 (, 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.

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, 8 December 2017

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;


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
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.
     0.30 secs (.0003 secs per iteration)  -- UNION
     0.25 secs (.00025 secs per iteration) -- UNION ALL
     1.72 secs (.000172 secs per iteration)
     1.09 secs (.000109 secs per iteration)
    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.
     6.79 secs (.00679 secs per iteration) -- UNION
     2.85 secs (.00285 secs per iteration) -- UNION ALL
    42.91 secs (.008582 secs per iteration)
    19.89 secs (.003978 secs per iteration)
    31.70 secs (.00634 secs per iteration)
    22.83 secs (.004566 secs per iteration)
    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.

Thursday, 7 December 2017

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.

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.

Wednesday, 6 December 2017

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

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.