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

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

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