Pages

Tuesday, 14 April 2020

From an e-mail I sent to colleagues, I thought I should also share it here (revised):

I'm known as the office optimist. And I do literally have many things to be grateful for. I still have a job, I’m able to work from home, and I have a decent study setup. And I’m an experienced introvert, this shelter-in-place business is no problem for me. I feel like one of the last people who’d go stir-crazy.

But recently I felt I had a crappy week. Worst in some time, and nothing really to do with current events. A vital piece of software kept flaking out on me, and I felt I didn’t achieve what I was aiming complete.

I opted out of an attempt at a virtual Friday Happy Hour for related reasons, even though I timing worked out that probably could have given it a go.

In hindsight I understand this is also in part due to a number of reasons I just made up for myself, to justify “not coming”, such as feeling like the audio will be awkward, or even just setting up the video/audio feed.

I’ve been listening to a few select podcast to keep up to speed with current events, Deep Background with Noah Feldman being one of them. But this particular episode from 27th March, "How to stay sane during a pandemic", seemed to have been a fly on my wall. I found it really hit the nail on the head, and I feel there’s messages in there that will speak to all of us, from a professor in happiness, no less.

The world has obviously lost a number of in-person conferences in recent times, but luckily we have people like Martin who asked the APEX community to jump, and they responded "how high?"

And out comes 24 hours of 24 speakers with APEX @ Home, on April 16th 2020. A lot of regular speakers appear to have pulled out some awesome sessions, and then there's my dinky one on APEX logic suggestions.

It's an AskTOM Office Hours session, so it's also recorded for later viewing, but you'll miss the chance for live Q&A.

And you'll also miss the challenge Connor put out there - to get a virtual selfie with all 24 speakers. Good luck with that.

What I've wanted to do, but haven't had the time, is roughly plot the time zone differences for each speaker. I'm talking at 8pm, but I bet it's all over the shop for every speaker, at various times of the day!

Sunday, 15 March 2020

In my previous post about tweaking APEX classic reports, most of the settings were "low-code" configurations. All except the final line of JavaScript that moved the radio group from its default location, to the region's title bar where we'd normally find buttons.

But how did I conjure that statement?

When I wrote my book on jQuery in APEX, my general premise was each web page can be queried like a database can, you've just got to learn how to apply the filters to find the relevant component.

Browser Tools

In most browsers, if you right-click on any component on the page, then choose 'Inspect Element', you'll see a window appear showing you what the web page looks like prior to markup.

I can also use F12 in Chrome on my laptop to bring up this console window, but don't presume that's the same anywhere - I borrowed a laptop for a webinar once, and it put the laptop in aeroplane mode. I digress... you can dock this console window to the bottom, side, or have it floating elsewhere.

Where is my item?

If I inspect the radio group, it will take me to the specific radio option, in this case 'Accounting'.

As I move the mouse up the tree, different portions of the page will highlight, signifying exactly what part of the page the HTML represents. Sometimes you'll also see orange & green, signifying margin & padding respectively.

Find the relevant component on the page with help from highlighting

I kept going until I found the P29_DEPT_Z_CONTAINER, which holds all of the contents of my radio group item P29_DEPT_Z. The id property allows us to 'query' this page using the filter (selector) #P29_DEPT_Z_CONTAINER.

I can wrap this selector with the $() function to return that portion of the page, a portion I would like to move somewhere else.

You can also see what happens when you enter this in the console window - it returns a result.$('#P29_DEPT_Z_CONTAINER')

What function do I use?

I use this jQuery cheatsheet to help identify the relevant command I need. Sometimes there's alternates depending on what expression is on either side of the equation.

In this case, I want to take my radio group, and append it to somewhere else on the page.$('the object I want to move').appendTo('where it is going');

Where is it going?

The next step is to identify the part of the page where my snippet of HTML will be going.

I can use the same inspect element technique to locate a region already dedicated to placing buttons.

Find the destination using the same technique

Here I've located my zhuzh region by the Static ID I've applied (purple), and there is a div the represents the location of the buttons. I can identify this part using one of the classes (red)

Classes are prefixed with a dot when used within selectors.t-Region-headerItems--buttons

