Oracle Apex on 10g using the PL/SQL Gateway? Yes!

Oracle Application Express is a very solid tool for rapid web application development that does not require knowledge of a programming language or HTML. It comes free with the Oracle database, and handles session tracking, security, database access etc. very simply and intuitively. For example, I had a form with pick lists, and I wanted to create a modify-able version of that form. I created an updateable report so that for each row you can click a little modify icon to the left of the row, which would bring up the form. The form had pick lists that were populated from a lookup table, and I was impressed that Oracle "knew" that I did not want the values from the lookup table but from the table that contained the row I just clicked on to modify. Nice.

Below are instructions for installing Apex 3.2 on Oracle 10g using the Embedded PL/SQL Gateway (EPG) and the http server that comes with the XML DB. This is not an "officially" supported configuration by Oracle. They do support APEX on 10g using an external web server. I have at least 5 forms used in production now that confirm that 10g using the EPG works, and I am going to show you exactly what you need to do to make it work. The Oracle documentation lacks some crucial elements, and the diagnostic information produced by the XML DB http server is well...er, non-existent as far as I can discern. If anyone knows how to get the equivalent of an apache http log from the XML DB http server, please post a comment to share your wisdom!


APEX installation instructions:

First, download Oracle Application Express from Oracle OTN then unzip Oracle APEX and begin the installation process as follows:

$ unzip apex_3.2.zip
$ cd apex
$ sqlplus "/ as sysdba"
SQL> @apexins sysaux sysaux temp /i/
SQL> @apxchpwd
(jot down the password that you chose)
SQL> @apex_epg_config /stuff

VERY IMPORTANT!! the /stuff directory above MUST be the directory that is one level above where the "apex" directory is located that you unzipped during installation. For example, if your apex directory is in /home/oracle/stuff, then choose /home/oracle/stuff. DO NOT choose /home/oracle/stuff/apex. Just a common stumbling block...

$ sqlplus "/ as sysdba"
SQL> @apxldimg.sql /stuff
(same dir you listed in previous step)
SQL> @apxxepwd.sql admin
SQL> EXEC DBMS_XDB.SETHTTPPORT(0);
SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;
SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
SQL> ALTER USER APEX_030200 ACCOUNT UNLOCK;
SQL> ALTER USER FLOWS_FILES ACCOUNT UNLOCK;
SQL> EXEC DBMS_XDB.SETHTTPPORT(8080);

This bit of code was off otn forums, and is necessary to prevent the annoying pop-up at the browser for user anonymous 5 times before letting you get to the web page authentication.

SET SERVEROUTPUT ON
DECLARE
  l_configxml XMLTYPE;
  l_value     VARCHAR2(5) := 'true'; -- (true/false)
BEGIN
  l_configxml := DBMS_XDB.cfg_get();
 
  IF l_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN
    -- Add missing element.
    SELECT insertChildXML
           (
             l_configxml,
             '/xdbconfig/sysconfig/protocolconfig/httpconfig',
             'allow-repository-anonymous-access',
             XMLType('' ||
                      l_value ||
                     ''),
             'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
           )
    INTO   l_configxml
    FROM   dual;
 
    DBMS_OUTPUT.put_line('Element inserted.');
  ELSE
    -- Update existing element.
    SELECT updateXML
           (
             DBMS_XDB.cfg_get(),
             '/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access/text()',
             l_value,
             'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
           )
    INTO   l_configxml
    FROM   dual;
 
    DBMS_OUTPUT.put_line('Element updated.');
  END IF;
 
  DBMS_XDB.cfg_update(l_configxml);
  DBMS_XDB.cfg_refresh;
END;
/ 

If you have problems logging in, or with images not displaying correctly:

SQL> select * from  dba_epg_dad_authorization;

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

If the query above returns no rows or different output, do this:

begin
  dbms_epg.authorize_dad (
  dad_name => 'APEX',
  user => 'ANONYMOUS');
end;
/

Re-check the dba_epg_dad_authorization query:

SQL> select * from  dba_epg_dad_authorization;

If you are still having problems, check out the various dad attributes:

set serveroutput on size 1000000
DECLARE
  l_dadNames DBMS_EPG.VARCHAR2_TABLE;
  l_attrNames DBMS_EPG.VARCHAR2_TABLE;
  l_attrValues DBMS_EPG.VARCHAR2_TABLE;
BEGIN
  DBMS_EPG.GET_DAD_LIST(l_dadNames);
  FOR d IN 1..l_dadNames.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(CHR(10)||l_dadNames(d));
    DBMS_EPG.GET_ALL_DAD_ATTRIBUTES(l_dadNames(d),l_attrNames,l_attrValues);
    FOR a IN 1..l_attrValues.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('- '||RPAD(l_attrNames(a),25)||' : '||l_attrValues(a));
    END LOOP;
    DBMS_EPG.GET_ALL_DAD_MAPPINGS(l_dadNames(d),l_attrValues);
    FOR a IN 1..l_attrValues.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('- '||RPAD('mapping',25)||' : '||l_attrValues(a));
    END LOOP;
    FOR a IN ( SELECT username FROM dba_epg_dad_authorization WHERE dad_name = l_dadNames(d) )
    LOOP
      DBMS_OUTPUT.PUT_LINE('- '||RPAD('authorized',25)||' : '||a.username);
    END LOOP;
  END LOOP;
END;
/

If nothing from this output helps in your troubleshooting efforts, you can turn on the highest level of logging (debugging) which will write to udump in theory, but I have not seen anything show up there...:

SQL> execute dbms_epg.set_global_attribute('log-level', 7);