Thursday, March 22, 2007

Small tool to escape HTML text for postings

Hi guys, when I post answers in the forum, I often have to copy / paste HTML sequences, which will have to be escaped, else they mess up the posting itself.

For example, you cannot use
<a href="">link</a> 
directly in a posting, you would have to escape it first to:
&lt;a href="">link&lt;/a>. 
This is accomplished by replacing the opening bracket < with the html escape sequence &lt;

You can find the tool here: http://apex.oracle.com/pls/otn/f?p=daust_demos:tools_escape_text

I use it quite often.

Enjoy,
~Dietmar.

Wednesday, March 21, 2007

More photos from Lavinia

Well, the proud daddy has to show off some more pictures from his beloved daughter, Lavina Alessia Marie :-)

http://www.flickr.com/photos/13993868@N00/sets/72157600007585103/detail/

She is already 7 months old and a real joy for us.

Regards,
~Dietmar.

Apex: Nested reports

Have you ever wondered how to display additional detail records together with the master records in a single report?

For example, in a recent project I had to display a list of orders. Together with the order details (who, when) I had to display the order items in the same row.

The result should look like this:


How can this be done?

The "trick" is to create a stored function which generates the HTML for the detail records so that they can be displayed with the master records.

Here is a step by step example based on the tables EMP and DEPT, what else ;).

1. Create the package and stored function to generate the html for all employees in a specific department:

CREATE OR REPLACE PACKAGE emp_pck
AS
/******************************************************************************
NAME: EMP_PCK
PURPOSE:

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 16.03.2007 1. Created this package.
******************************************************************************/
FUNCTION get_emps_inline_f (p_deptno NUMBER)
RETURN VARCHAR2;
END emp_pck;
/


CREATE OR REPLACE PACKAGE BODY emp_pck
AS
FUNCTION get_emps_inline_f (p_deptno NUMBER)
RETURN VARCHAR2
IS
l_str VARCHAR2 (32767);
l_cnt NUMBER := 0;
BEGIN
l_str := '<table class="inlineTable">';
l_str :=
l_str
|| '<tr><th>No.</th><th>Name</th><th>Job</th><th>Salary</th></tr>';

FOR cur IN (SELECT empno, ename, job, sal
FROM emp
WHERE deptno = p_deptno
ORDER BY ename)
LOOP
l_str := l_str || '<tr>';
l_str :=
l_str
|| '<td>'
|| cur.empno
|| '</td><td>'
|| cur.ename
|| '</td><td>'
|| cur.job
|| '</td><td>'
|| cur.sal
|| '</td>';
l_str := l_str || '</tr>';
l_cnt := l_cnt + 1;
END LOOP;

l_str := l_str || '</table>';

