Wednesday, March 29, 2006

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.

Sunday, March 19, 2006

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

Thursday, March 16, 2006

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.