Loading...

Follow Grassroots Oracle on Feedspot

Continue with Google
Continue with Facebook
or

Valid
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?
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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-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.
Happy APEXing.
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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.
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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.
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.
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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 wouldn't 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.
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Grassroots Oracle by Scott Wesley - 2M ago
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
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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.
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Grassroots Oracle by Scott Wesley - 3M ago
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.

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

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

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
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
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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;
/
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;
/
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;
/
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;
/
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?
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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.
Read Full Article

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview