Apex: Workaround for SVG chart hyperlink bug with Firefox 1.5

I have come across a nasty problem using SVG charts in Apex.

In order to create drill-down charts with Oracle Application Express you provide a SQL statement that will return the data to display a SVG chart's series. This SQL statement must be provided using this syntax:

SELECT link, label, value FROM...

For example:

select
'f?p=141:6:'||:app_session||'::::F141_P6_EMPNO:'||empno link,
ename,
sal
from emp

A simple chart might look like this:


When clicking on the link you would want to see a detail page in
the same browser window:

While this works well in Internet Explorer 6, Firefox 1.0.7 and Mozilla, what you see in Firefox 1.5 is:


This is due to a bug in Firefox 1.5 ( https://bugzilla.mozilla.org/show_bug.cgi?id=300868 )
. Unfortunately does Firefox ignore the target="_top" attribute for the xlink tag:

<a xlink:href="newdoc.html" target="_top">

The suggested workaround is :

xlink:href="javascript:window.top.location.href='newdoc.html'"

Using this fix to implement a workaround for Apex means changing a query like this:

SELECT *
FROM (SELECT   'f?p=&APP_ID.:14:' || :app_session || '::::P14_EMPNO:' || empno || ':' LINK
      , ename label, sal VALUE
   FROM HTMLDB_SAMPLES.EMP
  WHERE sal IS NOT NULL
ORDER BY ename)

to a query like this:

SELECT *
FROM (SELECT   'javascript:window.top.location.href=''f?p=&APP_ID.:14:' || :app_session
        || '::::P14_EMPNO:' || empno || ':''' LINK
      , ename label, sal VALUE
   FROM HTMLDB_SAMPLES.EMP
  WHERE sal IS NOT NULL
ORDER BY ename)

This works fine for all of the above mentioned browsers.

~Dietmar.

XE: Oracle Home Selector

When you installed Oracle on a Windows machine (previous to version 10g), it would also install the Oracle Home Selector:


This way you could switch between different Oracle versions installed on the same machine. It would set the environment appropriately (including the modification of the PATH variable).

With the release of Oracle 10g (and above) the Home Selector has been moved into the Oracle Universal Installer, but it has become a quite "hidden" feature.

Oracle Express Edition (XE) doesn't come with a Home Selector at all.

There is a freeware tool called "Oracle Locator Express" from DbMotiveTools, which does an excellent job here.

Once you download and double-click on it, you will find an additional icon in your taskbar.
Right-click on it and it will show you all Oracle_Homes it can find on this machine. On my machine it recognized all homes ranging from version 8.0 up until 10g.


It certainly found XE and the XE client as well.
Cool stuff!

~Dietmar

Where are the /images of Application Express in OracleXE?

In a standalone Apex installation, the required images, css and javascript files are accessible via the virtual path /i.

This virtual path mapping looks something like this in your marvel.conf (or dads.conf) file:

Alias /i/ /opt/oracle/oracle/product/10.2.0/htmldb/images/

But where are the images in OracleXE?

When you look at http://127.0.0.1:8080/i you will see that the images are available, but they cannot be found in the filesystem.


In OracleXE these resources are stored directly in the database, using the Oracle XMLDB feature.
You can access the resources and modify them directly by using the WebDAV feature of the XMLDB.

(1) With Internet Explorer, just go to File > Open > http://127.0.0.1:8080/i (check "open as webfolder"):

(2) You will be prompted for user credentials, enter username and password for the database user SYSTEM:
(3) After that you can access the files via WebDAV directly, using Drag&Drop:


Update (19.03.2006)

If you don't have an Internet Explorer for connecting via WebDAV available, you can also use another WebDAV client like sitecopy or cadaver ( http://www.webdav.org/projects/ ).

Or you can simply use a plain FTP client to modify the files:

(1) Enable FTP access to the database:

www:oraclexe[XE]> sqlplus system@xe
SQL*Plus: Release 10.2.0.1.0 - on Sun Mar 19 22:20:36 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Enter password:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0

SQL> exec dbms_xdb.setftpport('2100');

PL/SQL procedure successfully completed.

SQL> alter system register;

System altered.

SQL> select dbms_xdb.getftpport as "FTP-Port" from dual;

FTP-Port
----------
    2100

SQL> exit

(2) Modify the files via FTP:

www:oraclexe[XE]> ftp localhost 2100
Connected to localhost.localdomain.
220- www
Unauthorised use of this FTP server is prohibited and may 
be subject to civil and criminal prosecution.
220 www FTP Server (Oracle XML DB/Oracle Database) ready.
Name (localhost:oraclexe): system
331 pass required for SYSTEM
Password:
230 SYSTEM logged in
Remote system type is Unix.
ftp> cd i/themes
250 CWD Command successful
ftp> dir
200 PORT Command successful
150 ASCII Data Connection
-rw-r--r--   1 SYS      oracle      3075 OCT 10 03:41 generic_list.gif
-rw-r--r--   1 SYS      oracle      1307 OCT 10 03:41 generic_nochild.gif
-rw-r--r--   1 SYS      oracle      1463 OCT 10 03:41 generic_open.gif
drw-r--r--   2 SYS      oracle         0 OCT 10 03:41 opal
drw-r--r--   2 SYS      oracle         0 OCT 10 03:41 otn
-rw-r--r--   1 SYS      oracle       216 OCT 10 03:41 rollup_minus_dgray.gif
-rw-r--r--   1 SYS      oracle       215 OCT 10 03:41 rollup_plus_dgray.gif
drw-r--r--   2 SYS      oracle         0 OCT 10 03:41 theme_1
drw-r--r--   2 SYS      oracle         0 OCT 10 03:41 theme_10
drw-r--r--   2 SYS      oracle         0 OCT 10 03:41 theme_11
drw-r--r--   2 SYS      oracle         0 OCT 10 03:41 theme_12
drw-r--r--   2 SYS      oracle         0 OCT 10 03:41 theme_2
drw-r--r--   2 SYS      oracle         0 OCT 10 03:41 theme_3
drw-r--r--   2 SYS      oracle         0 OCT 10 03:41 theme_4
drw-r--r--   2 SYS      oracle         0 OCT 10 03:41 theme_5
drw-r--r--   2 SYS      oracle         0 OCT 10 03:41 theme_6
drw-r--r--   2 SYS      oracle         0 OCT 10 03:41 theme_7
drw-r--r--   2 SYS      oracle         0 OCT 10 03:41 theme_8
drw-r--r--   2 SYS      oracle         0 OCT 10 03:41 theme_9
226 ASCII Transfer Complete
ftp> bye
221 QUIT Goodbye.
www:oraclexe[XE]>

(3) Disable FTP access for security:

www:oraclexe[XE]> sqlplus system@xe

SQL*Plus: Release 10.2.0.1.0 - on Sun Mar 19 22:24:28 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0

SQL> exec dbms_xdb.setftpport('0');

PL/SQL procedure successfully completed.

SQL> alter system register;

System altered.

SQL> exit

~Dietmar.

XE: Oracle XE is in production now!

Oracle Express Edition has been released on 02/24/2006 and is in production now.

You can download the software here: http://www.oracle.com/technology/software/products/database/xe/index.html

The downloads come in two flavours: A Western European Edition and an Universal Edition

• Oracle Database 10g Express Western European Edition - Single-byte LATIN1 database for Western European language storage, with the Database Homepage user interface in English only. The character set is WE8MSWIN1252.

• Oracle Database 10g Express Universal Edition - Multi-byte Unicode database for all language deployment, with the Database Homepage user interface available in the following languages: Brazilian Portuguese, Chinese (Simplified and Traditional), English, French, German, Italian, Japanese, Korean and Spanish. The character set is AL32UTF8.

There is also a separate download of a

• Oracle Database 10g Express Client
  Client software to be installed on client systems for remotely accessing Oracle Database 10g Express Edition.

But you can just as well connect to Oracle Express Edition just like any other Oracle Database using SQL*Net.

The downloads are available for both Windows and Linux x86 platforms. The software is not installed using the standard Oracle Universal Installer (OUI) but uses the native installers, Microsoft Installer packages and RPMs for Linux. Specific Debian packages will be released shortly.

~Dietmar.

XE: Changing the default http port

Oracle XE uses the embedded http listener that comes with the XML DB (XDB) to serve http requests. The default port for HTTP access is 8080.

It also supports the WebDAV protocol just as well as FTP.

You can determine the current configuration using the following commands when you connect to XE as the oracle user SYSTEM (or any other DBA):

C:\WINDOWS\system32>sqlplus system@xe

SQL*Plus: Release 10.1.0.2.0 - Production on Mi Jan 25 11:44:33 2006

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Beta

SQL> -- get current status
SQL> select dbms_xdb.gethttpport as "HTTP-Port"
            , dbms_xdb.getftpport as "FTP-Port" from dual;

HTTP-Port   FTP-Port
---------- ----------
     8080          0

You can change the http port and the ftp port to whatever you like (keep in mind that you need special privileges for ports < 1024 on Unix/Linux systems).

SQL> -- set http port and ftp port
SQL> begin
 2    dbms_xdb.sethttpport('80');
 3    dbms_xdb.setftpport('2100');
 4  end;
 5  /

PL/SQL procedure successfully completed.

SQL> select dbms_xdb.gethttpport as "HTTP-Port"
            , dbms_xdb.getftpport as "FTP-Port" from dual;

HTTP-Port   FTP-Port
---------- ----------
       80       2100

If you only want to use the database without allowing access via http or ftp then you can disable both:

SQL> -- disable http and ftp access
SQL> begin
 2    dbms_xdb.sethttpport('0');
 3    dbms_xdb.setftpport('0');
 4  end;
 5  /

PL/SQL procedure successfully completed.

SQL> -- get current status
SQL> select dbms_xdb.gethttpport as "HTTP-Port"
            , dbms_xdb.getftpport as "FTP-Port" from dual;

HTTP-Port   FTP-Port
---------- ----------
        0          0

~Dietmar

XE: Where in the World is Oracle XE Being Used?

Mark Townsend from Oracle XE Marketing invites us to join the Oracle XE group on http://www.frappr.com:

Oracle Database 10g Express Edition (Oracle XE) is being used all over the world. If you are using Oracle XE why not add yourself to the map - see the OracleXE group on frapper - http://www.frappr.com/oraclexe.
You can also find other people in your area using Oracle XE as well.

Simply add yourself to the map (no registration required) and join the community!



You can also check out the Frappr group for HTML DB users ( http://www.frappr.com/htmldbusers ) and see who is using it in the world.

~Dietmar.

I have started this BLOG to spread the word on the really cool web development framework HTML DB and the free Oracle database Oracle Express Edition (XE) .

Enjoy the ride!

~Dietmar.