Wednesday, December 26, 2007

New website released based on Apex and XE

A few days ago, I have relaunched my company website at www.opal-consulting.de . This is a major redesign regarding the layout and the underlying technology.

How else could I possibly build my own website if not using Oracle Application Express and Oracle XE? I guess it has come out pretty well and it looks nice.

For now it is only in German, some English pages might follow in the future.
Here are some screenshots of my site:





I think it is sometimes necessary to showcase some really nice looking app in order to get a customer to "buy into" the underlying technology. In the end, this is all what the end-user "sees".

We (as developers) all know that Apex is "just" another framework with a rendering engine like so many others written in Java, C##, Perl, PHP, ... , you name it.

You can easily change the complete layout and/or integrate any 3rd party javascript library or other extension like all the other web frameworks out there. It is really versatile and extensible.
It is just that so many people *only* think of the standard sample application and the little tiny apps they do in the workshops. They think you are completely tied to this look and feel. But you aren't!

Hopefully, the look and feel of my site will make some people think differently about Apex!!!


I hope you like it :), comments are welcome!

Regards,
~Dietmar.

Thursday, December 20, 2007

Apex: Advanced class in Germany announced

The following post is in German, since it is targeted towards the German speaking community in Europe.

Wir freuen uns, folgende Oracle Application Express (APEX) Schulung für Fortgeschrittene bekanntgeben zu können:

Oracle APEX: Fortgeschrittene Techniken aus der Praxis

Nutzen Sie das Wissen und die Erfahrung von

  • Patrick Wolf, langjähriger Oracle Veteran und Senior Solution Architect bei einer Wiener IT Firma, wurde erst kürzlich vom Oracle Magazin zum „Oracle APEX Developer of the year 2007“ ausgezeichnet. Er ist weiterhin ein Oracle ACE und Betreiber des bekannten Blogs Inside Oracle APEX

  • Dietmar Aust, erfahrener Oracle Consultant mit Spezialisierung auf Oracle Apex, aktiv in den OTN Foren zu Apex und Oracle XE, mit Präsentationen auf den letzten DOAG Veranstaltungen,

  • Denes Kubicek, langjährige Projekterfahrung in den Bereichen Oracle und ApEx mit multisite Applikationen und sehr bekannt im Oracle APEX OTN Forum für seine Beispielapplikation und

, um fortgeschrittene Techniken und Best Practice Vorgehensweisen aus vielen erfolgreichen Apex Projekten zu lernen.

Nutzen Sie diese Möglichkeit, Ihre eigenen Anforderungen mit diesen Experten zu diskutieren.

Wir freuen uns auf Sie !!!

Weitere Details zum Kurs sowie die Anmeldung finden Sie hier.

~Dietmar.

Thursday, September 20, 2007

Apex Roundtable Talk with Mike Hichwa

Last Tuesday I have met Michael Hichwa at the Apex Roundtable Talk in Stuttgart. He is a great guy who truly loves this product. I am really happy to have chosen Application Express as my primary focus.

Denes Kubicek, Patrick Wolf und Carsten Czarski joined in on the discussion, amongst other Oracle customers that use Application Express a lot.

Mike gave an interesting presentation on the upcoming features. You can see the presentation here. He was also very interested in the projects we did and the enhancements we would like to see.

Lots of interesting stuff will be coming, it is really exciting. The 3.1 release is expected in November, the 4.0 release some time in the first quarter of 2008.

What I am really looking forward is the gradual publishing of an official API for Apex. Then we will be able to programmatically create applications, pages, regions, basically everything. This is a major step forward. It does already work now but is not supported.

Also the Apex team will make the Application Builder more extensible. They will start with custom item types in 4.0. Learning from this they will add more possibilities to add custom region types and wizards in subsequent releases.

He was also demonstrating some of the new features of 4.0, especially the new query report region, which uses AJAX a lot. There is a video done by Carl Backstrom available which shows some of this functionality.

So, lots of great stuff is coming ahead.

Regards,
~Dietmar.

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.

