Browsing "Older Posts"

APEX-AT-WORK no image

APEX 4.1 Tabular Form - ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

Von Tobias Arnhold →
I played a bit with tabular forms on views using an instead of trigger. After following the guide from
Christian Rokitta I came really close to what I needed. After I thought I was ready I got the following error message:
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

I didn't understood what was wrong so I searched the APEX forum for help. I found the reason in this post:
In there Marc Sewtz wrote about the issue I discovered.

My problem was that I tried to change an already existing tabular form which used declarative validations. After I deleted those validations everything worked fine.

My page was now available in 1 second instead of 5-6 seconds. Great solution.


One problem solved next problem ahead. When I tried updating my tabular form I got this nice Oracle error message: ORA-01031 insufficient privileges

I simply tried to add this sub-view to my updateable view which looked like that:
select 'Yes' as d, 'y' as r from dual
select  'No' as d, 'n' as r from dual;

Because "DUAL" is an Oracle system table or whatever. :) I had no privileges to update on this.
So I simply added a real master data table for this and it worked as expected.

Pivot Beispielapplikation ist Online

Von Tobias Arnhold → 10.25.2012
Ich habe endlich etwas Zeit gefunden die Anwendung online zu bringen. Hier ist der Link:

Aktuell arbeite ich an einer modifizierten Version. Wer Interesse an einer Live-Präsentation hat, sollte die nächsten lokalen DOAG Termine im Auge behalten.

Die aktuelle Version schicken ich Interessierten auch gerne zu.

Info zur Installation:
Wer auch die automatische PDF Generierung benutzen möchte, der muss folgende Steps durchführen:
 - Aktuelle jrxml2pdf Version downloaden:
 - jrxml2pdf Beispielapplikation installieren + Supporting Objects
 - In Beispielapplikation folgende neue Definition anlegen:
Name: PivotReport
jrxml for
(Achtung Feldgröße liegt bei APEX 4.2 nur bei 255 Zeichen, diese auf 32000 Zeichen erhöhen):
<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="" xmlns:xsi="" xsi:schemaLocation="" name="report1" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isFloatColumnFooter="true" uuid="0a6876d5-cc17-47a9-b072-2f4be3000e61">
 <property name="ireport.zoom" value="1.0"/>
 <property name="ireport.x" value="0"/>
 <property name="ireport.y" value="0"/>
  <![CDATA[select team as "Verein",
       nvl(sum("11/12"),0) as "11/12", nvl(sum("10/11"),0) as "10/11",
       nvl(sum("09/10"),0) as "09/10", nvl(sum("08/09"),0) as "08/09",
       to_char(sysdate,'') as cur_date,
       v('P10_USERNAME') as app_user
from (
  SELECT team,
  DECODE (jahr, '11/12', punkte, NULL) as "11/12",
  DECODE (jahr, '10/11', punkte, NULL) as "10/11",
  DECODE (jahr, '09/10', punkte, NULL) as "09/10",
  DECODE (jahr, '08/09', punkte, NULL) as "08/09"
  FROM (SELECT jahr, team, punkte FROM tbl_bundesliga)
group by team
order by team asc]]>
 <field name="Verein" class="java.lang.String"/>
 <field name="11/12" class="java.math.BigDecimal"/>
 <field name="10/11" class="java.math.BigDecimal"/>
 <field name="09/10" class="java.math.BigDecimal"/>
 <field name="08/09" class="java.math.BigDecimal"/>
 <field name="CUR_DATE" class="java.lang.String"/>
 <field name="APP_USER" class="java.lang.String"/>
 <variable name="PRINT1" class="java.lang.String"/>
  <band splitType="Stretch"/>
  <band height="62">
    <reportElement uuid="39c15a6e-237e-49f7-bb5b-343a8c60dd98" x="0" y="13" width="555" height="37"/>
    <textElement textAlignment="Center">
     <font size="22" isBold="true" isUnderline="true"/>
    <text><![CDATA[Bundesliga Historie 08/09 - 11/12]]></text>
  <band height="21" splitType="Stretch">
    <reportElement uuid="08af8ace-e78a-4a9e-afdc-6b74abeef679" x="108" y="0" width="1" height="20"/>
    <reportElement uuid="5a5b9e4f-672f-4f1c-be01-576545ed5efb" x="1" y="20" width="554" height="1"/>
    <reportElement uuid="215c203d-8de5-42c4-a64f-c5af97e973d4" x="0" y="0" width="111" height="20"/>
    <reportElement uuid="6ee6eec2-5139-44fc-856d-94cbd0be7e9a" x="111" y="0" width="111" height="20"/>
    <reportElement uuid="0b89f4f7-0cd0-4326-9b10-569d3386860b" x="222" y="0" width="111" height="20"/>
    <reportElement uuid="4b5b3f24-ad6c-4e1a-befc-0aa2126e29c5" x="333" y="0" width="111" height="20"/>
    <reportElement uuid="895f102a-e381-4348-899d-3141a0b28123" x="444" y="0" width="111" height="20"/>
  <band height="20" splitType="Stretch">
    <reportElement uuid="aa17a7d8-4339-401c-a143-39b6f289299d" x="108" y="0" width="1" height="20"/>
    <reportElement uuid="125ed9f3-0ee7-4d7a-895a-4bc917fdfdaf" x="0" y="0" width="111" height="20"/>
    <reportElement uuid="961d5a0f-c46e-4879-abf7-33dcd67d281f" x="111" y="0" width="111" height="20"/>
    <reportElement uuid="4a923094-0df9-482a-94d7-73f0f1638c6c" x="222" y="0" width="111" height="20"/>
    <reportElement uuid="aeec58bc-280a-492a-b6e4-7d42b5d7cd94" x="333" y="0" width="111" height="20"/>
    <reportElement uuid="8b66b028-f274-413f-a1e6-be0900b40a17" x="444" y="0" width="111" height="20"/>
  <band height="31">
    <reportElement uuid="a8347c0f-3c4d-42f3-99f7-766255410b0b" x="0" y="0" width="40" height="20"/>
    <reportElement uuid="9a8b7e7a-7482-48ff-8e70-f142b8291437" x="40" y="0" width="311" height="20"/>
    <reportElement uuid="b6a7c7cb-e435-4341-8b6f-976c3bbad57b" x="444" y="0" width="111" height="20"/>
    <textElement textAlignment="Right"/>
    <textFieldExpression><![CDATA["Datum: " + $F{CUR_DATE}]]></textFieldExpression>
APEX-AT-WORK no image

Pivot Beispielapplikation

Von Tobias Arnhold → 10.17.2012
Hallo Zusammen,
im Zuge der letzten APEX SIG habe ich meine Pivot Anwendung als Packaged App fertiggestellt. Wer noch Interesse an einer Kopie hat, der kann sich gern bei mir melden.

Die Live Anwendung werde ich ebenfalls bald auf dem online stellen.

Hide Interactive Report Column with jQuery

Von Tobias Arnhold →
Why would you need this? In case the end user wants to search for a text and doesn't need the corresponding column to be displayed.
But you may need it in a other situation?
APEX-AT-WORK no image

UILayout and S3Slider Plugin - Usability Update

Von Tobias Arnhold → 9.19.2012
After a lot of mails and requests I decided to create an example application and an extended documentation to each of the plugins. It will be published in the next weeks.

UILayout for APEX:
S3Slider for APEX


APEX SIG Event am 26.09

Von Tobias Arnhold → 9.18.2012
Nicht vergessen am 26.09 findet ein SIG Development Day zum Thema APEX statt. Mein Thema befasst sich mit APEX und Pivot Reports:

Aktuell arbeite ich noch an einer passenden Beispielapplikation, damit niemand denkt, dass es nur langweilige Folien zu sehen gibt.

Check-box bug in tabular form

Von Tobias Arnhold → 9.17.2012
I don't know if this is a environment problem or a general one. Anyway it is mad and took me a long while to find out what it is.
Here we go:
 - I have a tabular form with standard APEX processing.
 - The tabular form includes a single check-box column with y,n
 - The check-box column is conditional

As you can see the condition will always execute. And so it does. My column is perfectly displayed on my page.

Now whenever I try to save my changes. Nothing happens. The "Mulit Row Update"-Process does not get executed.
After some analyzing I found out that there were no more hidden elements for the tabular form created. APEX just didn't know what to save.

If I take the conditional display away it works fine.
As you see those marked input elements disappear with the conditional single checkbox.

Current environment:
We use Application Express

Is this a known bug? Have you ever experienced something like that?

After some more investigation and a good hint from a colleague I found the real reason.
What I didn't mentioned: All columns where conditional and in that case APEX doesn't know where to add the hidden elements. I made another column unconditional and it worked (even with my conditional single check box).
Good to know! :)

