Tips and wisdom from 15 years of DBA experience

Monday, August 10, 2009

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);

3 comments:

royski said...

Hi...what is your exact version of 10g? :) was your installation of 10g the default?

Roy

Zafer said...

10.2.0.3 is the minimal patchset level for the new APEX 4.0.1 (unless you are using XE).

Craig Glendenning said...

Yeah, all my testing was done on 10.2.0.4 with the latest CPU applied...any earlier version of 10g and you have bigger problems : )

Followers