Switch between Apex Versions

This is a part of the apex documentation but the fact is the most of us simply don't know about it. I have several customers and those customers have different versions of Apex installed. I am using XE on my Laptop to develop their applications. Therefore I need to be able to use different versions of apex simultaneously. With a help of the two simple scripts wrapped into an executable, I can switch between the versions in seconds:

ALTER SESSION SET CURRENT_SCHEMA = FLOWS_030000;
exec flows_030000.wwv_flow_upgrade.switch_schemas('APEX_030200','FLOWS_030000');

to switch to the version 3.0.1 and

ALTER SESSION SET CURRENT_SCHEMA = APEX_030200;
exec apex_030200.wwv_flow_upgrade.switch_schemas('FLOWS_030000','APEX_030200');

to go back to the latest release 3.2.

One Million Pageviews per Year

There are 1226 registered users of my workspace on apex.oracle.com. Arround 300 of those are still active. Many of those inactive accounts are using a download of My Demo Applicatiion installed localy.



If I run Google Analytics for the last 12 months I will get a nice analysis of the visits which says the following:

1. 1,001,992 Pageviews

2. 104,510 Visits

3. 9.6 Pages per Visit

4. 8.25 Minutes - Average Time on Site

These statistics do not include the clicks and visits done within the workspace while investigating the code behind the single pages. At peak times the application reaches 6000 pageviews a day.

New Training - September 2009

I was too busy to post about our sucessfull training in March this year. It is nice to see how the apex community is growing. It looks like every company using oracle is sooner or later going to consider using apex to build their applications.

We decided to schedule our next training for September of this year. More details you can find here.

Ich hatte kaum Zeit in den letzten Monaten. Auch nicht, um über unser letztes Training im März zu berichten. Die Anzahl der Teilnehmer übertraf unsere Erwartungen. Die Anzahl derer, die es aus terminlichen Gründen nicht schaffen konnten war ebenfalls sehr gross. Wir haben deswegen entschieden einen weiteren Termin im September zu organisieren. Wenn Sie Interesse haben, können Sie hier weitere Details über dieses Training finden.

Using Oracle Text with Table Data

Using LIKE clause with large tables may be a serious performance problem, involving full table scans for even a very small result set. The time you need to get a result will increase tremendously. Using Oracle Text may help you overcome those issues. In this simple example in my Demo Application I am showing how to start using Oracle Text on a table. There are also many other features included with Oracle Text such as preferred terms, fuzzy searches, highlighting, and much more. All explained in Oracle Text Documentation. Try it out.

Apex Stammtisch

Dietmar hat es hier gepostet:

APEX Stammtisch am 10.2.2009 in Siegburg

Wir organisieren am 10 Februar 2009 um 19:00 einen Stammtisch in Siegburg

Sion im Carre
Neue Poststr. 15
53721 Siegburg
http://www.s-im-carre.de/

Sie können sich hier anmelden.

Custom Export to CSV (again)

If you ever had to deal with exporting data different from the data you display, you probably visited this blog posting:

http://spendolini.blogspot.com/2006/04/custom-export-to-csv.html

which shows how that can be done. The purpose of the post is a bit different - it shows how to adjust the format of the exported file. Just recently I had a requirement to display some reports created using analytic functions with some groupings and counts. At the same time, there was a requirement to export the data without the results of the analytics. I could have used Scott's posting but I was too lazy. Instead of that I used the following (simple) approach. Basically, you would create two reports - on in which you display your data and one (never displayed) for exporting. The only thing you need to do then is to modify the link label.

Setting Item Session State using Ajax

This question is a frequently asked one. So, how can you set a value of an item without submitting a page? Today, I answered that question in the Oracle Forum. In addition to that, I created an example in my Demo Application showing those few steps you need to make in order to achieve that. Basically, you just need a small piece of javascript in your page header and an "onchange" call of that javascript in the HTML Form Element Attributes of your item. The code is generic and you can just copy-paste it without a need to change anything.



Enjoy.

Apex Training

In diesen "Krisenzeiten" erzählen alle darüber wie alles schlecht ist und was alles noch schlechter wird... Das ist ziemlich lässtig und es macht schlechte Laune. Aber, was ist denn eine Krise? In meinen Augen, das ist auch eine grosse Chance etwas zu ändern. Natürlich, zum Besseren. Und es gibt so viele Möglichkeiten. Zum Beispiel eigene Apex-Kenntnisse zu verbessern. Patrick, Dietmar und ich organisieren im März ein weiteres Training in Bensheim im Alee-Hotel Europa, um Best Practice Vorgehensweisen aus vielen erfolgreichen APEX Projekten zu lernen.

Neben der Informationen, die wir uns in zahlreichen APEX Projekten angeeignet haben, kommen die abendlichen Sessions (von 19:00-??:00) hinzu.

