Category: APEX

APEX: Who is doing what?

Ever wanted to know what a user in doing in Oracle APEX? Which pages he visited in his current session? What the values of his session variables are at this very moment? It’s possible! (I’m writing this while using APEX 5.1, but the info is also available in APEX 04.02 so the path to the option in APEX Development might differ somewhat from your APEX version.)

Where are Dashboards?

In the first place you can find a lot of information in the Oracle APEX Development environment. Go to “Monitor Activity” and then select “Active Sessions”.

Or open de “Dashboards”, Click on “Activities” and click on the Arrow right of “Recent Logins”.

The data shown in these APEX pages is accessible thru views that exist in the APEX_nnnnnn schema’s. Be aware that some of these views have an implied selection of a so-called security_group_id which is retrieved at runtime, so trying to see the data from SqlDeveloper will only work

  1. if you have the proper authorisations
  2. if you are logged-in as the user of the parsing schema.

If the parsing schema user has sufficient privileges you can ofcource access the data from APEX SQL Workshop when logging in to the proper workspace.

And the views you should look for are:

  • apex_050100.apex_workspace_access_log
    Shows all the login info of sessions that are still available in the running APEX-engine. These might not be all active sessions! Oracle APEX has it’s own rules for cleaning up sessions, and ofcourse you can use time-out and possible your own cleaning-up.
    This view does not contain an APEX session id.
  • apex_050100.apex_workspace_sessions
    Shows the current sessions and contains an APEX session id, which is important to be able to see the session variables. It also contains the session’s NLS settings like the territory.
  • apex_050100.wwv_flow_data_view
    This is the view where you can see in detail all the session variables of each session still available in the APEX runtime. For sake of security you should add a WHERE condition on the security_group_id, for instance  by limiting the rows to those where security_group_id = v(‘FLOW_SECURITY_GROUP_ID’);

These views should give you sufficient data to link it to your own user tables so you can add application info like full names, email and phonenumber in case you have to contact a certain user.

Regarding the implementation: Since the views you are referencing are APEX-owned views, you should consider building your own application-owned views on top of these APEX-views and only using those application-owned views. Then in case you have to upgrade to a new APEX version you only have to change these three application-owned views.

APEX Calender report

Another powerfull item that can be used to fight the data pile: presentation by a calendar. Oracle APEX supplies us with a simple wizzard which makes it easy to present date related data.

Because I am still learning and always trying just a bit different than standard, I found something to be aware of.

Case: An application for Helpdesk Tickets (see “Beginning Oracle Application Express”). I already have the “Form on a Report” page pair which lists the tickets and links to the Ticket details update page. Now I want to create a page with a calendar where the data links to that existing details page.

Action: Create a Calendar report page and let the link of the data point to the already existing detail page.

The link being the ticket_id ofcourse. Now APEX generates a link filling the #PRIMARY_KEY_VALUE#, but it should fill the “TICKET_ID#.

Interactive reports

Interactive reports are very powerfull tools of Oracle APEX, not only for the developer but also for the user. After the report has been developed you still can filter, sort, hide columns, add aggregates, highlights etc. In the current version APEX 4.1 there are however some limitations that I can’t quite understand. To mention some:

  • A with a filter on a date you cannot specify a time;
  • filtering on alfanumeric fields does not allow using the relations greater than (>), less than (<), greater-or-equal (>=), less-or-equal (<=) and between;
  • aggregate functions like min and max are not possible on alfanumeric fields.

Ofcourse there is the fact that printing of a shown interactive report is not supported. Luckily for us developers 😉

I am still learning to use this great development tool to it’s full potential so I might add some – to me – strange things later.

Herman Mol is powered by WordPress.  Staypressed theme by Themocracy