Wednesday, 24 April 2019

Highlight 'current' card in APEX

Earlier today I posted a solution that required a bit of jQuery to execute after refresh of a report.

Avoiding post-processing is always nice, so here's an example that is resolved during render.

I would like to highlight the 'current' card in this classic report:

Cards Classic Report

The SQL that dynamically defines these cards includes the following column, with some decider over which record is deemed the current selection.
,case when :P17_ITEM = 'SOMETHING' then 'is-active ' end AS card_modifiers
Then we can include some CSS relevant to that card group, identified here by Static ID 'my_cards_r'.
#my_cards_r .t-Cards-item.is-active div.t-Card
  {background-color : green;}
This solution should also reflect appropriately after any refresh of the card region.
If you define a declarative List region, it also uses the is-active class when the list entry is current, so this CSS could also apply.

This is different to the #CARD_COLOR# substitution string, that drives the colour of the circles where your icon/initials are.
Here I used:
,'u-color-12' card_color
Do you have a preferred solution?

APEX Classic Report as Alerts

I love classic reports.
You can make your data look like anything, out-of-the-box, as previously highlighted by Christina and Carsten.

When you create a classic report, you can select different Report templates via the region attributes. I use the Cards and Alerts a fair bit, allowing me to produce data driven content, instead of whatever I type in the application builder.

This has been the general premise of APEX templates since the beginning. We define a query, and feed those columns to the template, where the values are substituted in.

Alerts Report Template
We can combine this template with the following query, containing matching column aliases.
select 
  'Alert' as alert_title
 ,'Be alert, not alarmed.' as alert_desc
 ,null as alert_action
 ,'danger' alert_type 
 ,'fa-user-woman' alert_icon
from dual
This doesn't need to be from dual, it can be any query you like, as long as it contains those column aliases. Multiple rows will show multiple alerts, down the page. The alert_type can be any of the pre-defined template options for that attribute.

Over time these templates get upgraded, and now we operate with the Universal Theme, we gain access to improvements after verifying the theme.

For instance, APEX 5.1 introduced #CARD_COLOR# attribute to limit the manual CSS required to make it happen.

What the Alerts Report template still lacks is the ability to customise the icon (as at 19.1).


We can customise the icon with a declarative Alert Region, by setting the Template Option Alert Icons to 'Show Custom Icons', and specific the icon in the Icon field.

If we want to customise the output of a classic report displayed as an alert, we can use an After Refresh Dynamic Action that executes JavaScript on page load to rejig the HTML of the report.
// replace icon in template, since we can't do it in SQL yet
$('#acting .t-Alert').removeClass('t-Alert--defaultIcons').find('.t-Icon').addClass('fa fa-user-woman')

Here I find the region I gave a static ID of 'acting', then removes the default icons from the component with the t-Alert class.
It then finds the t-Icon class to extend with my chosen icon.
This transforms it to exactly what a declarative Alert region looks like.


This example was built for a report with only one record, but hopefully gives you an idea of the potential these type of events have. Check out similar example here.

Happy APEXing.

Sunday, 31 March 2019

APEX Web Service Request Limit

Every now and then there's a new setting worth mentioning.

The New Features page included news regarding the logging of
Requests to external web services from inside the database
Firstly, this only appears to log requests coming from within the APEX environment.
Requests from scheduled jobs will not be logged, as of 18.2.

Secondly, moving to the cloud can be ... a learning experience. Since we're now using Social Sign-in, web requests are up. We also re-configured directly listings and file fetches to use web services. Web services are great tools.

However, there's a new setting that needs guesstimating before you expect too many users to log in, otherwise you might spot the following error in your error logs:
ORA-20001: You have exceeded the maximum number of web service requests per workspace

Search for this message and you'll find Dimitri encountered it during his experiences with AOP. Pierre points also points out the setting in a forum post.

Found in the Internal workspace, under Security settings, Workspace Isolation, there is a Maximum Web Service Requests attribute. There is a workspace level setting under Manage Workspaces, Existing Workspaces, then choose your workspace.

Maximum Web Service Requests attribute

However, the instance level setting has a default of 1000. You may need to review that value if you have a reasonable number of users using web services within Oracle APEX.

Now while the item help states:
Enter the maximum number of web service requests that Application Express supports for each workspace in this instance. You can configure a more specific value at the workspace-level.
This forum response from Carsten states this applies to a rolling 24 hour window.