HowTo: Dynamisch berechnete Werte setzen

Von Tobias Arnhold → 9.16.2012

Wie berechne ich während der Laufzeit neue Werte und aktualisierte anschließend meine APEX Items.
Die Lösung für diese Fragestellung ist recht einfach, man nehme eine Dynamic Action nutzt die "Set Value"-Funktion um mit dieser mehrere APEX Items während der Laufzeit durch berechnete Werte zu setzen.

An Hand des folgenden Beispiels erkennen Sie die Logik:
Die Items haben  folgende Namen:

Bei Auswahl eines Teams werden automatisch die Felder Name, Punkte und Anzahl Meisterschaften gesetzt.

Dieses Beispiel besteht aus einer Dynamic Action, die 3 "True"-Aktionen beinhaltet.
Die erste "True"-Aktion reagiert auf das Change Event beim APEX Item P3_TEAM (unsere Select List).
Um die Werte entsprechend zu bestimmen wird eine Set Value Funktion ausgeführt. Diese berechnet die Werte für die Items: P3_PUNKTE & P3_ANZ_MEISTERSCHAFTEN
Außerdem wird dem Item P3_NAME der Rückgabewert unseres Teams zugewiesen.
Dies geschieht auf APEX Session und auf Browser Ebene.

Nun wiederholen wir nur die Übergabe der Parameter für die anderen beiden Items, in dem wir zwei weitere "True"-Aktionen anlegen.
Es mag vielleicht etwas komplex aussehen, ist aber mit etwas Übung recht einfach anzuwenden. Außerdem bleiben Sie die ganze Zeit im APEX Standard und müssen so nicht, eine Sonderlocke aufsetzen um einen individuellen Nutzerwunsch umzusetzen.

PDF Creation with direct print dialog

Von Tobias Arnhold → 9.12.2012

This solution is based on the usage of Jasper Reports to create PDF reports inside APEX.

You may had the requirement to create a PDF document with a direct print dialog but there should be no download pop-up dialog or an in-line view appear where the end user explicitly needed to click on the print button. Seems to be a simple problem: Print dialog should immediately appear after PDF creation.

Actualy there is a quite easy workaround available:
Just add this new property under the properties section (for you document) in the iReport builder settings.

/* Property name */
/* Property value */
this.print({bUI: true,bSilent: true,bShrinkToFit: false});
Now every time you create the PDF and open it. The print dialog starts automatically (at least with Adobe PDF).

Next step is to configure a way so that you do not need to open or close the PDF manually.
For that add a new HTML area with the following code on your master page (like page 1).