IF l_cnt = 0
THEN
RETURN '';
ELSE
RETURN l_str;
END IF;
END;
END emp_pck;
/
2. Create the styles in the page header, in order to format the inline table:
<style type="text/css">
<!--
.inlineTable{border:1px solid #cfe0f1;border-collapse:collapse;width:100%;}
.inlineTable th{color:#336699;border:1px solid #cfe0f1;padding:2px;}
.inlineTable td{border:1px solid #cfe0f1;border-left:none;border-top:none;padding:2px;}
-->
</style>
3. Create the query in a report region
SELECT dept.*, emp_pck.get_emps_inline_f (deptno) employees
FROM dept

Here you can see the final result:


The forum thread can be found here and the online demo is located here.

Regards,
~Dietmar.

Apex 3.0 has been released!

It is already a few days ago, but Apex 3.0 has been released, on the 16th of March.



The quality is once again impressive, especially with regard to the upgrade from an existing 2.2.1 version. The whole process took only roughly 40 minutes, including the configuration. Very slick!



Here is a list of the new features.



Especially the new PDF printing facility. Unfortunately, in order to use the interesting PDF capabilities like Report Queries to create more complex layouts, you would need a full license for the BI publisher. This is a bummer.



Using FOP or other open frameworks it seems like it will only be possible to use the data from the current region, but not from other items in session state.



I will try to cook something up using Jasper Reports, I have already built a secured gateway to calling a complex Jasper Report that runs in a Tomcat.

Perhaps I can make use of the XML that is generated by Apex. The first impression is good.



I will keep you posted.



Regards,

~Dietmar.

Wednesday, March 14, 2007

Apex Evangelists goes live!

Great news! Today the website of the Apex Evangelists has been launched.



The company was founded by Dimitri Gielis und John Scott. Their endavour is supported by other members like Patrick Wolf and Denes Kubicek and myself, all of us are well known in the Apex community.

The idea behind Apex Evangelists is that we will use our knowledge and experience of Application Express to provide a range of services, some of which are listed here -

* Application Website Development (plus of course hosting)
* Training Coaching (onsite and in our European Training Days)
* Application and Database Migrations
* Support Services

Our primary goal is to be able to provide these services to the European market and to generally evangelise (hence the name!) about how beneficial using APEX can be to European companies.

Interesting times ahead :).

Regards,
~Dietmar.

Sunday, March 11, 2007

Symbolic and hard links on Windows

This post is not directly related to Oracle, but it often bothered me, that you cannot create hard links and symbolic links on the windows platform. For example, you could move log files or data files to a different disk or even network drive and the application would not be able to notice it.

At least this is what I believed so far ;).

There is an excellent article explaining the way, the folder links work and how to create hard and symlinks on windows.

Symbolic links were introduced in Windows 2000, more specifically with NTFS 5.0. They are called "junction points".

In this article there are different tools mentioned, I have chosen the command line tool Junction by Mark Russovich, there is more information here.

Using junction.exe, creating a symlink is really easy:

C:\TEMP\links
Datenträger in Laufwerk C: ist VAIO
Volumeseriennummer: 54A5-8EF0

Verzeichnis von C:\TEMP\links

11.03.2007 12:42 .
11.03.2007 12:42 ..
01.11.2006 13:06 158.520 junction.exe
1 Datei(en) 158.520 Bytes
2 Verzeichnis(se), 7.896.629.248 Bytes frei

C:\TEMP\links

Junction v1.04 - Windows junction creator and reparse point viewer
Copyright (C) 2000-2005 Mark Russinovich
Systems Internals - http://www.sysinternals.com

The first usage is for displaying reparse point information, and the
second usage is for creating or deleting a NTFS junction point:

usage: junction [-s] [-q] or directory
-q Don't print error messages (quiet)

-s Recurse subdirectories

usage: junction [-d] directory [ target
-d Delete the specified junction
example: junction d:\link c:\winnt


C:\TEMP\links link2 c:\temp\link2

Junction v1.04 - Windows junction creator and reparse point viewer
Copyright (C) 2000-2005 Mark Russinovich
Systems Internals - http://www.sysinternals.com

Created: C:\TEMP\links\link2
Targetted at: c:\temp\link2

C:\TEMP\links
Datenträger in Laufwerk C: ist VAIO
Volumeseriennummer: 54A5-8EF0

Verzeichnis von C:\TEMP\links

11.03.2007 12:47 .
11.03.2007 12:47 ..
01.11.2006 13:06 158.520 junction.exe
11.03.2007 12:47 link2
1 Datei(en) 158.520 Bytes
3 Verzeichnis(se), 7.896.629.248 Bytes frei

C:\TEMP\links link2
Datenträger in Laufwerk C: ist VAIO
Volumeseriennummer: 54A5-8EF0

Verzeichnis von C:\TEMP\links\link2

11.03.2007 12:47 .
11.03.2007 12:47 ..
09.03.2007 20:54 49 debug.txt
1 Datei(en) 49 Bytes
2 Verzeichnis(se), 7.896.629.248 Bytes frei

C:\TEMP\links


!!! CAUTION !!!

There is a tricky thing to be aware of. On *nix (Linux, Unix, etc.) systems, when you delete a symlink, only the link is gone, but not not the files / directories the link points to.

Under Windows, these junction points work differently. If you use the Windows Explorer to delete the link, all files and subdirectories of the linked folder are gone, too. But only at the time, you empty the recycle bin. So be aware of that behaviour.

Always make sure, that you use the tool again, to remove the junction point, then it works fine.


C:\TEMP\links -d link2

Junction v1.04 - Windows junction creator and reparse point viewer
Copyright (C) 2000-2005 Mark Russinovich
Systems Internals - http://www.sysinternals.com

Deleted link2.

C:\TEMP\links c:\temp\link2
Datenträger in Laufwerk C: ist VAIO
Volumeseriennummer: 54A5-8EF0

Verzeichnis von c:\temp\link2

11.03.2007 12:47 .
11.03.2007 12:47 ..
09.03.2007 20:54 49 debug.txt
1 Datei(en) 49 Bytes
2 Verzeichnis(se), 7.897.178.112 Bytes frei


If you use the functionality carefully, it is a really good thing.

Regards,
~Dietmar.

Saturday, March 10, 2007

Apex Community Page updated

Last Wednesday, the community page on Application Express was updated.

You can find there links to

* APEX Community
* Special Interest Groups
* Consulting Companies
* Hosting Companies
* BLOGs
* Commmunity How-Tos and Articles

Regards,
~Dietmar.

Denes Kubicek - ApEx Demo

Many of us are already familiar with the excellent work of Denes Kubicek in the Apex forum. Especially his API to the cool xml charts ( XML/SWF Charts).

You can find his demo applications here.
The xml charts API can be found here.

Enjoy,
~Dietmar.