Remove Blanks from Formated Date Column

This small example is showing how powerful jQuery is and how easy it is to use it in APEX. In this case, I needed to correct the oracle formatting, which adds blanks to the name of the day prior to comma. Probably oracle RPAD's the day names to the longest (Wednesday). Those are showing in APEX as one blank - see the left column. The only good way to go around that annoying side effect is to use jquery and basically one line of code.

APEX Feature Request

Just created a new feature request for APEX at https://apex.oracle.com/pls/apex/f?p=55447:19:::NO:19:P19_ID:50481528500531591330407043519019274105 … Extend Interactive Report API - Get IR Query. The feature request is abut the following:

"This API should deliver a couple of different SQL statements for an interactive report. There are several possible requirements I can think of:

1. IR query including visible columns and filter values - actual SQL for the user session,
2. IR query including all columns and the original SQL,
3. get column names of an IR with or without column alias,...

Having this SQL we should be able to run it as EXECUTE IMMEDIATE, without having to replace any binds.

This feature could be included in the actions menu and available as a plugin for dynamic actions - new dynamic action feature (action)."

Please, feel free to go there and vote for it.

Select all Checkboxes in IR - APEX 5

If <input type="checkbox" onclick="$f_CheckFirstColumn(this)"> doesn't work for you in APEX 5 Interactive Reports then you will need to disable Fixed Headings (standard). Fixed headings splits the report into two tables and the function won't work.

Oracle Live SQL

Oracle Live SQL is a great source for learning SQL and PL/SQL. It has a lot of good examples and interactive educational tutorials. The best thing is, it runs on APEX ;) Add this site to your bookmarks.

Oracle Database Developer Choice Awards (2nd)

I was just looking at the voting results for ORDS and APEX. In the ORDS category Dietmar Aust is leading and in the category APEX it is Morten Braten. So far so good. These men deserve the award for sure - they did a lot for the community and the most of us are using (or have used) their solutions. Think of Morten's PL/SQL Utility Library or Dietmar's PDF Printing...

The only thing I have a problem with is the absolutely microscopic number of votes. The most votes in the category ORDS has Dietmar - 134. In APEX it is 98 votes for Morten. Even Jari Lane who is very well known in the APEX community and has helped numerous people with his solutions has received all together 103 votes - obviously almost the half of it as down-voitng. To my point of view this is incredible. But if I think back and do a comparison with my own experience then this is actually normal. In the last seven years I have received more than 9.000 emails from the community asking either for access to my workspace, my Demo Application or asking for an APEX solution of some kind. As a result of that I have created almost 4.000 accounts and answered 95% of those emails. But all together, maybe 50 receivers responded saying thank you.

Conclusion:

Shame on us. We are lazy and cynic. We are constantly talking about how big the APEX community and how strong it is. Maybe, because there are a few people out there like Dietmar, Jari, Jürgen Schuster, Morten and few others. The rest seem to be there only when they need something.

Oracle Database Developer Choice Awards

Don't forget to vote for the expert of your choice at https://community.oracle.com/community/database/awards. There are a lot of good men there deserving your voice for their activities in the community and for their selfless engagement.

Count Selected Rows

This small example is showing hot to make sure at least one row in your tabular form has been selected prior to proceeding:

https://apex.oracle.com/pls/apex/f?p=93000:806

Switch Panes in Page Designer

This is definitely worth of mentioning. Go here and download this plugin for Firefox or Chrome. You will like it. It switches the panes in the Page Designer so you can much easier access the properties of the selected elements. Congrats Filip. This is a cool feature. Thanks.

Expert Oracle Application Express 2nd Edition

Expert Oracle Application Express 2nd Edition seems to be available at Apress. Buy this book and support the charity project in memory of Carl Backstrom and Scott Spadafore (previous members of the APEX Team). Please note that all funds will be donated to the families of these two great men.

New APEX Blog