Dort stehen wir Ihnen für zusätzliche Hands-On Sessions zur Verfügung, um die Beispiele des Tages durchzuarbeiten oder auch, um konkrete Fragestellungen in Ihren aktuellen Projekten zu diskutieren.



Profitieren Sie von unserem jahrelangen und praxiserprobtem Oracle APEX Knowhow! Die Agenda und weitere Details findet Ihr auf der Web-Site wo Ihr Euch auch anmelden könnt.

Dynamic Charts

Whenever you need to create a chart with multiple series, you will go there and hardcode those series, creating an almost identical SQL for each one of them. As long as your values remain the same, that will be ok. But what if they change? Actualy, the solution is pretty simple - you need to create a PL/SQL Block to return all the series in one SQL. This example in my Demo Application shows how to write such a Function.



If you replace the column and table names, you can use the same function for all of your charts with multiple series.

Enjoy.

Tabular Form - Checkbox for Setting values to 'Y' or 'N'

This is also one of frequently asked question in the Apex Forum:

http://forums.oracle.com/forums/thread.jspa?threadID=493243

http://forums.oracle.com/forums/thread.jspa?threadID=714172

Using a checkbox for deleting rows is fine, but how do you use it if you want to flag the records and have a value updated depending on the checkbox status (valid/invalid, yes/no)?

It was Patrick Wolf who initially helped me to understand how a checkbox array works. He also created an example (on the fly) to show this can be managed. However, this example was based on a standard tabular form and it couldn't be reporoduced by many interested visitors of my Demo Application. Since this question has been repeated several times in the last couple of weeks, I decided to create a new example, which is based on a manual tabular form.



So, if you have a similar requirement, which is there in many cases, you may want to have a look here:

http://apex.oracle.com/pls/otn/f?p=31517:219

Validation with Ajax

As soon as you need to do some date or number validations using javascript, you will notice how cumbersome that attempt could be. Just recently, I had to programm an application which needed some date and number validations prior to the page submittion. I found that using ajax and letting your database do those kind of cheks is a quite elegant way.

In my Demo Application you will find an example

http://apex.oracle.com/pls/otn/f?p=31517:217

showing how easy it is to set it up.

Especially the date validation is funny. It will accept all kinds of date formats:

sep/15.08 03:16:00 AM

sep.15-08 03:16:00 AM

sep/15/08 03

and it will, of course, give you a user friendly message if you go too far and try entering something like this:

14.15-08 03:16:00 AM



or for

10.15-08 13:16:00 AM

you will receive



Try changing your browser language settings from english to any other language and do the excercise again.

OOW

It is time to get my luggage prepared for a long trip to San Francisco. This will be my first OOW. The trip will be long and exhausting but I am happy because I will get a chance to meet many of my colleagues, the most of which I know only through emails or the oracle forums.



I will arrive on Saturday afternoon and stay there till Thursday evening. These will be five exciting days and the programm is starting already on Sunday. I was invited by Dimitri Gielis to participate in the "Ask the Experts" session together with some well known names from the apex community: John Scott, David Peake, Scott Spendolini and Anton Nielsen.



Also, Dimitri announced an Apex Meetup on Tuesday evening.

The target is to catch as many apex sessions as possible. Unfortunatelly, some of them are starting at the same time. It will be hard to make a decission which one to take.

I will stay at the King George hotel, which is on a walk distance to the Moscone Center.

So, if you are also attending this great event and you are interested in apex, there is a good chance we will meet there.

Oracle Forums Splash

I got this message at least five times today:


at http://www.oracle.com/splash/forums/index.html.

Having to read this for the fifth time, I was just asking myself what sense do these couple of sentences make.

"Oracle forums is experiencing technical difficulty."

So, what kind of a technical difficulty are you experiencing day by day since almost a month? Furthermore: how long is this going to last? Another month? Or two? Any ideas?

"We are aware of the issue and are working as quick as possible to correct the issue."

Hm, are you sure you are?

"Please try again in a few moments."

Well, I did but it still doesn't work.

"We apologize for any inconvenience this may have caused."

Nice to know.

"To speak with an Oracle sales representative: 1.800.ORACLE1."

What should I tell him?

"To contact Oracle Corporate Headquarters from anywhere in the world: 1.650.506.7000."

I don't think they would want to talk to me.

"To get technical support in the United States: 1.800.633.0738."

Unfortunatelly, I belong to the rest of the world.

Guys, if this continues, I will never ever collect enogh points to become a guru.

What's wrong with the Oracle Forum?

As you probably noticed, the forums are either:

- not available at all or

- taking a lot of time to load or

- if they ever load, they are not working properly and showing errors if you try to create or update a posting.

If you manage to load those pages, you will notice that there are some new featueres implemented. Some of them are usefull. Like the new text editor (I realy like that one). Some of them are however questionable:

- the new reward system where you can get some points if you provide a helpfull or even a correct answer. The funniest thing is that the asker is supposed to say the answer someone provided is "correct".

- the fonts were changed to 11px which is not readable at all,