I immediately envisaged a JET chart in our monitoring application, so we could visualise how this figure changes over time - and to see where it peaks. After a slight hiccup, I now see this graph that tells me out limit should at least 4000, to allow wiggle room.

Plotting apex_webservice_log

The really thin bars were an accident that I ended up thinking better represents the data, as compared to being a line graph.

So as activity occurred during the day, as depicted by the green line, the rolling 24h figure in blue crept up. This was calculated using an analytic function that allows me to create a moving window on the data based on an interval of one day. And I'm summing how many at each moment, hence the sum(count(*)).
select * from (
    select request_date 
       ,sum(count(*)) over 
            (order by request_date  
             range between NUMTODSINTERVAL(1,'day') preceding 
                       and current row)  last_24hr
    from APEX_WEBSERVICE_LOG 
    where request_date > sysdate-3
    group by (request_date) 
-- must ignore first day in inline query
-- as cumulative range will be skewed
) where request_date > sysdate-2 
order by 2
The 'Hits' count in the y2 axis is rounded to 30 minutes, finding inspiration from these forum responses.
trunc(request_date)+round(to_char(request_date,'sssss')/1800)/48
Without this rounding, the chart looked too much like a NY skyline, while the rounding smooths the staccatic nature of the data. Perhaps there's a JET attribute I could use instead?

What took me some convincing was the shape of the rolling average graph at night. I knew daily activity was essentially in the shape of a bell. I saw it in the y2 axis above, and I see it in the APEX activity logs.

Two days of APEX activity. Can you see lunchtime?
So I found myself building a test case with a few dozen rows representing the bell, just to check there wasn't necessarily a dip in the rolling data overnight, instead of flat-lining.

I'm was not entirely convinced, so I detailed a the test case in this post, looking closely at the range analytic function. Get some real "SQL-perts"? on the case. It's the same query as above, just with smaller, static data set.

And now I've seen the rolling activity drift down on Saturday mornings, I'm more confident with the graph outcome. It's almost poetry.

What happens on the weekend

I must say, playing with these time-based graphs has been a much easier experience in 18.x. I'm still getting the hang of which settings to use (particularly with dates), but at least I can just use the SQL I need, not extra bloat just to help the UI.
While I'm complimenting these charts, the database is also thankful we can now define the SQL once, and use the same source to plot multiple series.

Load test your applications, people.

Saturday, 30 March 2019

Hide Pagination if one page results - Oracle APEX

I love this period of development not long after an APEX upgrade, where I discover all these little improvements that will help us out. Stuff that's not quite noteworthy in the wonderful read that is the new features guide (that's not sarcasm. Read it. Now.)

Recently I made an observation regarding a handy new template option, one that hides pagination if there is only one page of records. I've had a bit of jQuery doing this for a while, and I thought I had blogged about it already, but it was a similar feature regarding no data found.

Sure enough, it was still sitting in an email labelled 'blog post'. Now I could write this off, like quite a few others where a built-in feature got released before I got around to writing up a post. But I decided to post about this one anyway, in part because it may take some time for some to reach 18.2, and I think it's part of an interesting conversation regarding the evolution of the product.

Some of the improvements in each APEX release are likely the result of
- a tweet about a feature
- a forum post asking about a quirk
- a conversation at a conference, likely while at least one person was holding a beer
-  a request on apex.oracle.com/vote

If you notice something odd, or think something you've done would help everyone, put it out there.  Chuck it on the forum. Make it your first blog post. You'll help the community, and reap the benefits yourself when the time comes to upgrade to that version with some delicious fruit.

--***

So, that being said, here was the template option, available in APEX 18.2, but only to existing applications only after refreshing your theme.

Classic Report Region Template Option

Applied to a classic report, it will hide certain pagination styles with there is less records than that specified per page on that report.

This will save me creating a dynamic action on the region, calling a small bit of jQuery I have in a library. It's also something I figured could be practice for turning into a plug-in, but I never got there, either.

The trouble is, this template option doesn't appear to work with my favourite pagination type (another reason for the post).

I like to use the Search Engine pagination style, on the top left - available to the user regardless of vertical/horizontal width of the region. I have chapter in my book describing how to turn standard HTML links in this search engine pagination for a legacy theme into buttons.


Trouble is, it seems a little superfluous when there's only one page of results. And it takes up vertical space. Hence the idea of the template option.

With a little JavaScript invoked in a dynamic action after refresh on the region - this is the default dynamic action when you right-click to create dynamic action on a region in page designer. Kudos to the APEX developers responsible for those little touches.