This is a new blog on APEX. I expect a lots of good stuff there in the future. The last example describes how to modify the Page Designer and move the panes, adjust the layout and simplify the development of an application since accessing element attributes is much easier that way. I think this is worth of looking at and maybe the APEX team considers this as an improvement for the future release.

APEX 5 - Opening and Closing Modal Window

This example is showing how to open a Modal Page from any element in your application. It is easy to get it working using some standards like a button or a link in a report. However, it is not 100% clear how to get it working with some other elements which don't have the redirect functionality built in (item, region title, custom links, etc.). This example is also showing how to get the success message displayed on the parent page after closing of the Modal Page.

APEX 5.0 New Features Training September 2015

Oracle Application Express 5.0 wurde am 15.04.2015 freigegeben und ist als Download verfügbar.

Wir haben jetzt alle über zwei Jahre auf das neue Release gewartet ... und das Warten hat sich mehr als gelohnt ... die neuen Möglichkeiten werden Sie umhauen!

Wir haben die neuen Möglichkeiten genau unter die Lupe genommen und sind selbst total begeistert, weil sie das tägliche Arbeiten extrem vereinfachen. Wir haben schon einige Anwendungen in APEX 5.0 neu programmiert und einige ältere Versionan auf 5.0 umgestellt.

Mit dem neuen Pagedesigner sind wir so produktiv wie noch nie, das Universal Theme erlaubt auf einfache Weise, richtig elegante Applikationen zu entwickeln. Damit ist wirklich ein Durchbruch gelungen, die Konfiguration (über Template Optionen) und selbst farbliche Anpassungen sind ein Kinderspiel, der integrierte Theme Roller ist einfach genial!

Modale Dialoge, multiple Interaktive Berichte pro Seite, Erweiterungen für die mobilen Endgeräte, ein komplett neues File-Handling und viele neue Security - Features ... dieses Release ist wirklich umfangreich!

Neben diesen großen Features wurden auch wieder über 100 kleinere oder auch größere Verbesserungen implementiert.

In unseren Kursen haben wir schon über 200 APEX-Fans die besten Herangehensweisen, Tipps und Tricks beigebracht. Durch die Hands-On Übungen vertiefen wir diese und Sie können diese sofort einsetzen ... oder Sie schlagen sie nach ... wenn Sie sie später brauchen ;). Aber auf jeden Fall wissen Sie nach dem Kurs, was möglich ist!

Wir beide (Denes und Dietmar) entwickeln seit 2006 fast jeden Tag mit APEX Applikationen für unsere Kunden, wir haben mit APEX schon alles ausprobiert.

Nehmen Sie die Abkürzung und lernen Sie von den Besten, was für die Praxis am wichtigsten sein wird.

Um die Vorteile des neuen Releases möglichst schnell nutzen zu können, melden Sie sich am besten sofort an und sichern Sie sich Ihren Platz!

• Klicken Sie auf den Link "Anmeldung zum Kurs".
• Tragen Sie Ihre Anmeldedaten ein und klicken Sie auf den Button "Anmelden".
• Sie bekommen sofort eine Bestätigungs-Email zugeschickt.
• Sobald Sie in der Email auf den Link zur Bestätigung klicken, haben Sie Ihren Platz gesichert und sind auf jeden Fall dabei!

Anmeldung zum Kurs

P.S.: Die vollständige Agenda und weitere Infos zum Kurs gibt es online in der Kursbeschreibung.

Quick and Dirty - Refreshing a Select List of Values

This is a quick and dirty solution but it works. It shows hot to update a select list of values on demand without a lot of coding.

AJAX Callback and jQuery creating Page Items Dynamically

This example shows a couple of interesting techniques:

• 1. How to create and use an AJAX Callback process instead of an Application Process on Demand,
• 2. how to create a dynamic page item,
• 3. how to modify the item display settings and reference Font Awsome icon set and finaly,
• 4. it shows how to call an AJAX process from javascript.

