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

APEX Tip: Page Auto Refresh

This tip provides your users with the option of choosing a refresh interval for the page. The following steps were tested with APEX version 2.2 but should work on earlier versions of APEX or HTMLDB.

1. Create an Application Item to store the current refresh interval (e.g. F100_REFRESH_INTERVAL)
2. Create an Application Computation to initialise it (e.g. F100_REFRESH_INTERVAL_COMP):
   • Computation Item = F100_REFRESH_INTERVAL
   • Computation Point = On New Instance (e.g. On Login)
   • Computation Type = Static Assignment
   • Computation = (default number of seconds, e.g. 60)
3. Create a static List of Values (e.g. REFRESH_INTERVAL):

Display – Return
1 sec – 1
5 sec – 5
10 sec – 10
30 sec – 30
1 min – 60
5 min – 300
(you can change this list how you like, as long as the return values are positive integers)
(it is probably not a good idea to provide zero seconds as an option, as this will cause the page to continually refresh without giving the user much chance to intervene)

Now, for each page you wish to have auto-refreshed, edit the Page Properties:

• HTML Header = <meta http-equiv="refresh" content="&F100_REFRESH_INTERVAL.">

Somewhere in your application you will want to provide the user with a way of modifying the refresh rate. I prefer to provide this on the same page that is auto-refreshed.
Create an Item (e.g. P1_REFRESH):

• Display As = Select List with Submit
• Region = (any region, I prefer the Breadcrumb region if one exists)
• Label = Refresh
• Source Used = Always, replacing any existing value in session state
• Source Type = Item (application or page item name)
• Maintain session state = Per session
• Source value or expression = F100_REFRESH_INTERVAL
• List of Values definition = REFRESH_INTERVAL

I also like to show the date/time when the page was last refreshed. To do this, I just add a display-only text item to the page with:

• Source Used = Always, replacing any existing value in session state
• Source Type = PL/SQL Expression or Function
• Maintain session state = Per session
• Source value or expression = TO_CHAR(SYSDATE,'Dy DD Mon HH24:MI:SS')

Now, the page will be auto-refreshed according to the default interval, and the user can change the refresh interval using the select list.

You can add the item and the auto-refresh header to any page you like. If you need a different refresh interval on different pages, you will need to create additional Application Items, along with their own Application Computations to initialise them.

If the user hits the “Stop” button in IE, the page seems to stop auto-refreshing. I don’t know if this feature works the same in other browsers, however.