Thursday, 14 March 2019

My Kscope19 Sessions

The #Kscope19 session list is out!

I've scored two sessions in the Oracle Application Express (APEX) track.

APEX for Forms Developers

My APEX experience has now surpassed a reasonable run developing Oracle Forms, I think I offer something to help the vast amount of Forms developers make the transition.

Managing Multiple Applications

A lot of my APEX work has involved developing applications in modules of a whole, in part because of steady transition from Forms & Reports. I've been refining the ideas in this session for a while, I'm not sure which session I'm looking forward to the most!

Let me know if there's something in particular you'd like to hear about either of these topics.

See you in Seattle ;p

Thursday, 28 February 2019

APEX configuration problem - empty stack trace

This post is just here to save someone a few hours debugging one day.

We were trying to launch a new Tomcat instance, but we were facing a strange error.

There was no stack trace, and the debug trace started with
[TE] url-mapping start:
[TE] get /ords/f?p=ABC start:

Turns out we had listed some hostnames in the workspace isolation feature, in preparation for deep linking.

Our new server was not listed.

Function based tables

You've probably seen this somewhere already, no doubt from Connor, though I couldn't find much beyond Tim's post on pipelined functions - I can't find the right keywords to find related content.

I like table functions, so this will help me remember we no longer need to specify the table() operator in 18c (12.2).


select * 
from table(

Result Sequence                                                                                                                      

12c> select * from apex_string.split('A,B,C',',');

ORA-00933: SQL command not properly ended


select * from apex_string.split('A,B,C',',');

Result Sequence                                                                                                                      
This apex_string package is like the swiss army knife of string manipulation. I love it.
I think I'm surprised a few other posts haven't made it out of draft. This example felt a little like butchery, but it was an interesting play.

Here's a way to test it out with your own function, returning a supplied APEX collection type - a table/collection of strings.

create or replace function tf_test return apex_t_varchar2 is
  lt   apex_t_varchar2 := apex_t_varchar2(); -- ORA-06531: Reference to uninitialized collection
 for i in 1..12 loop
   lt.extend; -- ORA-06533: Subscript beyond count
   lt(lt.last) := add_months(trunc(sysdate,'yy'),i-1);
 end loop;

  return (lt);

select column_value as dt 
from tf_test();


12 rows selected
If you don't include the section with comments, you get the relevant error.

We need the trailing brackets even in the absence of actual parameters. We didn't in the old format, but I couldn't find the updated syntax diagram.

select * from tf_test;
-- ORA-04044: procedure, function, package, or type is not allowed here

We can subtract more code, just not the brackets ;p

Wednesday, 30 January 2019

Oracle USER vs sys_context

This post was inspired by the fact I couldn't find many good references for what I thought was 'previously discovered truth'. So what does any good science-nut do? Add their own contribution.

So here are two simple performance suggestions. The second was an added bonus I realised I could demonstrate simply.

1) Stop using USER

I'm using USER far less frequently anyway, since it has no context in Oracle APEX, but it is still a handy default value for created_by colums, and I'm sure some Forms programmers could add life with a small refactor.

Sven explores this in his excellent post regarding triggers in 12c (spoiler: there is typically no need for a trigger).
I thought Tim had a section in an article similar to this, but I couldn't find it again.
And there's a tweet. There's always a tweet.

I executed the following on a development server, and it took 47, 50, 50 seconds respectively.
  v_result varchar2(100);
  for i in 1..1000000 loop
  v_result := user;
  end loop;

50 seconds
Replacing USER with sys_context('userenv','session_user') took an order of magnitude lower at 2.5, 2.4, and 2.4 seconds.
  v_result varchar2(100);
  for i in 1..1000000 loop
  v_result := sys_context('userenv','session_user');
  end loop;

2.5 seconds
It's no longer context switching between SQL and PL/SQL.

2) Stop using NVL

Instead, consider COALESCE, or other null-related functions.

So consider the previous test, but NVLing both expressions. At 58 seconds, the time taken seems like the sum of both, plus time to evaluate. Connor has more detail on the difference.
  v_result varchar2(100);
  for i in 1..1000000 loop
  v_result := nvl(sys_context('userenv','session_user'),user);
  end loop;

58 seconds
By swapping the NVL with a COALESCE, you utilise a programming concept called 'short circuit evaluation'.  Results: 2.5, 3.1, 2.5 seconds.
  v_result varchar2(100);
  for i in 1..1000000 loop
  v_result := coalesce(sys_context('userenv','session_user'),user);
  end loop;

2.5 seconds
I'm sure there's the odd "it depends", but if you don't take the performance freebies, what are things going to be like when you tackle to nasty queries?

Tuesday, 22 January 2019

Customising APEX Session Expiry

It's nearly 8am, you're holding your favourite morning beverage, and you open yesterdays APEX tab, only to find this:

This is the current default expiry page. I'd like to tart it up.

I've used the following technique for so long, I've forgotten what it used to look like to drive me to this solution. Ultimately, you end up with a similar result, but you really can customise it to behave however you like.

Under Shared Components -> Security -> Session Management, you'll find the Session Timeout URL attribute. Here we can specify what page should be opened when the application times out.

If I use the following, it will open the login page just as it normally would have, but also include a REQUEST parameter, with the value "TIMEOUT"


We can define this value to be whatever we like, but the intention is that on the login page, we can conditionally render a region based on the declarative condition:

Request = Value

Where TIMEMOUT is the literal string we provided.

Here I've used it to conditionally display a region that uses the Alert template.

Useful feedback for the user

The only trouble is, when your original session times out, Oracle initiates a new Session ID - which will also time out. Then, when you press the login button, you'll just see this.

Your login page has timed out!

So we could extend the region I added by providing a link to re-open the login page, forcing a new session.
Your session has timed out.
Click <a href="f?p=&APP_ID.">here</a> to log in.

And add a condition to the original Login region to exclude when the timeout message is being displayed, forcing the user to click the link to re-open the page, thereby initiating a new session. I use this SQL expression to test the opposite, since the request value may be null.


Clean timeout message

You could pimp up the link by adding UT classes, with help from the UT button builder
class="t-Button", since buttons are easier to tap.

Or just add a declarative button to the region.

Or you could just set your Timeout URL to redirect to a completely different page, where the Authentication page attribute is 'Page is Public'.

Nothing too exciting, but I do like the options a REQUEST parameter can offer - transmitting information with no need for a defined parameter name.

This is one of a few ideas shown in this presentation, from slide 22.

Thursday, 17 January 2019

Authentication - Switch in Session

It's only taken a year, but I've finally checked out the ability to switch authentication schemes at runtime with 18.x. It's mentioned in this 18.1 new features slide deck, and the new features list in the documentation.

Such a frequent request in the forums is to either share authentication between sessions, or dynamically change the authentication scheme - which hasn't been possible until APEX 18.1.

I've set up a sample application, where the home page is public, and contains a concise summary of this post.
Clicking 'Secured Page' in the menu will open page 2, forcing default open-door authentication, if not already authenticated.

The default authentication scheme is open-door credentials, but there is another defined for APEX accounts. I've given them a simpler name for simpler parameterisation.

Authentication Schemes available to app

The non-current scheme must have the following property set to Enabled.

Authentication Scheme - Switch in Session

However, is this opening up a security issue, offering the end-user an ability to change authentication method on the fly?

Now the following two links allow toggling between authentication schemes at runtime

If this REQUEST parameter is present, it always appears to force fresh authentication.
However, :REQUEST returns null when attempting to use as a condition on the page.

This is an alternative to the application session sharing technique, and may be useful when integrating social sign-in.

Another use case could be for the related development environment. If you're using OAuth2 authentication, it probably won't be practical signing in as other users, so enabling switching back to an older authentication scheme could be useful.