This question is based on a recent forum posting where this question was asked.

Have a look and enjoy.

APEX Meetup Frankfurt

Am 26.06.2015 17.00 treffen wir uns in Frankfurt a. M. bei einem weiteren APEX Meetup. Danke an Sabine Heimsath und Moritz Klein für die Organisation. Ich zeige dort, wie man lokal eine XE Datenbank zusammen mit ORDS und Glassfish konfigurieren kann. Danach kann sehr leicht jede beliebige Version von APEX nachinstalliert werden. Der Vorteil ist, ich kann alle Features einsetzen (RESTful Services, XLS Upload) und der Austausch vo Images bzw. Konfiguration für die neue Version von APEX sind kein Problem mehr.

Die Adresse ist:

Ericsson Telekommunikation GmbH
Herriotstr. 1
Frankfurt

Our next meetup is in Frankfurt on 26th of June. Thanks to Sabine Heimsath and Moritz Klein we will meet at Ericsson Telekommunikation GmbH Herriotstr. 1 Frankfurt. I will demonstrate how to install XE with ORDS and Glassfish and how to upgrade to APEX 5.0 on a local virtual machine.

APEX 5.0 - Run Applications in New Tabs Part 2

In this blog posting I was showing how to get the Firefox Browser to work with tabs in APEX 5.0. It looks like APEX 5.0 was "optimized" to work in Google Chrome and the other browsers like IE and Firefox were not fully considered. I was able to make it working using the settings I described in the first post. However, the old style popup windows would also open in a new tab. Yesterday I discovered a feature I wasn't aware of. Changing the settings using that feature helped me to get everything working as it worked before. Here is what I did:

• 1. I uninstalled all Tab-Addons in Firefox.
• 2. The standard settings in Tools / Options / Tabs are shown in the following screenshot:
  
  
• 3. I installed Tab Mix Plus Add-On and changed the settings as in the screenshot:
  
  
• 4. Finaly, I changed the preferences in my workspace:
  
  
  

Now, the consequences were that clicking on Run Application button, would automatically open the application in a new tab:

and the classic popups like image popup would open in a new window:

The only thing is that you need to set the options for each workspace you use.

Enjoy.

APEX Plugins

This may sound like a joke but I think there must be something wrong here. This page APEX-Plugin.com is supposed to provide all the plugins out there. The only thing is that you need to log in before you download and that's O.K. However, I forgot my password and wanted to change it. The process is quite simple:

• I click on forgot my password link.
• Enter my email address.
• After that I receive a unique token per email.
• I enter that token and change my password.
• The last thing to do is to log in with the new password.

And this is where it starts. I can't log in and nothing happens. I did write an email asking for help but except a confirmation email there was no response whatsoever.

Anyone with the similar problem?

Tabular Form - Add Rows Top - Universal Theme

This old example shows how to add rows to the top of the tabular form. Unfortunately this doesn't work with the new Universal Theme. In order to make it working some small changes are required. See this example on how to do it using the new Universal Theme.

Enjoy.

APEX 5.0 - New Features Training

Unser Training startet in knapp drei Wochen. Es ist zeit sich anzumelden. Wir (Dietmar und Denes) haben Tobias Arnhold eingeladen und er erzählt über das Thema Migration auf APEX 5.0. Tobias ist einer der wenigen Betatester von APEX 5.0 in Deutschland gewesen und hat die wertvolle Erfahrungen damit gemacht, die er mit uns teilen wird.

Unsere Themen zu APEX 5.0 sind zahlreich und vielfälltig. Wir decken fast alles wissenswertes ab:

1. Page Designer,
2. Universal Theme,
3. Dutzende von kleinen aber unbekannten Verbesserungen,
4. Interactive Reports,
5. File-Handling,
6. Deprecated Features und Known-Issues,
7. Modale Dialoge,
8. Mobile Anwendungen
9. Viele weitere Themen wie Auswirkungen der neuen Features auf bestehende Komponenten:

   a.Forms und Tabular Forms
   b.Plugins und Dynamic Actions