You need to take care when determining which selector to use. IDs should be unique (but aren't necessarily), and classes certainly aren't unique.

A pairing of ID and class is often an effective combination, but you need choose the right class. One you've added your own is often safe, and you can inject classes into the page using various APEX attributes. They can be anything.

This t-Region set of classes is defined within the Universal Theme. One early problem with APEX was these classes would change from theme to theme, as we didn't have a universal theme, so migrating custom code could be awkward.

The UT has changed some classes over time, but documented ones should remain constant.

Verifying the selector

Using the find function in the Elements tab allows you to test your selector, test how many results are returned on the page. If I just use the class, I actually get two results - one for each standard region I have on the page.

Count components on the page by searching in element tab

Combining these selectors will ensure I only get the button class for the relevant region. This is what forms my destination selector in the appendTo().

Testing the move

We can test the final command in the Console tab of the browser tools.

If I test this without using the #id selector, then the radio HTML will be appended to both regions.

Test your command within the runtime page

Once you're happy with the result, you can add it to the relevant portion of your page. In this case, I put it in the page attributes 'Execute on Page Load', though often it's within a dynamic action.

Playing with CSS

While we're here, it would be remiss of me to mention how you can play with the CSS attributes in the browser tools.

Play with styling on the rendered page

In this example I found the background colour property of the radio group label, and turned it green.

This was done without re-rendering the page, but it's only relevant until I do re-render the page, just like when we applied the console command to move the radio group.

But it's a great way to test/tweak CSS commands to see the result before applying it as CSS content rendered with the rest of your page.

The syntax would be similar, we would take a selector, but in the same syntax as what's found in the Styles sub-tab. You can copy/paste from the tab if you're not sure.

.t-Region-headerItems--title {color : red}

This CSS in the page would apply the styling during render of all page markup, but we could also do this on demand with jQuery, with just a slight adjustment to the syntax - and this is but one variation.$('.t-Region-headerItems--title').css('color', 'red');

Conclusion

These concepts can arm you with some nifty behaviours, above and beyond what comes with APEX.

Or to think of it a different way, if can really empower your use of dynamic actions, as the same instruments can be wielded as a jQuery selector, dynamic action condition, part of set value action, to name a few.

Base Behaviour

The classic report is straight out of the wizard, this example performs a cross join to inflate the data set.select e.* from emp e cross join emp e2
where :P29_DEPT is null or e.deptno = :P29_DEPT

A where clause has been added to filter by Dept, if supplied.

Page Items to Submit

Note one of the most important properties in APEX - Page Items to Submit, in this case nominating the P29_DEPT item.

There is a Dynamic Action defined on Even Change for the item, which simply refreshes the nominated region.

Simple Dynamic Action

If we don't set Page Items to Submit on the region, then the database won't know about the change made on the browser. Any item specified in this list upon refresh will have the current value set in the browser sent to APEX session state (a key/value table in the database), so when the query binds the value, the database knows what the browser knows.

So when I select a department, the employee list refreshes to show the relevant department.

Make Left look like Right

This may seem like a long list, but it doesn't take long at all once you know where to click. I estimate 23 clicks, as of 19.2.

Modify Region properties

Change Template Options for region

Tick Remove Body Padding

Tick Show Region Icon

Add Icon: fa-list

Add Static ID: zhuzh

Modify Report properties

Change Template Options for report

Tick Stretch Report

Report Border Horizontal Only

Change Pagination Type to Search Engine.

Sometimes you may which to turn Heading Type from Custom to Off, or at least disable the sort (by adding order by to SQL, or disabling sort on columns)

Modify Item properties

Change Type to Radio Group

Change Number of Columns to 4 (something relevant to your list. Usually useful for items with small number of options)

Change template option Item Group Display to Display as Pill Button

Modify Page Property

Set Execute when Page Loads$('#P29_DEPT_Z_CONTAINER').appendTo('#zhuzh .t-Region-headerItems--buttons')
This moves everything holding the radio group together, to a spot made for buttons in the region.Not something I do often, but can be an economic use of space.

Notes

We've found the search engine pagination style great for touch devices, but I tend to prefer the Display Position on the Left, and at the Top, or at least Top & Bottom.

Report Template Options offer a facility to hide pagination when all rows displayed, but I've never seen it consistently work as I expect, so I continue to use my own JS library call for that.

The static ID can be whatever you like, so long as it doesn't clash with other IDs on the page, such as item names.

See my next post for more detail on how I derived that line of JavaScript, and what you can do with the browser tools.

Tuesday, 18 February 2020

While I point out a 'hidden' setting for Switch items in APEX, I want to comment on two related item types found in Oracle APEX => radio buttons and checkboxes.

1) Radio button

I really think the standard Template Option for radio groups should 'Display as Pill Button'

It really provides a simple and effective UX when a small set of options are on offer. Touch devices can select the option as if it's a button, and mouse users only need one click, compared to a select list requiring two. And the click doesn't need to be precise for those tiny radio group circles. I wish the application builder adopted the pill button approach more often.

And it's real easy to hook a dynamic action on change of the radio group, perhaps to refresh a classic report with data limited to the selected option. (Don't forget to add page items to submit!)