Here's the code I used to do the job.
if ($(this.triggeringElement).find('.t-Report-paginationText').children().length == 1
  && $(this.triggeringElement).find('.t-Report-paginationText').children().text() == 1)
    $(this.triggeringElement).find('.t-Report-pagination').hide();
A few points:

  • this.triggeringElement refers to the region, since that's what the dynamic action is firing off
  • find() is looking for page elements with the t-Report-paginationText class, in this case, the button group
  • children() counts how many buttons, so true if there is only one
  • I added the second condition when I realised it was hiding results when there were 11 pages of results, so I check if the button contains the text "1"
  • then it applies .hide() to the pagination group

Note, this is designed for Search Engine pagination in the Universal Theme, and the template option probably works with a different mechanism. There might be a clever way to solve this with CSS?

The real solution also belongs in a library so it can be re-used. I did it like this once, but I'm sure it could be neater.
function hide_pagination_iff_one_page (p_region) {
  if ($('#'+p_region+' .t-Report-paginationText').children().length == 1
  && $('#'+p_region+' .t-Report-paginationText').children().text() == 1)
    $('#'+p_region+' .t-Report-pagination').hide();
}
I would then put this in the after refresh dynamic action. It passes the ID of the region triggering the action, ready for embedding in the selector.
hide_pagination_iff_one_page($(this.triggeringElement).attr('id'));

So my open question is, how would you tidy such JavaScript? how would you place it in a library?
I'm still getting the hang of namespaces, and how to organise our stubs.

Friday, 29 March 2019

Visualising SQL Analytics Rolling Count with OracleJET in APEX

Back in around 2005, before the time of smart phones, I had some data.

I can't remember what the data was, but I was told that for it to be valid, it should roughly form a bell curve.

Sure, I'm sure I could have aggregated it, exported it to Excel, and plotted it to a graph, but this was 2005. There was no SQL Developer where I could copy & paste the results directly into Excel. No panel in Excel with a button that plots that data in one click. Back in my day, thing like that were a little bit more work ;p

This was a time when I enjoyed SQL*Plus, and I ended up writing a query to nest STDDEV within RPAD so it would plot asterisks down the page, in a vertical bell. In was a work of art. I'm sure I've still got the test case labelled in bowels of Gmail.

Fast forward to today, where I have data I know starts as a daily bell curve, but I'm applying a rolling 24 hour total over that daily resonance. How does that affect the wave?

I use today's fancy tools to plot the said data, and it doesn't turn out quite how I pictured.

This is my query below plotting real data using OracleJET charts in Oracle APEX.

The underlying data still formed a bell, more detail in a future post, but smoothing this out to a rolling 24 hour total - what should that look like? Why is there a flat line during no activity, instead of a dip?
No doubt there is a math-geek corner of the internet that explores this pattern, but I still wasn't confident with the query.

Real data is awesome. The trouble is, it can be real noisy. If you're trying to reconcile your results, it's often worth building a simple test case. As a regular forum participant, I'm such an advocate for this. If the person asking the question took the time to write the DDL for the test case, I reckon half the questions wouldn't need asking. The other half will have an executable test case ready for any responders.
How's this - in a response to a question I asked (with a test case, of course), Andrew Sayer responded with a LiveSQL demonstration! AskTom questions that have a livesql.oracle.com example are generally prioritised.

So, what I have here is a real simple table, with some "logs" at specific times, and a little variance. It's quite similar to the setup you might see for SQL exercises in the Oracle Dev Gym.
-- create a simple table of dates
create table range_eg  (rd date);
-- ready for adjustment
truncate table range_eg;
-- insert sample hits at a regular interval, with more frequency around noon.
insert into range_eg values (timestamp '2019-03-26 09:30:00');
insert into range_eg values (timestamp '2019-03-26 10:30:00');
insert into range_eg values (timestamp '2019-03-26 10:45:00');
insert into range_eg values (timestamp '2019-03-26 11:30:00');
insert into range_eg values (timestamp '2019-03-26 11:45:00');
insert into range_eg values (timestamp '2019-03-26 12:30:00');
insert into range_eg values (timestamp '2019-03-26 12:45:00');
insert into range_eg values (timestamp '2019-03-26 13:30:00');
insert into range_eg values (timestamp '2019-03-26 13:45:00');
insert into range_eg values (timestamp '2019-03-26 14:30:00');
insert into range_eg values (timestamp '2019-03-26 15:30:00');
insert into range_eg values (timestamp '2019-03-26 16:30:00');