Thanks to Morten for the heads-up.

Oracle APEX Social Sign-in Authentication Scheme

Recently I was involved in setting up a Social Sign-in Authentication Scheme, so despite the doom & gloom of this post, we are breaking some interesting rock.

I say 'involved', since I had the support of one of the nerdiest nerds in Perth, for all the server tinkering. I just had to paste in some URLs and other config settings within APEX.

He expected every error, and it always seemed just due process to the finish line. We had it all sorted within a couple of hours, and I was really doing other things while they configured SSL & wallets.

I can't really divulge all the details, since, you know, security, but I can certainly help describe the steps, to maybe help future tinkerers.

This post from Ciprian Iancu was a major reference for the team, though it wasn't entirely accurate for us. Details below.

<handwave> this is handwaving I'll use when I either don't fully understand what was done / is a security issue / just noted as a prompt. I'm the data/UX guy ;p
We also moved so quickly, I didn't have a chance to copy all the errors.

1 - SSL 

Our new Tomcat server needed to be running with SSL before Azure would accept a handshake.

<handwave> There is something they do that allows me to use https

2 - Wallet

<handwave>A wallet was configured on the database server. There is talk about ensuring the one wallet contains numerous certificates, since we now need it for a number of exchanges.

I entered the wallet's file location in the Internal workspace under Manage Instance -> Instance Settings -> Wallet.

Without it, we get a warning regarding certificate failure.
At one point the permissions on the wallet file were wrong, which I saw by opening the login page in debug mode, then checking the debug log.


You should see an entry starting with apex_authentication.callback

Debug example

3 - ACL

ACLs allow the database to communicate to certain hosts in the outside world. Ciprian's blog was helpful, but the ACLs weren't quite right. The ACL API has changed in 18c, the graph host was incorrect, and we only needed http, not connect.

    host => '',
    lower_port => 443,
    ace  =>  xs$ace_type(privilege_list => xs$name_list('http'),
                        principal_name => l_user,
                        principal_type => xs_acl.ptype_db));

    host => '',
    lower_port => 443,
    ace  =>  xs$ace_type(privilege_list => xs$name_list('http'),
                        principal_name => l_user,
                        principal_type => xs_acl.ptype_db));

4 - Web Credentials

It took a little while to know this is where we needed to define something prior to creating the authentication scheme, as noted in this bug description. Without it, we are unable to define the authentication scheme.

Web Credentials

<handwave> Mr Sysadmin set up a Client Secret somehow in server land.

I then pasted the long string under Application Builder -> Workspace Utilities -> Web Credentials.

5 - Create Authentication Scheme

I always find some strange satisfaction in setting up an authentication scheme, and to do one that integrates with Azure using contemporary methods was particularly gratifying.

First round of settings

The User Info Endpoint URL allows us to harvest more information from Azure. By default, a small set of attributes are returned in a JSON packet, but we can extend the attributes supply by extending the URL$select=userPrincipalName,onPremisesSamAccountName,mail,officeLocation,department,displayName,givenName,jobTitle,mobilePhone,surname,id

Post-Authentication Attributes

I found an OTN post from Christian Neumueller describing exactly how to apply the apex_json package to get attributes listed in the settings - I just made some adjustments to make it look a little prettier, and add the fields we wanted to look at.

I'd really love to see examples like this in the inline help for attributes more often.
Here is what I used in the post-authentication procedural code:

:G_USER_INFO := 'Authenticated via Azure. '||chr(10)||
  '<br>Details from Graph:<br>'||
  '<table class="t-Report-report">'||
  '<tr><td class="t-Report-cell">id:</td><td class="t-Report-cell">  '||apex_json.get_varchar2('id')||'</td></tr>'||
  '<tr><td class="t-Report-cell">userPrincipalName:</td><td class="t-Report-cell">  '||apex_json.get_varchar2('userPrincipalName')||'</td></tr>'||
  '<tr><td class="t-Report-cell">onPremisesSamAccountName:</td><td class="t-Report-cell">  '||apex_json.get_varchar2('onPremisesSamAccountName')||'</td></tr>'||
  '<tr><td class="t-Report-cell">mail:</td><td class="t-Report-cell"> '||apex_json.get_varchar2('mail')||'</td></tr>'||
  '<tr><td class="t-Report-cell">displayName:</td><td class="t-Report-cell">  '||apex_json.get_varchar2('displayName')||'</td></tr>'||
  '<tr><td class="t-Report-cell">givenName: </td><td class="t-Report-cell"> '||apex_json.get_varchar2('givenName')||'</td></tr>'    ||              
  '<tr><td class="t-Report-cell">surname: </td><td class="t-Report-cell"> '||apex_json.get_varchar2('surname')||'</td></tr>'||
  '<tr><td class="t-Report-cell">officeLocation: </td><td class="t-Report-cell"> '||apex_json.get_varchar2('officeLocation')||'</td></tr>'||
  '<tr><td class="t-Report-cell">department:</td><td class="t-Report-cell">  '||apex_json.get_varchar2('department')||'</td></tr>'||
  '<tr><td class="t-Report-cell">jobTitle:</td><td class="t-Report-cell">  '||apex_json.get_varchar2('jobTitle')||'</td></tr>'||
  '<tr><td class="t-Report-cell">mobilePhone: </td><td class="t-Report-cell"> '||apex_json.get_varchar2('mobilePhone')||'</td></tr>'||
  '<tr><td class="t-Report-cell">Access Token: </td><td class="t-Report-cell"> '||apex_json.get_varchar2('access_token')||'</td>'||

I had a simple region on the home page to display the data.

Region on Home Page, showing G_USER_INFO

Then when we launch the application, we're redirected to; enter our Azure credentials, accompanied with the client's splash; then see our APEX page.

G_USER_INFO displayed at runtime

We were prompted to stay signed in, but I keep pressing No until we have a chance to experiment further.


APEX uses the Username Attribute to source the APP_USER variable. In our case the userPrincipalName returned an email address, but we wanted to match this up to our existing login IDs which use the WESLEYS style format.

We first tried using the onPremisesSamAccountName attribute, but that returned "wesleys"

Christian Neumueller once again provided an easy solution to ensuring our APP_USER returned the same uppercase value, by adding this to the post authentication process:

apex_custom_auth.set_user(p_user => upper(v('APP_USER')));

And in 19.1, there's already a declarative "Convert Username to Uppercase" attribute.

Logout URL

This was originally pointing to the application home page, which just re-authenticated me - which was quite handy as I refined the handling and temporary display of attributes from JSON.

We still need to experiment with authentication behaviours when it comes to remembering who you are, and logging out. For now, I know we can go to to log out of Azure.

No doubt there are other experiences & behaviours to refine as we move forward, but this is a nice step away from database accounts!

7 - Link Applications

This experiment was done on a fresh application, but I was able to use the principles behind sharing application authentication, to then create a button that linked to our main application - already authenticated.

I hear there is now a way to programmatically change the current authentication scheme, via a parameter. I'd like to experiment with that to see how it might change my answer when people ask if we can authenticate into the one application using two authentication schemes.


We had this hooked up within 2-3 hours. That can't be a normal experience, but it's still been a really good exercise in understanding how this technology works. Kind of.

It's also pretty cool that we have such declarative options for setting this up in our APEX applications.

If you want more examples to help get your configuration working, maybe filling in the blanks I left, see other posts by
- Dimitri - Social Sign-in with Google and Facebook
- Ciprian - Social Sign-in with Microsoft Azure
Adrian - Social Sign-in without a wallet
- Adrian - Certificates
- Morten - Authentication with Microsoft
- Mahmoud - Forum solution with Google