Add a “Who’s Online Now” box to your APEX app

Something to file under “pointless fun”…

Allow your users to feel like they’re part of a community by letting them know who else is using the app at the same time.

1. Open APEX Application Builder, and open the page you want to add this to.
2. Click the Create Region icon.
3. Choose Report, then SQL Report.
4. Enter a title, e.g. “Who’s Online Now”. Click Next.
5. Copy the following for the SQL Query (sorry about the poor formatting):

   SELECT apex_user || ' (' || minutes_ago
          || DECODE(minutes_ago,1,' min ago)',' mins ago)')
   FROM (
     SELECT DISTINCT
            apex_user,
            FIRST_VALUE(TRUNC(seconds_ago/60))
            OVER (PARTITION BY apex_user ORDER BY seconds_ago)
            AS minutes_ago
     FROM apex_workspace_activity_log
     WHERE application_id = :APP_ID
     AND seconds_ago < 3600
     AND apex_user NOT IN (:APP_USER, 'nobody')
     ORDER BY 2);
   

6. Click Next. Click Create Region.

You can customise the region as much as you like. For example, I use:

• Template (under Region Definition) = “Sidebar Region”
• Report Template (under Report Attributes) = “template: 16. One Column Unordered List”
• Pagination Scheme = “- No Pagination Selected -“
• Headings Type = “None”

The query is based on the APEX view apex_workspace_activity_log which is supplied with the product. It looks at all session activity within the last hour by users other than the current user, and returns a list showing their most recent activity.

The only slight problem is that it doesn’t detect when someone logs out – they’ll stay in the report for up to an hour.

Faster APEX using mod_expires

This is a followup on my earlier series on setting up APEX on Linux. In it I described how I used Apache web server in front of APEX, instead of accessing APEX directly via OWA (this was so that I could use the same port to serve ordinary web pages and files via HTTP).

A consequence of that set up is that all my APEX pages are a little slow to load up because none of the images are being cached on the client. To solve this I added the following lines to my httpd.conf:

<LocationMatch /i>
   ExpiresActive on
   ExpiresDefault "access plus 1 month"
</LocationMatch>

<LocationMatch /apex>
   ExpiresActive on
   ExpiresDefault "access"
   ExpiresByType image/gif "access plus 1 month"
   ExpiresByType image/jpeg "access plus 1 month"
   ExpiresByType image/bmp "access plus 1 month"
</LocationMatch>

My location “/i” points to the images used by APEX. Since there are only images in there I’ve told it to expire everything (ExpiresDefault) after a month.

The location “/apex” is for the actual APEX pages, including APEX application images which are not in /i/. I’ve set the default to “access” (in other words, don’t cache them), but added exceptions for the various image types in use to cache for a month.

Which APEX am I logging into?

I’ve got two computers at home both running APEX, and when I log into APEX the page looks exactly the same – except for the IP address in the URL, which is just slightly different. If you’re forgetful like me it’s easy to confuse which machine I’m logged in to. Here’s a tip:

Login to apex_admin on each machine in turn and click Manage Service, then Messages. Then select a Custom Message for the Login Message and/or a System Message, and enter the name of the machine, or some other message that will remind you which machine it is. The message will be shown whenever you see the login screen, and when you login to APEX.

If you find the text isn’t visible enough, you can insert HTML into the message, e.g. <B>Development</B>.

APEX 3.0 via Embedded PL/SQL Gateway

I managed to get APEX 3.0 working on Oracle 10.2.0.1 using the embedded PL/SQL gateway (i.e. without installing Apache), contrary to the advice given here. I used apex_epg_config instead of apex_epg_config11. For it to work, however, I had to remove some security on XDB so it’s not a recommended option. This involved modifying my xdbconfig.xml to allow anonymous access to XDB using this:

DECLARE
  configxml SYS.XMLType;