10. Migrationsthemen

APEX 5.0 Universal Theme Bug - Update

The APEX team came up with a temporary fix for this issue. Please visit this forum posting for all the updates on the issue. Basically, you need to run a small function on page load. The solution is also described there.

By the way this is one of the three bugs I have discovered so far.

APEX 5.0 Universal Theme Bug

The problem I described here is strange. In my case and I tested it on several laptops with many browsers and multiple installations, appears to be a Universal Theme related bug and occurs in some specific constellation - in my case on Windows 8.1 running FireFox or Google Chrome (IE11 works as expected). I noticed this problem while working on the Tabular Form chapter for the new Expert APEX book. The first thought I had is that I see pink elephants because the colleagues I asked to check the issue couldn't confirm it - they were using different OS. This seems to be somehow Windows 8.1 related by I am not 100% sure. Your help would be great and if you have the same problem, please join the post and provide your input.

APEX You Tube Channel

Great idea to organize the Oracle APEX YouTube Channel. David Peake has started a Video Series about the new Page Designer in APEX 5.0. You should have a look.

jQuery Toggle Image

For this, you will need two lines of code. In case you want to save the values, there is a bit more work to do but everything can be done using standards.

jQuery - Replace Image

I am posting this just because I am getting questions related to this or similar issues all the time. In this example you can see how you can replace images on the fly in your page using Dynamic Actions. There are at least two good ways to do that:

1. Rendering images in a report. Refreshing a report is a predefined event in a Dynamic Action and doesn't require coding.

or

2. Using jQuery to do that.

Enjoy.

Tabular Form - Change Column Type

Just started a new version of My Demo Application - based on APEX 5.0 and Universal Theme. There, I will rework the old content and provide some interesting new possibilities (as of 4.2). In this example I am showing how to change the column type from Text to Display in a tabular form.

When could this be useful? For example if you have a certain group of users not allowed to edit a column and you don't want to create additional and conditional columns. Eventually you can use this to set only specific rows to display only depending on your application logic.

APEX 5.0 - PL/SQL Function Returning Error Text - Bug 20971572

I prefer using PL/SQL Function Returning Error Text while creating validations. Two days ago I discovered a bug - it is not possible to create the function if no error message entered. This bug has been documented and will probably be fixed in 5.1. The workaround is to enter a dummy text like "ERROR" or similar.

APEX 5.0 and Standard Report Column

If you are using the new Page Designer in APEX 5.0, you may search for the "Standard Report Column" in the list of column types. If you switch to the component view, you will see it. In the Page Designer you don't have the old column types any more. You should choose "Plain Text" and activate the option "Escape special characters" on the bottom of the column options.

Expert Oracle APEX 2nd Edition

Just submitted first of my two chapters (Tabular Forms) for the new Expert Oracle APEX 2nd Edition. The chapter is covering the most important practices in Tabular Form handling. Also, a couple of neat features like cascading select lists in Tabular Forms - this time using standards only. Still not sure when the book will be published but it should happen soon.

 Follow on Twitter

APEX 5.0 Summer School 2015

Es tut sich ganz schön was in der APEX Community dieses Jahr. APEX 5.0 wurde veröffentlich. APEX Connect im Juni findet statt und sollte bisher der größte APEX-Treffen im deutschprachigen Raum werden. Nun, wurde auch eine Reihe an Webinaren organisiert - APEX 5.0 Summer School - für alle, die keinen Urlaub machen oder für diejenigen, die den Urlaub langweilig finden. Meldet euch an. Die Anzahl der freien Plätze ist nicht unendlich.

APEX 5.0 will be released today

Great news. Just finished wathching the Google hangout with the APEX team. They confirmed that APEX will be released today. The download should be made available soon. Stay tuned.

Ask the Real Experts