2) Switch

The Switch item was the answer to a number of methods of deploying an "iPhone-like" on/off switch.

I actually wrote an (internal) item plugin to do just this, but I much prefer using the native option.

A frequent question relates to how switches are rendered. If you don't see what you expect, have a look in Shared Components -> Component Settings.

Application -> Shared Components -> Component Settings

Personally I prefer the 'Pill Button' look, for reasons above. I find the APEX Application Builder seems more washed out with all the Switches in the properties bar, compared to the more softened On/Off pill buttons. If I could change this at the builder level, I would.

The offerings will vary depending on your APEX version, and if the application's Universal Theme has been refreshed.

Application level settings for plugins can also be found in Component Settings, in addition to built-in APEX feature tweaks. These can something you choose to review after an APEX version upgrade.

3) Checkbox

It seems the humble HTML checkbox will always be extant, regardless of the pain it brings to tabuler forms. However, it suffers the same precision requirement as the native radio group selections - though better deployments allow selection using the label as well.

Monday, 10 February 2020

For all those pushing data around, especially dirty data, this one is for you.

Today I was preparing to process data I loaded from a spreadsheet.
A simple filter was required - to ignore the header row, had it been included.

I'm lucky enough to be working on 19c, and I remembered that a reasonably new function should help me out with all many of data loading issues. With a quick scan of my favourite reference manual, I found VALIDATE_CONVERSION.

For example, this gives me 'ORA-01722 invalid number' because of the header row I failed to exclude.

select * from (
select 'Order' seq from dual
union all select '1' from dual
union all select '2' from dual
union all select '10' from dual
union all select '12' from dual
union all select '140' from dual
)
where validate_conversion(seq as number) = 1
order by to_number(seq)

It's one of a few tools I'm using to make data loading life easier, and processing data in sets using SQL, not looping & context switching within PL/SQL.

The kicker, turns out this has been available since 12.2.

It turns out the usage of validate_conversion in PL/SQL will give the compilation warning PLW-06009. And so does the alternative to check if this returns null:to_date('z-z-2001' default null on conversion error, 'dd-mm-yyyy')

This query transposes all the string/value columns into rows, each denonimated by the new "REC" column.

Note, this query can only be executed by those with the APEX Administrator Role, and if you want to have it within a view that could be executed by other schemas, then select access on wwv_flows is needed with grant option.

Columns unpivoted into rows

By placing the query in a view, I can now make queries like this to find any substitution strings in any applications that have date references.

select * from apx_app_sub_strings
where val like 'APP_DATE%';

Pretty neat, well at least as far as what I was trying to do.

This query may break in future versions, as it's based on an underlying, undocumented view.
I also think that once upon a time, there were fewer pairs.

Update: As the community quickly pointed out, I missed & forgot about a view already dedicated for such a task. I was too busy looking for a column number, when really I should have used APEX_APPLICATION_SUBSTITUTIONS.

I thought I'd see how 'they' solved the problem, and I was a little surprised to see a bunch of UNION statements.

Thursday, 16 January 2020

Just an observation made yesterday that I thougt was worth everone's consideration.

I created a classic report on a reference table with a touch over 1000 rows, with pagination set at 150 rows per page.

That’s all I had on the page.

The table had it's own set of lookups for a couple of columns, so I assigned two LOVs I already defined in Shared Components.

Classic Report LOV

Next minute, I had a simple reference page taking 10 seconds to load.

This is information from apex_workspace_activity_logs, showing results where no LOV was applied, two LOVs, and just one. I later found the number of rows shown in the pagination set also varied the result.

As I removed one of the LOVs, I quickly realised this was the problem.

I ran the page in debug mode, to see if something crazy was happening as it constructed the query.
When I used debug = YES, it pinned all the effort onto the one line item - but not the query itself.rows loop: 150 row(s)

Looking again using LEVEL9, I could see that every row took a bit of work, not just the query.
In fact each row had three debug line items referencing my LOV lookup SQL.

begin begin SELECT name display_value, id return_value
bulk collect into wwv_flow_utilities.g_display,wwv_flow_utilities.g_value
FROM my_secondary_ref
WHERE SYSDATE BETWEEN eff_start AND COALESCE(eff_end, SYSDATE)
ORDER by 1
; end;
end;

Debug chart

Apparently these are only melded into the SQL for IR/IG, not Classic Reports. Context switching kills the page instead - well, at least 149 extra executions of the one statement.

I ran another test so I could check v$sqlarea, and sure enough, there are far more executions of this lookup tha necessary - unnecessarily churning the CPU.

v$sql_area status