-- I took a few hits out the middle, so the bell would not be quite the same shape
insert into range_eg values (timestamp '2019-03-27 09:30:00');
insert into range_eg values (timestamp '2019-03-27 10:30:00');
insert into range_eg values (timestamp '2019-03-27 10:45:00');
insert into range_eg values (timestamp '2019-03-27 11:30:00');
--insert into range_eg values (timestamp '2019-03-27 11:45:00');
--insert into range_eg values (timestamp '2019-03-27 12:30:00');
--insert into range_eg values (timestamp '2019-03-27 12:45:00');
insert into range_eg values (timestamp '2019-03-27 13:30:00');
insert into range_eg values (timestamp '2019-03-27 13:45:00');
insert into range_eg values (timestamp '2019-03-27 14:30:00');
insert into range_eg values (timestamp '2019-03-27 15:30:00');
insert into range_eg values (timestamp '2019-03-27 16:30:00');

-- I added a small day at the end, to see how it may affect rolling total drift to zero
insert into range_eg values (timestamp '2019-03-28 11:30:00');
insert into range_eg values (timestamp '2019-03-28 12:30:00');
insert into range_eg values (timestamp '2019-03-28 13:30:00');
Then used an analytical function to cumulate the running count over a 1 day interval.
The second column, with accompanying union, was an attempt to see what happened if I filled out each hour with a record, to see if that influenced the overnight dip in my real data. It also let me see the cumulative count taper off as the data ended.
select * from (
    select rd 
     ,sum(sum(c)) over ( order by rd  
         range between NUMTODSINTERVAL(1,'day') preceding 
                   and current row)  last_24hr_running
     ,sum(count(*)) over ( order by rd  
         range between NUMTODSINTERVAL(1,'day') preceding 
                   and current row)  last_24hr_cnt
     from 
      (select 1 c,rd from range_eg 
      union all
      -- I even tried to pad out records to see 
      -- if the line would vary during the lulls
      select null c, trunc(sysdate,'hh24')+1-rownum/24
      from dual
      connect by level< 24*3
      )
    group by (rd) 
-- must ignore first day in inline query
-- as cumulative range will be skewed
) --where request_date > sysdate-2 
order by 2;

Data checks out, SQL checks out - does the graph check out?

Test case plotted

I think so, but given the context of the data, I don't think this line graph is appropriate, particularly around the period of no activity. More on that in the next post.

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

12c

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

Result Sequence                                                                                                                      
---------------
A
B
C

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

ORA-00933: SQL command not properly ended

18c

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

Result Sequence                                                                                                                      
---------------
A
B
C
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
begin 
 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);
end;
/ 

select column_value as dt 
from tf_test();

DT
---------
01/JAN/19
01/FEB/19
01/MAR/19
01/APR/19
01/MAY/19
01/JUN/19
01/JUL/19
01/AUG/19
01/SEP/19
01/OCT/19
01/NOV/19
01/DEC/19

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.
declare 
  v_result varchar2(100);
begin
  for i in 1..1000000 loop
  v_result := user;
  end loop;
end;
/

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.
declare 
  v_result varchar2(100);
begin
  for i in 1..1000000 loop
  v_result := sys_context('userenv','session_user');
  end loop;
end;
/

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.
declare
  v_result varchar2(100);
begin
  for i in 1..1000000 loop
  v_result := nvl(sys_context('userenv','session_user'),user);
  end loop;
end;
/

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.
declare
  v_result varchar2(100);
begin
  for i in 1..1000000 loop
  v_result := coalesce(sys_context('userenv','session_user'),user);
  end loop;
end;
/

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"

f?p=LEMENU:LOGIN::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
TIMEOUT


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

lnnvl(:REQUEST = 'TIMEOUT')

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.
https://apex.oracle.com/pls/apex/f?p=100567:1
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
https://apex.oracle.com/pls/apex/f?p=100567:2:0:APEX_AUTHENTICATION=apex
https://apex.oracle.com/pls/apex/f?p=100567:2:0:APEX_AUTHENTICATION=open

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.
file:/home/oracle/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.

https://myhost:port/vbs/f?p=100::::YES::

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.

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

  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'login.microsoftonline.com',
    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

https://graph.microsoft.com/v1.0/me/?$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>'||
  '</table>';

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 login.microsoftonline.com; 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.

APP_USER


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 portal.office.com 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.

Conclusion


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

Wednesday, 16 January 2019

APEX 18.x Application Session Sharing

For quite some time I've been crafting multiple applications that, to the end user, appear as one.