Friday, January 26, 2007

Oracle: Changing the value for SYSDATE

In many applications you work with time sensitive data, e.g. a table of products that have valid_from and valid_to dates.

You might want to display the currently valid products at some point and thus compare the current date (SYSDATE) to the valid_from and valid_to date values.

Or you calculate some values based on the value of SYSDATE.

These parts of your application are difficult to test. Altering the system clock is generally not a good idea!!!

So, how can you do this? Usually you have to modify the test data or change the code for testing purposes. But this is cumbersome and you usually can only test parts of the program.

I have come across this post recently how to set a specific fixed return value when you call SYSDATE.

It is very simple, you can set any date as the current date for SYSDATE:

ALTER SYSTEM SET fixed_date = '2003-01-01-10:00:00';
can be reset with a simple
ALTER SYSTEM SET fixed_date = NONE;


Consider the following example:


HR@o102> conn hr/hr
Connected.
HR@o102> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;

NOW
-------------------
26.01.2007 22:18:56

HR@o102> create table products(id number, product varchar2(50), valid_from date, valid_to date);

Table created.

HR@o102> insert into products(id,product,valid_from,valid_to)
2 values (1, 'product1', to_date('01.01.2006','dd.mm.yyyy') , to_date('25.12.2007','dd.mm.yyyy'));

1 row created.

HR@o102> insert into products(id,product,valid_from,valid_to)
2 values (2, 'product2', to_date('01.01.2006','dd.mm.yyyy') , to_date('25.01.2007','dd.mm.yyyy'));

1 row created.

HR@o102> insert into products(id,product,valid_from,valid_to)
2 values (3, 'product3_new', to_date('31.01.2007','dd.mm.yyyy') , to_date('31.12.2007','dd.mm.yyyy'));

1 row created.

HR@o102> commit;

Commit complete.

HR@o102> create or replace view products_current_v as
2 select * from products where trunc(sysdate) between valid_from and valid_to;

View created.

HR@o102> select id, product from products_current_v;

ID PRODUCT
---------- --------------------------------------------------
1 product1

HR@o102> conn / as sysdba
Connected.
SYS@o102> alter system set fixed_date='2006-12-31-10:00:00';

System altered.

SYS@o102> conn / as sysdba
Connected.
SYS@o102> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;

NOW
-------------------
31.12.2006 10:00:00

SYS@o102> select id, product from hr.products_current_v;

ID PRODUCT
---------- --------------------------------------------------
1 product1
2 product2

SYS@o102> conn / as sysdba
Connected.
SYS@o102> alter system set fixed_date='2007-02-01-10:00:00';

System altered.

SYS@o102> conn hr/hr
Connected.
HR@o102> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;

NOW
-------------------
01.02.2007 10:00:00

HR@o102> select id, product from products_current_v;

ID PRODUCT
---------- --------------------------------------------------
1 product1
3 product3_new

HR@o102> conn / as sysdba
Connected.
SYS@o102> alter system set fixed_date=none;

System altered.

SYS@o102> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;

NOW
-------------------
26.01.2007 22:18:56

Update 22.03.2007

There is an important thing to note!!! In Oracle 9.2.x there is a bug regarding the setting of fixed_date. Once you restart the instance, you will run into an oracle error: ORA-00065: initialization of FIXED_DATE failed

SQL> ALTER SYSTEM SET fixed_date = '2007-04-01-10:00:00';
System altered.

SQL> ALTER SYSTEM SET fixed_date = NONE;
System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00065: initialization of FIXED_DATE failed
SQL>

Once you hit this error, you will need to recover using the following approach:
SQL> create pfile from spfile;
File created.
A new pfile was created in the $ORACLE_HOME/dbs directory, in my case:
/opt/oracle/product/9205/dbs/inito920.ora

Edit the file and delete the line *.fixed_date='NONE'

Then restart the database:
SQL> startup pfile=/opt/oracle/product/9205/dbs/inito920.ora
ORACLE instance started.