On Wednesday you will have a chance to ask the APEX developers anything you would like to know about APEX 5.0. See this posting from Patrick Wolf or go directly to the registration form in order to get a link.

apex.oracle.com - Upgrade to APEX 5.0

It is time to get used to the new APEX builder interface. apex.oracle.com has been upgraded to the latest pre-production version of APEX 5.0. Some functionality like "Administration" are not visible any more and you have to search a bit. The overall impression is good and my old demo application seem to still work in the most of the cases.

Enjoy.

Read Only Item Layout

The possibility to set the items conditionally to read only is one of the important security features in APEX. Using this feature you can secure your applications and it will not be possible to manipulate the protected item (region or page). The downside of this feature is that it will change the layout of the protected items to some kind of standard, ignoring the previous formatting in the "normal" mode. If you have a form which looks like this:

and change the description item to the read only mode, then the page will look a lot different and will be hard to control:

In such cases you can help yourself in different ways. Using some css or jQuery, you can make those read only items to appear only slightly different. For example, I used this code to change the layout of the description item on page load:

$('#P3_BP_DESC_DISPLAY').attr("readonly","readonly").css({"font-weight":"bold","color":"#ccc","display":"block","width":"240px"
,"height":"120px","overflow-y":"auto","border":"1px solid grey"})

and the result looked like this:

There are also some other more generic methods:

.display_only {font-weight:bold; color: #ccc; display: block; width: 200px}

$('[id*=_DISPLAY]').css({"font-weight":"bold","color":"#ccc","display":"block","width":"200px"})

$('.display_only').css({"font-weight":"bold","color":"#ccc","display":"block","width":"200px"})

The only thing you need to note is that setting an item to the read only mode will create two page elements. The displayed element will get the suffix _DISPLAY added to the corresponding item ID.

Enjoy.

APEX Connect June 2015

APEX Connect in Düsseldorf in June 2015 is going to be the biggest APEX-only event in Germany so far. You should consider joining us.

APEX Connect in Düsseldorf im Juni 2015 wird der größte APEX-Treffen bisher sein. Meldet euch und hilft uns es noch erfolgreicher und größer zu machen. Viele interessante Vorträge und vor allem viele interessante Persönlichkeiten aus der APEX-Welt werden dort sein. Das ist eine ausgezeichnete Gelegenheit viel Neues zu erfahren. Anmeldungsformular kann man hier aufrufen. Die Preise sind moderat und durchaus im Rahmen.

A good Rule of Thumb

I like the newest blog post from Joel Kallman and especially his rule of thumb:

"My rule of thumb - when you're editing code in a text area/code editor in the Application Builder of APEX and you see the scroll bar, it's time to consider putting it into a PL/SQL package."

I would go further and say that even the smallest application you create should have at least one package for all the PL/SQL code you write.

IR Scrolling - With a Little Help From My Friends

If you are working with interactive reports you will for sure be faced with a problem of wide reports. If you are taking care of the page layout and eventually have more than just an interactive report on the page, you will want to limit it's size to something making sense. The first problem will appear if you limit the width by setting the region attribute to something like this

style="width:830px"

and you will not see some of the columns:

If you add a scrolling by wrapping the region in a div and adding the following to the region header:

<div style="width:810px;overflow-x:scroll">

and closing it in the footer by adding:

</div>

you will be able to scroll with two ugly side effects:

The action bar will be included in the scrolling as well and disappear as you scroll to the right.

The sort widgets for the columns will appear on the wrong position the more you scroll.

You can solve this problem in the following way:

Remove the scrolling DIV from the region header / footer.

Use this java script in the page Function and Global Variable Declaration:

function onload_ir(p_width, p_report_id){

$('<div id="scroll_me" style="width:' + p_width + 'px;overflow-x:auto;display:inline-block"></div>').insertBefore('#apexir_DATA_PANEL'); $("#apexir_DATA_PANEL").appendTo("#scroll_me"); $("#apexir_DATA_PANEL").show();

var or_Finished_Loading = gReport._Finished_Loading; gReport._Finished_Loading = function(){ or_Finished_Loading(); if(gReport.current_control=='SORT_WIDGET'){

var offset_pos = $("#" + p_report_id ).position().left; var pos_left = $('#apexir_rollover').css('left'); pos_left = pos_left.replace('px',''); if (pos_left>p_width-100) {new_pos = parseFloat(pos_left) + parseFloat(offset_pos) - 25; $('#apexir_rollover').css('left', new_pos+'px');} }; }; };

Create a Dynamic Action which runs After Refresh (fire on page load option should be turned on) of the IR region and execute this script there:

onload_ir(810, 7990109002761687)

810 is the widht of the scolling region, which is a bit less then the total width of the region.

7990109002761687 is the id of the data grid of the interactive report. You can find this id if you use firebug and scroll to the point where the data grid is placed.

What this script does is:

It will wrap the data grid into an additional div and add a scroll bar to it.

It will overwrite the IR onload function and add a sort widget positioning function to it in order to reposition the widget according to the scrolling.

The important part of the overloading function was done by Tom Petrus, who is a big help when it comes to tricky stuff like this.

Now, once you have done that, your report will show up properly once you scroll it.

Enjoy.

The most frequently abused feature in APEX

Today I participated in the Scott's survey:

https://apex.oracle.com/pls/apex/f?p=70347:Q::AAC3

and there was one really interesting question:

"What do you think is most frequently abused? (and why)"

If APEX has any week points than it is definitely the fact that you can place your code almost everywhere. Especially PL/SQL chunks of code. If I start counting and list all the places it will be a very long list:

Page Read Only and Cache Page Condition

Region Conditional Display and Read Only Condition

Buttons Conditional Display

Items Conditional Display

Items Source value or expression

Items Post Calculation Computation

Items Default value

Items Conditional Display and Read Only Condition

Computations

Processes on Submit, on Load, on Demand

Validations and Validation Conditions

...and yes, Dynamic Actions as my favorite.

There is of course more but I will stop counting here. If you start working with APEX this is great - you will easily get on target and have an application up and running in no time. A little bit of code here, a little bit there and there we go.

This problem becomes obvious if you application is more than just a small and temporary solution shared between a couple of people. As an application grows it will start suffering from performance issues. Furthermore it will be hard to maintain it. It will have a dozens of complex pages with many items, many computations and processes, conditional buttons, validations and dynamic actions. If you practice writing anonymous PL/SQL blocks and you paste those wherever it is possible, your code will become redundant and slow. You will probably repeat the same code many times on your page. This means, your pages will require more processing time than they should. I would even go so far and say this is almost as bad as putting your business logic into triggers. Have you ever had a chance to debug such applications (and you didn't know the code was placed there)?

The possibility to have so many options is of course great and useful. But it doesn't mean you should use all of the options. The other thing important to know is that you should never write anonymous PL/SQL blocks in your applications. Never! Whenever you need to use PL/SQL, you should use packages and place your code there. The probability that you will then repeat your code is quite low and for sure your application will run much faster.

My proposal for the future version of APEX would be to have a subtitle for each of those containers saying "Handle with care" and providing some explanations why. I am serious here. Really.

Alert Function

In APEX you will probably use javascript alert function in many cases by saying:

alert ('some message');

The popup box you get looks a bit strange and outdated. As an alternative to this you may use this function call:

function get_alert (p_message,p_id) {$("<div/>", { "html":p_message}).attr({"title":"Error has occurred!"}).dialog({
modal:true,
buttons:{"Go to Error":function(){$(this).dialog("close");
$(p_id).select();}}
}); }


As you can see in this example, you can add additional functions to this alert box in order to do something after closing of the alert message. You can choose to display the alert as a modal window as well.

Enjoy.