BEGIN
  SELECT INSERTCHILDXML(xdburitype('/xdbconfig.xml').getXML(),
         '/xdbconfig/sysconfig/protocolconfig/httpconfig',
         'allow-repository-anonymous-access',
         XMLType('true'),
         'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
  INTO configxml FROM DUAL;
  DBMS_XDB.cfg_update(configxml);
END;
/

Now for some general tips:

Tip #1. List Your DAD Authorizations

As described so well in the excellent article DBMS_EPG – The Embedded PL/SQL Gateway in Oracle 10g Database Release 2, you can authorize and deauthorize a DAD using the DBMS_EPG package. It should be noted that dropping a DAD will not remove the authorizations as well, so you need to deauthorize it as well. To find out what authorizations have been made on your database, you can query the DBA_EPG_DAD_AUTHORIZATION view, e.g.:

SQL> select * from dba_epg_dad_authorization;

DAD_NAME USERNAME
-------- ---------
APEX     ANONYMOUS

1 row selected.

There’s also a USER_EPG_DAD_AUTHORIZATION view which shows the DAD_NAME for authorized DADs for the current user.

Tip #2. List Your DADS

You can find out what DADs have been set up on your instance using the DBMS_EPG package. There is a bug, however, that causes dbms_epg.get_all_dad_attributes to return nothing, so this script gets each attribute separately with dbms_epg.get_dad_attribute. The script lists all the DADS, their mappings and their attributes.

set serveroutput on

PROMPT list all dads
declare
  blank       dbms_epg.varchar2_table;
  dad_names   dbms_epg.varchar2_table;
  paths       dbms_epg.varchar2_table;
  procedure show_dad_attribute(dad in varchar2, attr in varchar2) is
    val varchar2(4000);
  begin
    val := dbms_epg.get_dad_attribute(dad, attr);
    if val is not null then
      dbms_output.put_line('... ' || attr || '=' || val);
    end if;
  end;
begin
  dbms_epg.get_dad_list(dad_names);
  if dad_names.count > 0 then
    for i in dad_names.first..dad_names.last loop
      dbms_output.put_line(dad_names(i));
      paths := blank;
      dbms_epg.get_all_dad_mappings(dad_names(i), paths);
      if paths.count > 0 then
        for j in paths.first..paths.last loop
          dbms_output.put_line('... path=' || paths(j));
        end loop;
      else
        dbms_output.put_line('... No mappings found');
      end if;
      show_dad_attribute(dad_names(i),'after-procedure');
      show_dad_attribute(dad_names(i),'always-describe-procedure');
      show_dad_attribute(dad_names(i),'authentication-mode');
      show_dad_attribute(dad_names(i),'before-procedure');
      show_dad_attribute(dad_names(i),'bind-bucket-lengths');
      show_dad_attribute(dad_names(i),'bind-bucket-widths');
      show_dad_attribute(dad_names(i),'cgi-environment-list');
      show_dad_attribute(dad_names(i),'compatibility-mode');
      show_dad_attribute(dad_names(i),'database-username');
      show_dad_attribute(dad_names(i),'default-page');
      show_dad_attribute(dad_names(i),'document-path');
      show_dad_attribute(dad_names(i),'document-procedure');
      show_dad_attribute(dad_names(i),'document-table-name');
      show_dad_attribute(dad_names(i),'error-style');
      show_dad_attribute(dad_names(i),'exclusion-list');
      show_dad_attribute(dad_names(i),'fetch-buffer-size');
      show_dad_attribute(dad_names(i),'input-filter-enable');
      show_dad_attribute(dad_names(i),'info-logging');
      show_dad_attribute(dad_names(i),'owa-debug-enable');
      show_dad_attribute(dad_names(i),'max-requests-per-session');
      show_dad_attribute(dad_names(i),'nls-language');
      show_dad_attribute(dad_names(i),'path-alias');
      show_dad_attribute(dad_names(i),'path-alias-procedure');
      show_dad_attribute(dad_names(i),'request-validation-function');
      show_dad_attribute(dad_names(i),'session-cookie-name');
      show_dad_attribute(dad_names(i),'session-state-management');
      show_dad_attribute(dad_names(i),'transfer-mode');
      show_dad_attribute(dad_names(i),'upload-as-long-raw');
    end loop;
  else
    dbms_output.put_line('No dads found');
  end if;
end;
/

When the above script is run, you’ll get something like this:

APEX
... path=/apex/*
... authentication-mode=Basic
... database-username=ANONYMOUS
... default-page=apex
... document-path=docs
... document-procedure=wwv_flow_file_mgr.process_download
... document-table-name=wwv_flow_file_objects$
... nls-language=american_america.al32utf8
... request-validation-function=wwv_flow_epg_include_modules.authorize