function refreshPDF(){
var iframe = document.getElementById('region_iframe_pdf'); 
iframe.src = 'f?p=&APP_ID.:2:&SESSION.:::2::';
<!--  iFrame element which leads to page 2 (including our Jasper Reports Call as BeforeHeader Process) -->
<iframe id="region_iframe_pdf" src="#" style="border:0px #FFFFFF none;" name="iFramePDF" scrolling="no" frameborder="0" marginheight="0px" marginwidth="0px" height="1px" width="1px"></iframe>
Now we only need to call the javascript function to refresh our page 2 (which includes our PDF generation):
This could be added as button href link or as a dynamic action.
The generated PDF on page 2 must be an embedded PDF.
The trick is that the iFrame is more or less not visible to the end user and becomes only refreshed when the end user calls the function: refreshPDF

The idea and most of the code comes from my good fellow Sebastian.

Balken Chart (Progress Bar) - Hidden Feature

Von Tobias Arnhold → 8.28.2012
Viele von euch kennen bestimmt die Balken-Grafik die Ihr innerhalb eines APEX Reports erstellen könnt:
Zu diesem Thema gibt es einige sehr gute Anleitungen:

Neben der einfachen Verwendung in den Report Attributes, gibt es auch die Möglichkeit die Charts direkt mit einer APEX_UTIL Funktion zu generieren:
APEX_UTIL Documentation
    p_number         IN NUMBER    DEFAULT NULL,
    p_size           IN NUMBER    DEFAULT 100,
    p_background     IN VARCHAR2  DEFAULT NULL,
    p_bar_background IN VARCHAR2  DEFAULT NULL,
    p_format         IN VARCHAR2  DEFAULT NULL)

In diesem Zusammenhang hat Peter Raganitsch die Generierung einer Balken-Grafik innerhalb eines APEX Items beschrieben:
DOAG Tipps & Tricks: So erstellen Sie mit APEX eine Balkengrafik-Formatmaske in einem Page-Item

Nun hat man oft die Notwendigkeit eine bedingte Anzeige (Beispiel: Ampel) abzubilden. Dies geht auch mit den Balken-Grafiken in APEX. Ich zeige dies mal anhand eines Report Beispiels:
 case when FINISHED_IN_PERCENT <= 30    
      when FINISHED_IN_PERCENT >= 31 and 
           FINISHED_IN_PERCENT <= 70
      when FINISHED_IN_PERCENT >= 71
 end as chart,
APEX-AT-WORK no image

APEX Tabular Forms mit Before Insert Trigger

Von Tobias Arnhold → 8.20.2012
In vielen meiner Projekte nutze ich Tabular Forms. Bei Standardspalten wie "geändert am" oder "geändert von" ist es leider nicht ohne weiteres möglich das SYSDATE und den APP_USER mitzugeben.

Der Grund liegt in den wenigen Einstellmöglichkeiten (bis mindestens APEX 4.1).  Es gibt nur einen Default Wert den ich setzen kann. Dieser wird aber nicht bei einem Update Prozess gezogen und der alte Wert bleibt bestehen.

Eine Lösung stellt hier ein "before insert or update"-Trigger dar:
  :NEW.geaendert_am := sysdate;
  :NEW.geaendert_von := UPPER(NVL(v('APP_USER'),USER));
APEX-AT-WORK no image

Neue Version des SQL Developers (3.2)

Von Tobias Arnhold →
Es gibt eine neue Version des Oracle SQL Developers:

Neben vielen Bugfixes und Usability Erweiterungen, wurde auch eine Administrationsumgebung für den APEX Listener hinzugefügt. Siehe Release Notes - New Features:

Aktuell gibt es eine Early Adopter (Beta) des APEX Listeners in der Version 2.0:

Achja wer es bisher verpasst hat, auch APEX gibt es in einer zweiten Version des Early Adopters zum testen:

Noch ne Info:
Am 26.09 findet eine APEX SIG in Karlsruhe statt. Ich selbst halte auch ein Vortrag:

IR Bug after import of an application with new id

Von Tobias Arnhold → 8.07.2012
Today I discovered a really cruel bug in APEX.
We migrated an APEX 4.0 application into a new workspace with APEX 4.1.
The application got a new application id. The application used a lot of interactive reports. Some of the report columns where created as "Display as Text (based on LOV, escape special characters)".
After the import all LOV assignments where dropped.

Did anyone else discovered the same issue? Is there any bug fix planned in a newer version of APEX. 
In the new workspace we use version
APEX-AT-WORK no image

Getting APEX views

Von Tobias Arnhold → 7.03.2012
There is a easy way getting all APEX views just with a simple select:
select apex_view_name, comments
from apex_dictionary
where column_id = 0
order by 1;