This is possible with some Session Sharing attributes in relevant the Authentication Scheme, a feature agnostic to the Scheme Type.

This ultimately means you can set up two different applications that use two different methods of authentication, thereby letting in two different sets of users in the 'same' application - you just need to make some UX decisions.

APEX 18.x introduced a 'Type' option, and renamed the section from a less descriptive 'Session Cookie Attributes'.

Session Sharing Attributes

I thought this was offering something a little bit extra, but it appears to just make the process a little simpler for the likely majority.

We can still segment our applications, where users of applications with the same cookie name / colour may have links that jump seemlessly between applications, such as Earth and Mars.

Application sharing examples

This is possible only if you use the declarative 'link to different application' target, or include the session ID when you build the URL

f?p=EARTH:HOME:&SESSION.::

If the user tried to jump to Jupiter, then they will be prompted for login details. Ceres and Vesta stand alone with the default option, sharing authentication with no other apps, not even between themselves (unlike a certain spacecraft).

Upon returning to the authentication scheme for Earth, I noticed the Type actually changed back to Custom, and I saw an (undocumented) substitution string WORKSPACE_COOKIE used.

This saves us from having to nominate the cookie name, and store the literal ourselves, perhaps as a custom application substitution string. It just presumes that all apps in our workspace we nominate as such are to share authentication.

I thought I read some attempts on the forums to change the 'current' authentication scheme on the fly, but I don't see any mention in the documentation - Correction.

Thursday, 10 January 2019

Thursday Thought: Imposter!

Ever feel like you're struggling to keep up? With anything at all?

I'm feeling a bit of that with APEX right now.

Partially because I've been at a site that is a version or so behind, but who isn't, right?

But those fine specimens on the Oracle APEX team keep producing so many nifty things, I'm finding it increasing difficult to keep up. Same with the database itself. So many great new practical development features in 18.x.
Time to buy?

I'm sure I'm not the only one, and the fact I'm writing this 6 months after I noted the idea kind of illustrates my point. It was spurred a little bit from Tim Hall's post about the little funk he's feeling. Some of the issues he's been feeling resonate with my own. Work has been good and bad, my presentation game is a little off, and I'm not blogging as much. Heck, sometimes I used to schedule a few posts in advance, instead of posting a flurry.

There are a few major factors involved, including, but not limited to:
  1. Managing a house containing a little human learning to communicate

  2. Moving house

  3. Needing to work hard to prove what seems blazingly obvious to us - that building enterprise applications with APEX is an advantage.
They're all positives in their own right, just a little disruptive if you're used to having a little extra time to learn on the side.

However, as I'm sure I've moaned before, I miss the writing. The learning process of breaking something down enough to try explain the topic in a blog post - mostly so future me can remember how something worked, then replicate/adopt it for my new problem. "Oracle Things I Got to Remember Not to Forget"
It frees my mind a little, knowing I can let it go because I've noted this resource effectively for the future.

I also like sharing with the community. Let's wreck this together, right? If we're all helping each other, we're all building better applications, we're all pleasing our customers, we're all sustaining our careers.

Point 3 has a richer story I can't share here, but it is still great for learning how to build applications with a rich user experience. I think my UX & logic building is currently on point, but I'm seeing posts from people like Adrian, Morten, & Vincent just killing it, exploring new avenues, doing funky things.

Not that's really been my bag here on my blog, I've always been focussed on practical solutions with any feature available to us. Features that anyone could be using, possibly shown in a way you may not have seen before, or reinforcing and idea you have. So worrying what others are doing just feeds doubt.

So I'd like to tie this rambling back to learning APEX, and whatever funk may be nearby.

I feel my progress using this development tool has shown a reasonable learning curve, punctuated by a few events - such as the availability of the Page Designer, introduction of the Universal Theme, and understanding how to effectively use Dynamic Actions.

And when I use the term punctuated, I mean I might be working effectively with the tools I'm aware for some time, learn a new trick, gain a new feature, then things improve dramatically for a time.


And just as I started to write this post, I found an article (whatever he's selling aside) that really nailed what I was thinking. I recommend the read, by anyone with a career: https://taylorpearson.me/punctuated/

So if you're feeling in a funk, maybe it's just status-quo.
The next interesting development may be just around the corner.

If you still feeling a little of the imposter syndrome, particularly if you're currently working in a small team, I recommend considering the comments on this tweet from tech commentator @SwiftOnSecurity.

Of course, one solution to my problems is less Twitter, but... input.

Happy new year!