- the visited property they implemented are i.m.h.o. unneccessary because of the image showing if the posting was opened before or updated since. It is anoying too. Hard to read because of the grey color on a grey background.

I already asked the people who set it up if they are realy sure this makes sense. They didn't answer. Well, I am probably not the only one who asked.

Despite of the changes made, features I like or those I don't like, the most important thing though is the availability of the forum. And the fact is - it is not available since almost a week. Have they done a test before they upgraded? How did they test it? Did they ask the comunity about the changes? Maybe they should roll it back once again and consider a redisign using ApEx.

Oracle Text in Five Short Steps

Almost every application I create contains a BLOB (file) table for storing files available for a download. In the most of the cases it will store some documentation related to the application - user manuals, howto's and similar stuff. The download functionality is a nice thing but how about searching the content of those files? There are some examples arround showing how to do that in ApEx but in my opinion the most of them are far to complex. I created an example in my Demo Application, showing how to do that in less than five minutes:

http://apex.oracle.com/pls/otn/f?p=31517:76



This example includes the most frequently used functionalities:

1. mixed case search

2. highlighted snippets

3. index synchronization on commit

If you need more, you can always look into the Oracle Text Documentation.

Enjoy.

Form Validation and User Friendly Messages

I am getting this kind of questions quite often:

"I have report with form for modifications. After clicking on the link the form related to this report pops up. Often this is done by at least two users trying to modify one and the same record simultaneously. In such cases the application raises - "ORA-20001: Current version of data in database has changed since initiated update process. Error Unable to process row ....(table name)".

Could you give some advice or give and example how to handle this in a more user friendly manner?"

I created an example in my Demo Application showing how this can be done:

http://apex.oracle.com/pls/otn/f?p=31517:210

which also includes a step by step guideline.



There, you may test the validation by starting a concurrent update process and trying to update the data on the parent page.

Add Rows to a Standard Tabular Form using Item Value

There are many threads regarding this issue in the forum. Many of them without any answer. So, how do you add rows to your standard tabular form based on an item value?
If you follow this example:

http://apex.oracle.com/pls/otn/f?p=31517:209

you will see that you need three small adjustments to the standard process:

1. extend your SQL using UNION_ALL by adding a small SQL selecting NULL's from DUAL followed by CONNECT BY LEVEL <= :P1_ADD_ROWS_ITEM,

2. add a conditional branch, first in the sequence, passing a request ADD to your page

3. do a modification to the standard add rows process to add 0 rows

and there you go. You don't need to write a manual process to get that functionality
applied on your standard tabular form.



Enjoy.

Dynamic Tabular Form

It can happen that you get a requiremen similar to this one:

http://forums.oracle.com/forums/thread.jspa?threadID=691046

1. You have an unknow number of tables

2. You don't know the structure of the tables in advance

3. You still need forms for entering and maintaining data

The only solution that comes to my mind are dynamically created tabular forms. Tabular forms have already a bad reputation. Now, if you add the word "dynamical" to it, it will sound even more horrorful.

As I promised in the mentioned thread, I tried to create a working example. This is what I came up with:

http://apex.oracle.com/pls/otn/f?p=31517:195

It took me arround three hours to get that code together. Of course, this can be done much better - using a package and providing some additional functionalities like validations and other item types.

Multiple Cascading Select Lists (again)

This is an issue for many developers. I see a lot of questions and receive a lot of mails on this topic. I thought that demonstrating how to do that with two items would be enough to guess how to do it with more than that, since the approach is the same. However, there is nothing better than a step by step guide to get it working.

You will find the extended example here:

http://apex.oracle.com/pls/otn/f?p=31517:119



My advice, from what I have seen so far debuging the other people examples, is to:

1. be carefull with copy-paste and before you do it, think of replacing the given item , table and procedure names with your own,

2. test your on-demand processes in the SQL workshop first, to see if they run,

3. avoid multiplying your code through copy-paste. This makes it much easier to debug and

4. if you have no experience with a similar solution, try to reduce your scope and do it on a simple example first. Try using emp, dept tables like I do it in my Demo Application.

Enjoy.

Default Sorting of a Report

This was an interesting question:

"My requirement is Report should always display the original sequence (in the same way if I run the report in TOAD or SQL*PLUS, ...) and when I click the column heading then only it should do the actual sorting. Now, as soon as I set the sortable attribute to YES for a column and running the report for the first time... it's doing the sorting based on the that column."

So, how do you do that? Normaly, if you enable sorting on an apex report, it will save the sorting preference per user in an item and will call this preference next time you login.

I used the rownum to display the "original sequence" on initial load of the page and enabled the sorting on that column as well. Putting this column to be the first in the row on columns with enabled sorting and hiding it would do the trick. The second thing I did was to remove the sorting preference on the initial load and keep the rownum ordering as long as the user decides to sort the report.

You can view a working example here:

http://apex.oracle.com/pls/otn/f?p=31517:13