select LPAD (' ', (LEVEL - 1) * 2) ||  apex_view_name s, comments
from (
  select 'ROOT' as apex_view_name, null as comments, null as parent_view
  from dual
  select apex_view_name, comments, nvl(parent_view,'ROOT') as parent_view
  from apex_dictionary
  where column_id = 0
  connect by prior apex_view_name = parent_view
  start with parent_view is null
order SIBLINGS by apex_view_name DESC; 
Current APEX views in version 4.2
**APEX_WORKSPACES Available Application Express (APEX) workspaces
****APEX_WS_DATA_GRID Websheet Data Grid definition
*****APEX_WS_DATA_GRID_COL Report column definitions for Websheet Data Grid columns
***APEX_WORKSPACE_UI_TYPES The UI Types identify the available user interface types of a workspace
***APEX_WORKSPACE_SQL_SCRIPTS Identifies SQL Scripts used to execute SQL and PL/SQL commands
***APEX_WORKSPACE_SESSIONS Application Express (APEX) sessions by workspace and APEX user
***APEX_WORKSPACE_SCHEMAS Database Schemas mapped to APEX workspaces
***APEX_WORKSPACE_GROUP_USERS Application Express (APEX) users and groups relationship
***APEX_WORKSPACE_GROUPS Application Express (APEX) users and groups relationship
***APEX_WORKSPACE_FILES Identifies uploaded files belonging to the workspace in the modplsql or EPG documents table
***APEX_WORKSPACE_DEVELOPERS Application Express (APEX) developers, APEX users with privilege to develop applications
***APEX_WORKSPACE_CLICKS Clicks in Application Express that are tracked by using APEX_UTIL.COUNT_CLICKS
***APEX_WORKSPACE_APEX_USERS Application Express (APEX) users
***APEX_WORKSPACE_ACTIVITY_LOG Page view activity log detail.  One row is logged for each page view for application with logging enabled.
****APEX_WORKSPACE_LOG_SUMMARY_USR Page view activity log summarized by user for the last two weeks
****APEX_WORKSPACE_LOG_SUMMARY Page view activity log summarized by application for the last 14 days
****APEX_WORKSPACE_LOG_ARCHIVE Page view activity is a daily summary of workspace acitivity that is retained until physically purged
***APEX_WORKSPACE_ACCESS_LOG One row is logged for each login attempt.
***APEX_TEAM_TODOS Items that need to get done - i.e. to dos.
***APEX_TEAM_MILESTONES Identifies bugs, also known as software defects.
***APEX_TEAM_FEEDBACK Identifies user feedback.
****APEX_TEAM_FEEDBACK_FOLLOWUP Identifies user feedback followup.
***APEX_TEAM_FEATURES Items that need to get done - i.e. to dos.
***APEX_TEAM_BUGS Identifies bugs, also known as software defects.
***APEX_APPLICATIONS Applications defined in the current workspace or database user.
****APEX_APPL_PLUGIN_SETTINGS Stores the values of custom attribute of scope &quot;application&quot; of a plug-in.
****APEX_APPL_PLUGINS Stores the meta data for the plug-ins of an application.
*****APEX_APPL_PLUGIN_FILES Stores the files like CSS, images, javascript files, ... of a plug-in.
*****APEX_APPL_PLUGIN_EVENTS Stores which events can be triggered by this plug-in. This events are used for binding dynamic actions.
*****APEX_APPL_PLUGIN_ATTRIBUTES Stores the meta data for the dynamic attributes of a plug-in.
******APEX_APPL_PLUGIN_ATTR_VALUES Stores the possible values of a plug-in attribute if it's of type selectlist.
****APEX_APPL_LOAD_TABLE_RULES Identifies a collection of transformation rules that are to be used on the load tables.
****APEX_APPL_LOAD_TABLE_LOOKUPS Identifies a the collection of key lookups of the data loading tables
****APEX_APPL_LOAD_TABLES Identifies a named collection of Application Data loding tables which are used to store the metadata information of the data loading tables
****APEX_APPLICATION_WEB_SERVICES Web Services referenceable from this Application
****APEX_APPLICATION_TREES Identifies a tree control which can be referenced and displayed by creating a region with a source of this tree
****APEX_APPLICATION_TRANS_REPOS Repository of translation strings.  These are populated from the translation seeding process.
****APEX_APPLICATION_TRANS_MAP Application Groups defined per workspace.  Applications can be associated with an application group.
****APEX_APPLICATION_TRANS_DYNAMIC Application dynamic translations.  These are created in the Translation section of Shared Components, and referenced at runtime via the function APEX_LANG.LANG.
****APEX_APPLICATION_TRANSLATIONS Identifies message primary language text and translated text
****APEX_APPLICATION_THEMES Identifies a named collection of Templates
*****APEX_APPL_THEME_DISPLAY_POINTS The Theme Display Points identify the available display points in page and region templates
*****APEX_APPLICATION_THEME_STYLES The Theme Style identifies the CSS file URLs which should be used for a theme
*****APEX_APPLICATION_TEMP_REPORT Identifies the HTML template markup used to render a Report Headings and Rows
*****APEX_APPLICATION_TEMP_REGION Identifies a regions HTML template display attributes
*****APEX_APPLICATION_TEMP_POPUPLOV Identifies the HTML template markup and some functionality of all Popup List of Values controls for this application
*****APEX_APPLICATION_TEMP_PAGE The Page Template which identifies the HTML used to organized and render a page content
*****APEX_APPLICATION_TEMP_LIST Identifies HTML template markup used to render a List with List Elements
*****APEX_APPLICATION_TEMP_LABEL Identifies a Page Item Label HTML template display attributes
*****APEX_APPLICATION_TEMP_CALENDAR Identifies the HTML template markup used to display a Calendar
*****APEX_APPLICATION_TEMP_BUTTON Identifies the HTML template markup used to display a Button
*****APEX_APPLICATION_TEMP_BC Identifies the HTML template markup used to render a Breadcrumb
*****APEX_APPLICATION_TEMPLATES Identifies reference counts for templates of all types
****APEX_APPLICATION_TABS Identifies a set of tabs collected into tab sets which are associated with a Standard Tab Entry
****APEX_APPLICATION_SUPP_OBJECTS Identifies the Supporting Object installation messages
*****APEX_APPLICATION_SUPP_OBJ_SCR Identifies the Supporting Object installation SQL Scripts
*****APEX_APPLICATION_SUPP_OBJ_CHCK Identifies the Supporting Object pre-installation checks to ensure the database is compatible with the objects to be installed
*****APEX_APPLICATION_SUPP_OBJ_BOPT Identifies the Application Build Options that will be exposed to the Supporting Object installation
****APEX_APPLICATION_SUBSTITUTIONS Application level definitions of substitution strings.
****APEX_APPLICATION_SHORTCUTS Identifies Application Shortcuts which can be referenced &quot;MY_SHORTCUT&quot; syntax
****APEX_APPLICATION_PROCESSES Identifies Application Processes which can run for every page, on login or upon demand
****APEX_APPLICATION_PARENT_TABS Identifies a collection of tabs called a Tab Set.  Each tab is part of a tab set and can be current for one or more pages.  Each tab can also have a corresponding Parent Tab if two levels of Tabs are defined.
****APEX_APPLICATION_PAGES A Page definition is the basic building block of page. Page components including regions, items, buttons, computations, branches, validations, and processes further define the definition of a page.
*****APEX_APPLICATION_PAGE_VAL Identifies Validations associated with an Application Page
*****APEX_APPLICATION_PAGE_REGIONS Identifies a content container associated with a Page and displayed within a position defined by the Page Template
******APEX_APPLICATION_PAGE_TREES Identifies a tree control which can be referenced and displayed by creating a region with a source of this tree
******APEX_APPLICATION_PAGE_RPT Printing attributes for regions that are reports
*******APEX_APPLICATION_PAGE_RPT_COLS Report column definitions used for report regions
******APEX_APPLICATION_PAGE_ITEMS Identifies Page Items which are used to render HTML form content.  Items automatically maintain session state which can be accessed using bind variables or substitution stings.
******APEX_APPLICATION_PAGE_FLASH_CH Identifies a Flash chart associated with a Page and Region
*******APEX_APPLICATION_PAGE_FLASH_S Identifies the Flash chart series which comprise a Flash chart
******APEX_APPLICATION_PAGE_FLASH5 Identifies a Flash chart 5 chart associated with a Page and Region
*******APEX_APPLICATION_PAGE_FLASH5_S Identifies the Flash chart series which comprise a Flash chart
******APEX_APPLICATION_PAGE_BUTTONS Identifies buttons associated with a Page and Region
*****APEX_APPLICATION_PAGE_PROC Identifies SQL or PL/SQL processing associated with a page
*****APEX_APPLICATION_PAGE_MAP Identifies the full breadcrumb path for each page with a breadcrumb entry
*****APEX_APPLICATION_PAGE_GROUPS Identifies page groups
*****APEX_APPLICATION_PAGE_DB_ITEMS Identifies Page Items which are associated with Database Table Columns.  This view represents a subset of the items in the APEX_APPLICATION_PAGE_ITEMS view.
*****APEX_APPLICATION_PAGE_DA Identifies Dynamic Actions associated with a Page
******APEX_APPLICATION_PAGE_DA_ACTS Identifies the Actions of a Dynamic Action associated with a Page
*****APEX_APPLICATION_PAGE_COMP Identifies the computation of Item Session State
*****APEX_APPLICATION_PAGE_BRANCHES Identifies branch processing associated with a page.  A branch is a directive to navigate to a page or URL which is run at the conclusion of page accept processing.
****APEX_APPLICATION_NAV_BAR Identifies navigation bar entries displayed on pages that use a Page Template that include a #NAVIGATION_BAR# substitution string
****APEX_APPLICATION_LOVS Identifies a shared list of values that can be referenced by a Page Item or Report Column
*****APEX_APPLICATION_LOV_ENTRIES Identifies the List of Values Entries which comprise a shared List of Values
****APEX_APPLICATION_LISTS Identifies a named collection of Application List Entries which can be included on any page using a region of type List.  Display attributes are controlled using a List Template.
*****APEX_APPLICATION_LIST_ENTRIES Identifies the List Entries which define a List.  List Entries can be hierarchical or flat.
****APEX_APPLICATION_ITEMS Identifies Application Items used to maintain session state that are not associated with a page
****APEX_APPLICATION_GROUPS Application Groups defined per workspace.  Applications can be associated with an application group.
****APEX_APPLICATION_COMPUTATIONS Identifies Application Computations which can run for every page or on login
****APEX_APPLICATION_CACHING Applications defined in the current workspace or database user.
****APEX_APPLICATION_BUILD_OPTIONS Identifies Build Options available to an application
****APEX_APPLICATION_BREADCRUMBS Identifies the definition of a collection of Breadcrumb Entries which are used to identify a page Hierarchy
****APEX_APPLICATION_BC_ENTRIES Identifies Breadcrumb Entries which map to a Page and identify a pages parent
****APEX_APPLICATION_AUTHORIZATION Identifies Authorization Schemes which can be applied at the application, page or component level
****APEX_APPLICATION_AUTH Identifies the available Authentication Schemes defined for an Application
****APEX_APPLICATION_ALL_AUTH All authorization schemes for all components by Application
**APEX_UI_DEFAULTS_TABLES The User Interface Defaults for the tables within this schema.  Used by the wizards when generating applications.
**APEX_UI_DEFAULTS_LOV_DATA If you create a form, report, or tabular form that includes this column and if the appropriate Display As Type is set to use a list of values (Radio Group or Select List) then a Named List of Values will be created within the application and will be referenced by the resulting item or report column.
**APEX_UI_DEFAULTS_GROUPS The User Interface Defaults for the groups within the tables in this schema.  Used by the wizards when generating applications.
**APEX_UI_DEFAULTS_COLUMNS The User Interface Defaults for the columns within this schema.
**APEX_UI_DEFAULTS_ATTR_DICT The Attribute Dictionary is specific to a workspace.  It is part of User Interface Defaults and can be used in report and form creation.
**APEX_THEMES List of APEX built-in, public and workspace themes

First release of the PL-jrxml2pdf - Generate iReport PDFs with PL/SQL only

Von Tobias Arnhold → 6.29.2012
A while ago I wrote about and development idea creating iReport (Jasper Report) PDF-files directly inside APEX (PL/SQL).

Andreas Weiden the brain and developer behind this project finally released the first version:

I helped him as a beta tester and I must say what he did is really amazing. Even in the beta state it worked really good. It supports a lot of functionality from iReport so I could use it even with complex reports. If you want to try it now then here is the download link:

Btw.: The project itself is open source.
APEX-AT-WORK no image

Tabular Form - Validation issue

Von Tobias Arnhold → 6.28.2012
I'm currently have some questions about TABULAR FORM validations. I my questions inside the APEX forum:
1. I only want that a select list entry get selected ones not several times. It must be some kind of validation check. Is there any example available?
2. If a select list value is already set within a row then this id should not be displayed in any other row anymore.

For question one I found an answer myself. This is a validation based on the idea from Denes Kubicek.
-- Validation of Type: Function Returning Error Text 

   l_facility_ids varchar2(32000);
   l_facility_bez varchar2(200);
   l_error   VARCHAR2 (4000);
   FOR i IN 1 .. apex_application.g_f07.COUNT -- select list with facilities
      IF instr(':'||l_facility_ids||':',':'||apex_application.g_f07(i)||':')>0
         select description into l_facility_bez from facilities where id = apex_application.g_f07(i);
         l_error :=
            || '</br>'
            || 'Row '
            || i
            || ': facility"' || l_facility_bez  ||'" is already selected.'; 
      END IF;

      l_facility_ids := l_facility_ids ||':'|| apex_application.g_f07(i);

   RETURN LTRIM (l_error, '</br>');
Is there an workaround for question two available?

Easy table export to XLS (based on HTML)

Von Tobias Arnhold → 6.22.2012
There is a quite easy way to generate nice looking Excel files based on your APEX reports. It doesn't require any special printing engine and should work even with Excel 2003.

It supports all kind of report views as long as they are build as tables. In my case I created an export based on this pivot table output:
Of course my pivot table is build up as plug-in and selects data from my project data tables.

All you need to do is to get the table element (including all html code) from your rendered page and write it into a blank XLS-file.
Those are the steps you have to follow:

1. Create a new APEX item: textarea
Textarea - P1_EXPORT_DATA
HTML Form Element Attributes: style="display:none;"

2. Create a button
Button: P1_EXPORT
Action: Defined by Dynamic Action

3. Edit your table region
Info: Check your report template. Your table element should look similar to this:
<table border="0" cellpadding="0" cellspacing="0" summary="" id="report_#REGION_STATIC_ID#"

3. Create a Dynamic Action
Event: Click
Selection Type: Button
Button: P1_EXPORT
Actions: Execute Javascript Code & Submit Page
 - Execute Javascript Code:
 - Submit Page:

4. Create a new Branch
Branch to Page or URL - OnSubmit
Page: 1
Request: Export
Conditions - PL/SQL

5. Add a new process
Process: Export
Type: PL/SQL
Process Point: On Load - Before Header
     l_mime        VARCHAR2 (255);
     l_length      NUMBER;
     l_file_name   VARCHAR2 (2000);
     lob_loc       BLOB;
     dbms_lob.createtemporary(lob_loc, TRUE);
     OWA_UTIL.mime_header ('application/xls', FALSE);
     HTP.p ('Content-Length: ' || DBMS_LOB.GETLENGTH(lob_loc));
     HTP.p ('Content-Disposition: attachment; filename="my_xls_doc.xls"');

The output will look like this:

It also supports images but they must include the complete URL.

Btw.: APEX 4.2 EA is out. New Application Builder looks mostly nice but I think the region headers are far to big. Check it out yourself:
New feature list:

Select table and column comments (Oracle SQL)

Von Tobias Arnhold → 6.07.2012
My default development client is the Oracle SQL Developer. If I compare it with TOAD it is slim, free to use and includes a table modeling area.
One thing I don't like is the ability to see table comments. To find this information you need to click on the table inside the table view ("Connections" > #MY_CON2# > Tables), then on tab: "Details", scroll to column "Comments" and finally double click on the comment field to see all details.

Instead of going this way each time you can use this select instead:
-- All tables inside my user
select table_name, comments 
from user_tab_comments
where table_name = :MY_TABLE;

-- All tables for all users:
select table_name, comments 
from all_tab_comments 
where owner = :MY_USER
and   table_name = :MY_TABLE;
To select the column comments use this select: (idea comes from bdelmee)
select TABLE_NAME,
    case when K.DATA_SCALE is not null then '(' || K.DATA_PRECISION || ',' || K.DATA_SCALE || ')'
      when K.DATA_PRECISION is not null then '(' || K.DATA_PRECISION || ')'
      when K.DATA_LENGTH is not null and K.DATA_TYPE like '%CHAR%' then '(' || K.DATA_LENGTH || ')'
    end DATA_TYPE,
from user_col_comments C join user_tab_cols K
where table_name = :MY_TABLE
Now we join both selects together and get all information we need:
  decode(column_id,0,TABLE_NAME,null) as TABLE_NAME,
  decode(column_id,0,null,column_id)  as COLUMN_ID,
from (
  select cc.TABLE_NAME,
    tc.NULLABLE, tc.DATA_TYPE || 
      case when tc.DATA_SCALE is not null then '(' || tc.DATA_PRECISION || ',' || tc.DATA_SCALE || ')'
        when tc.DATA_PRECISION is not null then '(' || tc.DATA_PRECISION || ')'
        when tc.DATA_LENGTH is not null and tc.DATA_TYPE like '%CHAR%' then '(' || tc.DATA_LENGTH || ')'
      end DATA_TYPE,
  from user_col_comments cc
  INNER JOIN user_tab_cols tc ON (cc.TABLE_NAME = tc.TABLE_NAME and cc.TABLE_NAME = tc.TABLE_NAME and cc.COLUMN_NAME = tc.COLUMN_NAME) 
  select tab.table_name as TABLE_NAME, 
         0 as COLUMN_ID, '' as COLUMN_NAME,
         '' as NULLABLE, '' as DATA_TYPE,
         tab.comments as COMMENTS
  from user_tab_comments tab
where table_name = UPPER(:MY_TABLE)
order by table_name, column_id  ;
That's it.

APEX IR - Date Filter usage like String columns

Von Tobias Arnhold → 5.25.2012
If you want to filter date values like this:

Then extend the code to your date columns (inside the region source) like this:
  My date column name is: IMPORT_DATE
  decode(IMPORT_DATE,null,null,'<!-- ' || to_char ( IMPORT_DATE, 'yyyymmdd' ) || ' -->' || IMPORT_DATE) as IMPORT_DATE

For more information take a look into this forum thread:
APEX-AT-WORK no image

APEX character validation whichs shows all not allowed characters

Von Tobias Arnhold → 5.18.2012
This time I will present a solution for a character validation on the APEX item level.
Normally you create a simple validation based on a REGULAR EXPRESSIONS which checks for a set of characters which are allowed inside your item. If there are characters inside your item which are not in the list an error will be displayed. Unfortunately the end user doesn't know the exact character which is not allowed. If you have a text-box and you allow 500 characters then this will be a problem for the usability of your application.  

Create a new validation as:
"Function Returning Error Text"
 v_text varchar2(16000) := :P1_TEXT;
 v_text_cnt number;
 v_text_err varchar2(1000);
 select count(*) into v_text_cnt from dual
 REGEXP_LIKE (v_text,'^[[:alpha:] .,-:;!?[:digit:]]+$');
 /* List of allowed characters */

 if v_text_cnt = 0 then
   select regexp_replace (v_text,'[[:alpha:] .,-:;!?[:digit:]]+'
      )   invalid_characters into v_text_err
   from dual;

   return 'Error. The following characters are not allowed: ' || v_text_err;
 end if;

The end user can search for the wrong character and fix it himself.

 I added a forum entry to it:
APEX-AT-WORK no image

Different ways updating a table with sub select (join table)

Von Tobias Arnhold → 5.15.2012
/* 1. Standard sub query */ 
UPDATE tbl_employees x
   SET department = (
         FROM tbl_department y
        WHERE y.dept_id = x.dept_id);

/* 2. Sub query with exist clause */
UPDATE tbl_employees x
   SET department = (
         FROM tbl_department 
        WHERE y.dept_id = x.dept_id)
         FROM tbl_department y
        WHERE y.dept_id = x.dept_id);

/* 3. Updatable view (needs foreign key connection) */
SELECT x.department AS old_val, AS new_val
  FROM tbl_employees x, tbl_department y
 WHERE y.dept_id = x.dept_id)
   SET old_val = new_val;