Total System Global Area 571545196 bytes
Fixed Size 452204 bytes
Variable Size 402653184 bytes
Database Buffers 167772160 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

SQL> create spfile from pfile;
File created.
Then restart the database to have it started using the spfile.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 571545196 bytes
Fixed Size 452204 bytes
Variable Size 402653184 bytes
Database Buffers 167772160 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
Now do it again, this time the proper way:

SQL> ALTER SYSTEM SET fixed_date = '2007-04-01-10:00:00';

System altered.

SQL> ALTER SYSTEM SET fixed_date = NONE;

System altered.

SQL> alter system reset FIXED_DATE scope=spfile sid='*';

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 571545196 bytes
Fixed Size 452204 bytes
Variable Size 402653184 bytes
Database Buffers 167772160 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>

Regards,
~Dietmar.

Tuesday, January 16, 2007

XE: ODBC connection to a remote XE server

How do you set up an ODBC connection to a XE instance on a remote server?

The basic steps are:
* Download and install the XE client on your Windows machine
* Create an ODBC DSN entry
* Create a new MS Access MDB and link two tables from XE (using the sample user HR)

First, you download the XE Client software from the Oracle Technet: http://www.oracle.com/technology/software/products/database/xe/index.html

Double-click on the downloaded file to install it:


During the installation choose to install the client software into a directory of your choice and finish the installation.



After that you can configure the ODBC entry. Goto Start > Control Panel > Administration > ODBC DatasourcesOn the tab System-DSN add another data source:


Choose Oracle in XEClient:


Enter the details for the connection to the remote machine, in my case the remote machine is daust3.opal-consulting.de

Data Source Name: choose an arbitrary name
Description: choose an arbitrary description
TNS Service Name: host:port/SID
User ID: Oracle user to connect to

The service name is critical here, the hostname is the name of the remote machine but it can also be an ip-address. The port is the port on which the Oracle listener is operating, usually 1521. The SID is fixed, it is called XE.

Then test the connection by entering the password for the user HR. The result has to be "Connection successful".


After that start MS Access (in my case I used MS Access 2000) and create a link to the tables stored in XE. You can do this either by clicking on New > Link table


or by right clicking in the table pane and selecting Link Tables ...


then choose ODBC Databases from the bottom of the list:


Choose the computer data source XE, which we just configured:


then enter the password for the user HR. You can also just overwrite the User Name with a different user you want to connect to:


then select the relevant tables by holding down the control-key and click on the table names. Then hit OK.


Voila! The tables are linked and you can start using them:


Just be aware of firewalls!!! If you cannot connect to the remote machine the reason can be that port 1521 is blocked by your firewall!

Update: 18.01.2007

Storing the password in the DSN entry

You can also store the password together with the username in the DSN entry. This way the login prompt won't show when you link tables in MS Access. This might be useful in certain situations.
Just use the username/password in the User ID field when configuring the DSN entry.
For example you want to connect as the oracle user HR with password hrpwd. Then enter HR/hrpwd as the User ID instead of HR.

Thanks to Jer for this tip ( post in the XE forum ) .


Update: 18.01.2007

Using tnsnames.ora with XE client

In the DSN entry you can specify the connection information to the XE server instance using the easy connect syntax, just as shown above.

But you can also use the traditional resolution via the tnsnames.ora file. This adds another layer of abstraction so that you can access the tnsnames entry in you VBA code and not hardcode the server, port and SID information into your code.

To accomplish this, the following steps are necessary:

First, you have to add the variable TNS_ADMIN to your Oracle configuration in the registry.
Start regedit and go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_XEClient.



Then add a new string called TNS_ADMIN. Set the value to the directory where you want to store the tnsnames.ora file (you can also reference a directory of another Oracle install where a valid file is located).




Then create (or copy) the file tnsnames.ora and enter the following content:

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DAUST3.opal-consulting.de)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

Then you can reconfigure your DSN entry and replace daust3.opal-consulting.de:1521/XE with XE.


Regards,
~Dietmar.