Dietmar Aust

Subscribe to Dietmar Aust feed
Updated: 1 week 1 day ago

How to locate all interactive reports in my application?

Wed, 2013-05-22 06:07
Today my client decided to remove the email functionality from all interactive reports in their APEX application.

I could step through all the pages of my application and check whether there are any interactive reports used. This very manual approach can take some time and I can make mistakes by overlooking some of the interactive reports to modify.

A different approach would be to use the (officially supported) data dictionary views in APEX. Using this simple sql query I can find all interactive reports in all pages of my application (having the id 20120618):
select   workspace
, application_id
, application_name
, page_id
, region_name
, download_formats
from apex_application_page_ir
where application_id=20120618
order by page_id;
This will show all interactive reports which are used in my application:

We can even look for all interactive reports that need to be fixed by specifically looking for all reports where the download format email is enabled:
select   workspace
, application_id
, application_name
, page_id
, region_name
, download_formats
from apex_application_page_ir
where application_id=20120618
and instr(download_formats, 'EMAIL') > 0
order by page_id;
These data dictionary views contain a wealth of information about your application. This is a real strength of Oracle Application Express, due to its nature being driven by a metadata repository.

Thus from a quality management perspective you could use the following query to make sure, all interactive reports are configured the same way, i.e. having flashback disabled, using the same display position for pagination (Top and Bottom-Left) and showing the dropdown list for selecting the rows per page:
select   workspace
, application_id
, application_name
, page_id
, region_name
, pagination_display_position
, show_rows_per_page
, show_flashback
from apex_application_page_ir
where application_id=20120618
and ( pagination_display_position <> 'Top and Bottom - Left'
or show_rows_per_page = 'No'
or show_flashback = 'Yes' )
order by page_id;
Here is the result:

But how do we find the appropriate views? The APEX data dictionary contains information about all available data dictionary views and all of their columns.

Typicall I use the following query to find all information about interactive reports, all related views contain _IR_ in the view name:
select *
from apex_dictionary
where apex_view_name like '%IR%'
This will show us all view including their respective columns:

If we want to display the view only without their columns we can add the filter for the column_id 0, in there the APEX team added the overall description of the view itself:
select *
from apex_dictionary
where apex_view_name like '%IR%'
and column_id=0;
Now, we get only the view names including their description:


Oracle Application Express 4.2.2 Patch released

Sat, 2013-04-27 04:36
Just yesterday, the most recent APEX patch set has been released. It is Release The full version just as well as the patch set to apply to a 4.2.0 or 4.2.1 instance can be downloaded from here.

The full details can be found on Joel's Blog

Nevertheless I will have a good look at the release notes, because they contain quite some important notes, which you should be aware of before upgrading. Especially, there were some changes made to the templates. So it is wise to upgrade your existing 4.2.0 / 4.2.1 instance with the patch set directly. If you export/import a 4.2.1 APEX application into a 4.2.2 instance, you cannot leverage the changes immediately, you would have to install the new theme and switch it then. 

I will try to look at these issues in more detail ...


Join me at ODTUG's Expert Panel - Oracle Application Express

Mon, 2013-04-22 14:48
In May, I will take part in one of ODTUG's webinars:

Tuesday, May 14, 2013 12:00 PM - 1:00 PM EDT

ODTUG's Expert Panel - Oracle Application Express

Panelists: Dietmar Aust (Opal Consulting), Roel Hartman (APEX Evangelists ), Dan McGhan (Enkitec)
Moderator: Patrick Cimolini (Patrick International APEX Consulting) 

This is a live interactive panel similar to the ACE Lunch and Learn panels held at Kscope. This innovative webinar is modeled after a call-in radio talk show. You will be able to chat a question to our organizer and if it is accepted you will have the option to verbally ask the question to our panel. Please be sure to have the proper equipment (headset and a quiet office) so that our panelists can clearly understand your question. You’ll also be able to participate in the discussion with the panel while they answer your question. 

The following are some potential topics, but audience participation will shape the scope of the panel: 
  • What skill sets does an APEX team need? 
  • What options are available for producing printed reports from APEX?
  • Moving away from Forms/Reports to APEX 
  • Interactive Reports - THE killer feature of APEX 
  • Developer Productivity - negotiate and tweak the requirements with the customers in order to use standard APEX components to implement them = unparalleled productivity
  • Real-world showcases (APEX can handle application complexity just as well as a high load serving many users)

Personally, I like ODTUG a lot, it is a friendly and very competent place to be, I always enjoy the conference.

The panel will be held for 90minutes, so there will be a lot of time asking questions :).

You can register for the webinar here


How to make your browser save your password

Thu, 2013-04-18 10:13
Typically and for good security reasons the password manager from either Firefox or any other browser will not prompt you to save the password for the login screen of the APEX workspace or any of your own APEX applications.

Why? The APEX team implemented the login page with the HTML attribute autocomplete=off:

For a development environment I find this behaviour kind of annoying, especially when the APEX builder session times out frequently and you have to login to your workspace multiple times during the day.

Thus I have looked around and found a javascript routine which removes the autocomplete=off attribute from the form.

Just right-click on this link and save it as a bookmark: Force browser to save the password
I have found it here, but it is explained on many other websites as well.

Once you click on your bookmark you will see the following message:

If you continue to log into APEX you will get the typical message where you can save the password now:

It is good practice to use a password for your password manager in Firefox, too. Here are the steps to enable it.

Cheers and enjoy,


APEX Training 15.04. - 17.04.2013

Mon, 2013-03-04 00:56
Wie jedes Jahr in den letzten sechs Jahren, veranstalten wir (Denes Kubicek und ich) unser

Oracle APEX: Knowhow aus der PraxisTraining in Bensheim an der Bergstrasse. Wir werden unsere bisherigen Themen überarbeiten und einige neue Themen hinzufügen. So werde ich auch folgende neue Themen in das Programm der Schulung aufnehmen:

- jQuery (Beispiele und Übungen)
- APEX Collections
- Erstellung von komplexen Forms
- APEX und Mehrsprachigkeit

Wir haben dieses Mal einen ganz speziellen Gast zu der Schulung eingeladen - Christian Rokitta aus den Niederlanden. Er ist ein Experte in Sachen Layoutgestalltung und Mobile Applikationen. Die Teilnehmer der Schulung werden sich gemeinsam für eins dieser Themen entscheiden und Christian wird es vortragen.

Unser Highlight sind auf jeden Fall die abendlichen  Q & A Session, in denen die Teilnehmer die Gelegenheit bekommen ihre eigenen Projekte vorzustellen und ihre konkrete Probleme mit uns zu diskutieren.

Die Anmeldung zur Schulung finden Sie hier.

Viele Grüße,

Upgrading the JasperReports libraries to 5.0.1

Wed, 2013-01-09 17:04
Would you like to upgrade your existing JasperReportsIntegration with the latest 5.0.1 libraries of JasperReports?

Here you go ...

Step 1: Download the files for 5.0.1
You can download the files here:

 Step 2: Shutdown the Apache Tomcat J2EE server Step 3:  Remove the existing JasperReportsLibraries from your existing installation
Typically, after you have installed your previous version of the JasperReportsIntegration toolkit on your Apache Tomcat J2EE server, the files will be located in the directory $CATALINA_HOME/webapps/JasperReportsIntegration/WEB-INF/lib, for example version 4.7.0 of JasperReports, where $CATALINA_HOME represents the path to your installation of Tomcat.

Then you would have to remove these libraries first. In this directory you should find two files for removal: and _jasper-reports-delete-libs-4.7.0.cmd, for *nix or Windows respectively. For *nix systems you would have to make it executable, though, e.g.: chmod u+x . Then you can call it and it will remove all files for version 4.7.0. But it will NOT remove the file for the JasperReportsIntegration and all other libraries which YOU might have placed there deliberately.

You can always find the required removal scripts here: . 
 Step 4: Install the new 5.0.1 libraries
Now you can just copy the new libraries from into $CATALINA_HOME/webapps/JasperReportsIntegration/WEB-INF/lib.
 Step 5: Start the Apache Tomcat J2EE server again
Now you system should be upgraded to the most current JasperReports 5.0.1 !

Just drop me a note when you need updated libraries for 5.0.2, 5.0.3, ... 6.0.0, etc. I have scripts in place to create a new package of the libraries. 

Here you can find the notes from my last upgrade (4.5.0 => 4.8.0), I hope it makes sense:

** download the libraries from:
** to /home/jasper/JasperReportsLibraries

cd /home/jasper
mkdir JasperReportsLibraries

** unzip them
cd JasperReportsLibraries
unzip -d JasperReportsLibraries-4.8.0

** stop tomcat server

** remove libraries of current jasper reports release
cd /home/jasper/tomcat/webapps/JasperReportsIntegration/WEB-INF/lib
chmod +x


** copy libraries of the new release to the WEB-INF/lib directory
cp /home/jasper/JasperReportsLibraries/JasperReportsLibraries-4.8.0/* /home/jasper/tomcat/webapps/JasperReportsIntegration/WEB-INF/lib

** restart tomcat