/* 4. Update trough merge statement */
MERGE INTO tbl_employees x
 USING (SELECT name, dept_id
          FROM tbl_department y ) y
    ON (y.dept_id = x.dept_id)
WHEN matched THEN
   SET x.department =;
APEX-AT-WORK no image

Shift Left example with Oracle SQL

Von Tobias Arnhold → 5.13.2012
I'm currently working on a integration of the jQuery Real Person (Captcha) plugin into APEX.

On the server side I had to execute a shift left. Which normally works like this:
5381<< 5
With Oracle SQL you can do like this:
select 5381*power(2,5) from dual;

I had some issues with really big numbers. The function acts different to my Firefox browser Javascript function.

APEX-AT-WORK no image

Cloud Plugin Plugin Extension

Von Tobias Arnhold → 5.10.2012
I really like the Label Cloud Plugin by Carsten Czarski but a customer complained that there are not so much visual differences by words when the occurences are very similar.
For example you have 20 words inside your cloud:
10 words with a value of 1
04 words with a value of 2
02 words with a value of 4
02 words with a value of 8
01 word with a value of 10
01 word with a value of 12

What I did to get more differences into the cloud was a "font-weight:bold" for each second word and I added a padding to make the words not standing to close to each other.

How To: 
Inside your label cloud region add a static id.