I’ve tended to embed these in my queries anyway, often as some form of scalar subqery.
This is a habit I started as a Oracle Forms developer, since Post-Query lookups made repeated network calls that just slowed the application down.

This makes me really wonder how much of our existing page generation time is spent on this work?
Even on small classic report regions. This all adds up.
If only there was a way I could find all occurences of LOVs used in classic reports... wait a minute! I can query the APEX dictionary! ;p

Wednesday, 15 January 2020

You've thought hard about your conference talk ideas; you then fleshed out your idea and worked hard on an abstract; and finally you plucked up the courage to make the submission to that big conference.

What next?

Keep momentum.

#physics

It really depends on the style of your talk, but in most cases, just keep whatever momentum you have (or had back in December), and work on it in some form every week.

Wait, abstracts closed. Public voting complete. Aren't I just waiting for a March notification before I bother starting?

No.

There are a few reasons you could be working on your talks each week. For instance:

Rome wasn't built in a day - it depends, but a reasonable talk should take at least 20 hours to develop, plus the time needed to gather the experience the talk represents, past, present, or future. Presentations left to the last minute can look & sound like it. Presentations started early will have time to cultivate, allow your ideas to progress as you write up progress so

Experience - even if your talk isn't accepted, you have gained the experience necessary for a topic you've considered worthy enough to talk about it. Maybe you've already gained the experience, but compiling your thoughts will help you gain a deeper understanding of the topic.Maybe you gathered a few important insights along the way.

Aim high - OK, maybe you end up getting a "sorry, not this time" email for the big conference. So what's happening locally? Do you have a regular meetup nearby? Is there another conference where this talk would fit well? Would the team at your current workplace benefit from listening to your talk for an hour? If you write it, people will come.

And as an extra tip

Transform your speaker notes into search engine fodder I need to do this more often. I've seen more organised folk post their notes on GitHub, or as some sort of blog post. As a developer, sometimes I stumble across a great set of slides, but yearn for a little more context or content. It will always be useful for somebody, won't hurt your SEO, and you'll thank yourself immediately, and again in 6 months time when you google your own post.

How am I tackling my own submissions?

Slow at first, it's been a busy summer, but now I'm back at work building things, I feel like getting back into regular time aside.

I've submitted four ideas, to help my chances of being accepted. Maybe one idea is really good, but every other Tom, Dick, and Henrietta has submitted some variation of that idea. Perhaps your secondary submission fits rather well instead, among all the rest?

And given the thoughts above, I could make progress on all these ideas, knowing that they'll be useful somewhere along the line. Our local user group always seem happy to have me yak on about APEX.

Anyhoo, these were the titles for my Kscope20 submissions.

Oracle Reports to AOP Case Study

Visualising APEX Performance Monitoring

Navigating APEX Version Upgrades

A Practical Guide to APEX Authorisation Schemes

I was involved in the public voting exercise, and I not only saw some similar submissions, but a whole bunch of submissions on ideas I've considered, had, or wished that I had. Even if only a third of those submissions ever get produced, there's going to be some amazing content out there.

My titles are a little utilitarian, and lack some awesome word play I saw when reviewing abstracts, but I'm pretty excited about the building the content.

What am I doing to prepare these? I haven't got to this yet, have I?

AOP - I strategically chose this topic as I'm learning this as part of business as usual at work. My aim is to present a cheat-sheet style session to help new AOP developers hit the ground running. All I need to do is show up to work each day to prepare, though I need bed down the session structure.

APEX Performance monitoring - I've been writing charts & reports on these log tables for years, and I have the confidence I can piece together this presentation at will. I've offered different formats, so it may even be hands-on.

Version upgrades - I've done a few of these over time, and I've been making notes. I need to start playing on an empty canvas to help cultivate exactly how it will play out.And by this I mean probably both a simple text file that slowly fleshes out a list of key items I want to cover, and some sort of scratching/drawing the represents the story of my talk.

Authorisation Schemes - this one's been a real slow burn, something I've been wanting to do for a long time, but I hope to turn this into more than just a presentation. This is what I've been hinting about in this sites left menu bar, some form of publication.After piecing together a fairly clear breakdown of content, I may have procrastinated a little.

Regardless of where my experience is coming from to formulate the presentation, I typically start with a simple text file, flesh about my ideas, reorder as necessary, then start my slides.

Once the slides are done, I tend to go through them a few times, with a text file ready to note down simple modifications I want to make, without disrupting my flow too much.

Rinse and repeat, until your practice in front of a mirror/camera/friend comes out smooth, and on time.

Don't worry if you get nervous. We all get nervous.

PS - some of us have been living under metaphorical rocks, so if you've missed a bunch of posts by Martin Widlake on presenting, I recommend you check them out.