Region Footer:
APEX-AT-WORK no image

APEX simple cookie example

Von Tobias Arnhold → 5.09.2012
This is an example for the use of a simple cookie which gets created after submit when click on a button. Afterwards a region inside my page shouldn't be shown anymore. The cookie itself is valid for 30 days and will save the value "1".

Page process:
 owa_util.mime_header('text/html', FALSE);
     expires => sysdate + 30);

 apex_application.g_unrecoverable_error := true;
 exception when others then null;
Update code from Patrick (for more information check the comments):
owa_util.mime_header('text/html', FALSE);
value => '1',
expires => sysdate + 30 );

apex_util.redirect_url (
p_url => 'f?p=&APP_ID.:1:' || :SESSION,
p_reset_htp_buffer => false );
The condition checks for the cookie and reads the value. If it is NULL then the region will be displayed.
Region Condition:
-- Type: PL/SQL Function Body Returning a Boolean
 cookie  OWA_COOKIE.cookie;  

   cookie := OWA_COOKIE.get('APEX_AT_WORK_COOKIE');

   if cookie.vals.First IS NULL then 
    return true;
    return false; 
   end if;


APEX login example for more then one application: Update 19.01.2015
I case you use the cookie technique with the safari browser. Be aware that the first character is not allowed to be a sepecial character like space or comma. Otherwise the cookie stays emtpy.
APEX-AT-WORK no image

Using jQuery and Regular Expression to validate form fields

Von Tobias Arnhold → 5.03.2012
This is a simple example to validate an APEX item with regular expressions (on client side):
v_text = $('#P1_MY_ITEM').val();          
v_regex = /^[a-zA-Z0-9_ .,-:;!?&\(\)"\t\r\n]+$/;   
/* only number: /^[0-9]+$/; */
/* only character: /^[a-zA-Z]+$/; */
Important links to the topic:
APEX-AT-WORK no image

APEX report column with %-sign

Von Tobias Arnhold → 5.01.2012
If you want to beautify your number column inside an APEX report with a percent-sign then most of you would do like this:  
select column_name || '%' as column_name
from my_table

This creates the correct output but the sort will not work anymore as expected because a type conversion will automatically be applied. It is not longer a number column and the sort will be assigned as it would be a character column.
Luckily there is a easy workaround for this. Let the select as it is:
select column_name 
from my_table

And add this under the "Column Attributes">"Columns Formatting">"HTML Expression": #COLUMN_NAME#% 

That's it.  

Update 09.05.2012 You might want to do the same with an Interactive Report then this trick could help you:
APEX-AT-WORK no image

Customized APEX workspace login

Von Tobias Arnhold → 4.27.2012
There is a easy way to set up your APEX workspace login URL so that you reach a special page inside your application builder.
The following URL example will let you jump automatically to the SQL Commands interface:,F4550_P1_COMPANY,F4550_P1_USERNAME:\f?p=4500|1003\,WS_TEST,APEX_USER

FSP_AFTER_LOGIN_URL - Defines the URL you want to jump to.
F4550_P1_COMPANY - Workspace name
F4550_P1_USERNAME - APEX developer account

Important Links:

Export Application - \f?p=4000|4900\

SQL Workshop - \f?p=4500|3002\

Object Browser - \f?p=4500|1001\
SQL Commands - \f?p=4500|1003\
Data Upload und Download - \f?p=4300|1\
Generate DDL - \f?p=4500|12\

Team Development: \f?p=4800|4000\

Administration: \f?p=4350|1\
Page Views by Calender - \f?p=4350|27\

APEX-AT-WORK no image

Using IReport without the Jasper Report Server inside APEX

Von Tobias Arnhold → 4.17.2012
There is a nice development going on to include the created IReport (from Jasper Reports) templates with a PL/SQL PDF Generator. For more information take look into this blog post:
APEX-AT-WORK no image

Client IP Adresse in APEX auslesen

Von Tobias Arnhold → 4.09.2012
Um die Client-IP-Adresse mit Hilfe von PL/SQL auszulesen, können Sie zwei unterschiedliche Prozeduren verwenden:
from dual;

from dual;
Das Ganze funktioniert super solange Sie keinen vorgeschalteten Proxy Server verwenden! Aber auch dafür gibt es einen Workaround. Stellen Sie folgende Einstellung in Ihrer http.conf ein:
RewriteCond %{REMOTE_ADDR} (.*)
RewriteRule .* - [E=REMOTEA:%1]
RequestHeader set X-Oracle-Cache-User "%{REMOTEA}e"

# Außerdem müssen folgende Einstellung mit gesetzt werden:
# 1. Konfiguration der RewriteRule muss in einem VirtualHost hinterlegt sein:

# 2. Rewrite muss aktiviert werden
RewriteEngine on

# 3. ModHeader muss mit geladen werden
LoadModule headers_module modules/
Die Lösung wurde von Dietmar Aust im Oracle APEX Forum auch noch tief greifender beschrieben: 
Weitere Forum Einträge zu Thema:
Das auslesen der Info in PL/SQL sieht dann so aus:
from dual;
Info: Es kann vorkommen das andere Einstellungen verhindern das die Variable richtig durchgeroutet wird.
APEX-AT-WORK no image

Erster DOAG Artikel veröffentlicht

Von Tobias Arnhold → 4.03.2012
Hallo Zusammen,

in der aktuellen DOAG Ausgabe findet Ihr viele interessante Artikel zu Oracle APEX. Einer dieser Artikel handelt um APEX Plugins und stammt aus meiner Feder. Gönnt euch eine Minute Auszeit und schaut einfach mal rein.

Viel Spaß beim lesen.
APEX-AT-WORK no image

APEX Plugin Competition

Von Tobias Arnhold → 3.30.2012
A few days ago (Tuesday March 27th 2012) started the first ODTUG APEX Plugin Competition ever. This is a chance for everybody to submit their self created Plugins to get reviewed, judged and maybe win one of the many Prizes available.
Plugins will be judged upon skill level of its creator. That means beginners and experts will not be in the same category, to make it fair to everyone.
Read all the Details on Martins Blog and see the rules on the ODTUG Page.
To participate in the competition go to and login/register to submit your Plugin.
Judges for this Competition are: Martin, Dan, Peter, Learco, John, Dimitri, myself and of course Patrick as the inventor of Plugins.
Don’t hesitate to submit your Plugin, even if you think you won’t make the first prize. It’s also about getting a Review and maybe valuable Feedback by the judges.

Dynamic Actions als Nadelöhr

Von Tobias Arnhold → 2.28.2012
Eines Vorweg: 
Dynamic Actions sind genial und machen das dynamische agieren mit dem Endnutzer ohne Seiten-Submit wunderbar einfach. Nur leider können Dynamic Actions Ihren Browser auch leicht performance seitig ins wanken bringen. Nehmlich dann, wenn Sie sehr viele Dynamic Actions gleichzeitig verwenden.
Hier ein Beispiel meiner Plug-in App (Seite 1):
Ich verwende 7 unterschiedliche Dynamic Actions diese wiederum unterschiedliche Aktionen beinhalten:

Nun ist die Frage, wie kann ich die Performance überhaupt verbessern?
Das Zauberwort heißt: "Condition"
Sie können Dynamic Actions wie auch viele andere APEX Elemente bedingt anzeigen. Dies ist natürlich nicht immer möglich, aber man sollte es im Auge behalten, da jede geladene Dynamic Action auf Kosten der Endnutzer Performance geht.

Das Problem ist, Sie können sehr gut SQL und PL/SQL Code auf Performance-Probleme hin analysieren. Bei Dynamic Actions können Sie dies nur bedingt tun, da viele Client-PC's unterschiedliche Konfigurationen haben die ein späteres Debugging schwierig gestalten.
Tip: Zum Debuggen von Dynamic Actions verwende ich Firefox mit Firebug.

Wo liegen die größten Client Nadelöhre (Im Bezug auf Dynamic Actions):
- Client-Rechner (Hardware)
- Browser (Software)

Warum Browser?
Haben Sie sich jemals gefragt warum es Javascript Speed Tester gibt und warum die Browser Hersteller besonders mit tollen Javascript-Engines Werbung machen. Javascript wird wie in APEX in einem immer größerem Umfang bei der Webentwicklung genutzt. Das hat zur Folge, dass die Browser den JS-Code möglichst schnell abarbeiten müssen, um so die Usability der Webseiten flüssig zu halten. Sie sollten daher immer darauf achten die Endnutzer Browserauswahl einzuschränken. Dies ist aus CSS Gründen schon von Vorteil und wird durch die Nutzung von komplexen Javascript Code / Dynamic Actions noch verstärkt.

Dynamic Actions sind ein mächtiges Werkzeug das wohl überlegt verwendet werden muss. Genauso wie Sie bei der Entwicklung von SQL und PL/SQL Code auf Performance Engpässe achten müssen, ist auch bei der Entwicklung von Dynamic Actions die verwendete Anzahl abhängig von den Anforderungen im Rahmen zu halten. Ich rate im Normalfall nicht mehr als 10 Dynamic Actions pro Seite zu verwenden.

Ein paar Links zum Thema:
Dynamic Actions
AJAX-Technologie ganz einfach: Dynamic Actions  -
Building Dynamic Actions in Oracle Application Express 4.0 -

JavaScript engine -
Geschwindigkeit und Speicherverbrauch aktueller Browser -