Recent Posts

Oracle APEX Meetup in Stockholm 16.08.

Von Tobias Arnhold → 7.19.2016
In my summer holiday, this year in Stockholm (Sweden), I will make a stop and present two APEX topics in the "Stockholm Oracle Meetup group".

The topics will be:

Working with interactive SVG graphics in APEX 
Adding SVG graphics inside individual business applications is quite in common. Because those graphics can increase the usability and even more important it creates a completely new way to visualize data inside a browser app. In this presentation he will show us how one can integrate SVG charts and how to use the possibilities of SVG elements to make every static image interactive with only a few lines of code.



APEX Dashboard Competition 
At the beginning of the year the German community announced the "APEX Dashboard Competition". The goal was to create a nice looking dashboard application. This presentation will give you an impression about the best 10 applications and their individual solutions.


If you are interested to see what APEX is capable of then join the presentation on meetup.com.
Thanks to Mathias Magnusson for organizing this great event. Hope to meet as much APEX enthusiasts as possible. :)

Interactive Report with a Font Awesome edit icon

Von Tobias Arnhold → 7.01.2016
Most of you know the default APEX edit icons you can choose from inside your Interactive Report.
Basically you use them to link to a detail page.


The icon source looks like this.


A typical result page.


Two things I would like to change:
 1. The Icon should be a nice looking car symbol from the Font Awesome library.
 2. The column width should be as small as the icon and not relative to the page width.

We change the pencil.png towards the nice looking automobile icon from Font Awesome.
Code:  
<span class="fa fa-automobile"></span> 
 

Then we add a little CSS snippet to the page inline CSS.
Code:
.minimize-first-table-col td:first-child{
  width: 1%;
  overflow: visible;
  font-size:120%;
  padding: 8px;
}



And as a last step we add the class minimize-first-table-col inside the Interactive Report > Appearance > CSS Classes.


The result will looks like this.


Reset Interactive Report (IR)

Von Tobias Arnhold → 6.30.2016
Resetting an Interactive Report (IR) can be done in 4 different ways.
User Reset, URL Link, Page Process, Dynamic Action

First of all
It is always a good start to set up a specific static report id.


1.  User Reset
Not much to say.


1. URL Link

f?p=&APP_ID.:1:&APP_SESSION.:::RIR,CIR:

Cache definition:
- RIR: reset IR to primary report
- CIR: reset IR to primary report but with custom columns
- RP: reset IR pagination.

More details about the difference of RIR and CIR can be found here:
Apex Interactive Report: The difference between CIR and RIR

Basically all you need to know about the URL options in an IR can be found in the documentation:
Application Express Application Builder User's Guide


Also be aware that you can use several IR since APEX 5 on one page. Filters must now be applied in a specific syntax:
IR[region static ID]_

2. Page Process (Before Header)
DECLARE
  v_region_id APEX_APPLICATION_PAGE_REGIONS.REGION_ID%TYPE;
BEGIN

  SELECT region_id INTO v_region_id
  FROM APEX_APPLICATION_PAGE_REGIONS
  WHERE application_id = :APP_ID
  AND page_id = 1
  AND static_id = 'IR_RO_REPORT_ID'; -- Static ID of your IR

  APEX_IR.RESET_REPORT(
   P_page_id => 1,
   P_region_id => v_region_id,
   P_report_id => NULL
  );

END;

3. Dynamic Action
Create a new Dynamic Action.
Add a TRUE action of type "Execute PL/SQL code"  and use the same code as above.
And finally add another TRUE action to refresh the IR.

If you still need more information then take a look here:
Reset an Interactive Report (IR)

Oracle Spatial (Teil 4) - Unterschiedliche Koordinaten Punkte zu einer Linie zusammenführen

Von Tobias Arnhold → 6.21.2016
Vor kurzem musste ich Daten aus einer Excel-Liste in das SDO_GEOMETRY Format bringen. Leider hatte die Excelliste einen Haken.

Die Anforderungen:
- Es musste eine Linie (Typ 2002) im GK3 Format (31467) aus VON und NACH Punkten generiert werden.
- Die Excel-Liste hatte die Punkte entweder als GK3 oder als WGS84 Format hinterlegt.

Nach Import der Daten sah meine Quelltabelle dann so aus:


Und hier das Select zur richtigen Transformation der Geo-Daten ins SDO_GEOMETRY Format:
SELECT 
     SDOP.ID,
     SDOP.VON_NAME,
     SDOP.NACH_NAME,

     /* Linie generieren */
     MDSYS.SDO_GEOMETRY(
        2002, /* Linie */
        31467, /* GK3 */
        NULL,
        MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),
        MDSYS.SDO_ORDINATE_ARRAY(
            SDOP.VON_GEO_GK3.SDO_POINT.X,
            SDOP.VON_GEO_GK3.SDO_POINT.Y,
            SDOP.NACH_GEO_GK3.SDO_POINT.X,
            SDOP.NACH_GEO_GK3.SDO_POINT.Y
        )
     ) AS GEO_GK3,

     /* Entfernung berechnen */
     ROUND(SDO_GEOM.SDO_DISTANCE(
           GEOM1 => SDOP.VON_GEO_GK3,
           GEOM2 => SDOP.NACH_GEO_GK3,
           TOL => 5,
           UNIT => 'unit=KM'
     ),3) ENTFERNUNG
FROM (
      /* Berechnung der SDO Punkte */
      SELECT
        ID,
        VON_NAME,
        NACH_NAME,

        /* VON: Check ob GK3 oder WGS84 und Vereinheitlichung ins GK3 Format*/
        CASE 
         WHEN VON_GK3_X IS NOT NULL 
         THEN
            MDSYS.SDO_GEOMETRY (
                      2001, -- Zweidimensionaler Punkt
                      31467, -- Typ: GK3
                      SDO_POINT_TYPE(
                          X => VON_GK3_X, 
                          Y => VON_GK3_Y,
                          Z => NULL
                      ), 
                      NULL,
                      NULL
            ) 
          ELSE
            SDO_CS.TRANSFORM( 
              MDSYS.SDO_GEOMETRY (
                2001, -- Zweidimensionaler Punkt
                8307, -- Typ: WGS84
                SDO_POINT_TYPE(
                    X => VON_WGS84_X,  -- Längengrad / Longitude / Ost
                    Y => VON_WGS84_Y,  -- Breitengrad / Latitude / Nord
                    Z => NULL
                ), 
                NULL,
                NULL
              ),
             31467 -- Umwandlung in GK3
             )
        END AS VON_GEO_GK3,

        /* NACH: Check ob GK3 oder WGS84 und Vereinheitlichung ins GK3 Format*/
        CASE 
         WHEN NACH_GK3_X IS NOT NULL 
         THEN
            MDSYS.SDO_GEOMETRY (
                      2001, -- Zweidimensionaler Punkt
                      31467, -- Typ: GK3
                      SDO_POINT_TYPE(
                          X => NACH_GK3_X, 
                          Y => NACH_GK3_Y,
                          Z => NULL
                      ), 
                      NULL,
                      NULL
            ) 
          ELSE
            SDO_CS.TRANSFORM( 
              MDSYS.SDO_GEOMETRY (
                2001, -- Zweidimensionaler Punkt
                8307, -- Typ: WGS84
                SDO_POINT_TYPE(
                    X => NACH_WGS84_X,  -- Längengrad / Longitude / Ost
                    Y => NACH_WGS84_Y,  -- Breitengrad / Latitude / Nord
                    Z => NULL
                ), 
                NULL,
                NULL
              ),
             31467 -- Umwandlung in GK3
             )
        END AS NACH_GEO_GK3

      FROM GEO_MEINE_DATEN
) SDOP
Mit dem Ergebnis:

APEX Master (Interactive Report) - Detail (Modal Dialog) Form: Conditional Column Link

Von Tobias Arnhold → 6.15.2016

Since APEX 5 it is much easier to create master-detail pages with modal dialogs. But there is still no declarative way to create a conditional row based column link.

This blog post will show you a way how to create a conditional row based master - detail page.

1. We need some sample data:
WITH MY_DATA AS
(
select 1 as ID, 'APEX Connect' as name, 1 as CONDITIONAL_COL from DUAL
union all
select 2 as ID, 'KScope' as name, 0 as CONDITIONAL_COL from DUAL
union all
select 3 as ID, 'DOAG' as name, 1 as CONDITIONAL_COL from DUAL
union all
select 4 as ID, 'APEX-FRA Meetup Group' as name, 1 as CONDITIONAL_COL from DUAL
union all
select 5 as ID, 'Oracle World' as name, 0 as CONDITIONAL_COL from DUAL
)
select 
  ID,
  NAME,
  case when nvl(CONDITIONAL_COL,99) = 1 then
    'inline-block'
  else 'none' end as CSS,
  case when nvl(CONDITIONAL_COL,99) = 1 then
    'Visited conference'
  else 'Not visited yet' end as DETAIL_COL
from MY_DATA
As you can see the column CONDITIONAL_COL defines the conditional appearance.
CONDITIONAL_COL = 1 means show a detail button.
CONDITIONAL_COL = 0 means no detail button.

2. Now we can create a APEX master detail page where the "Page Mode" of the detail page is set to:
"Modal Dialog".


The master page gets an Interactive Report with the above select statement.

3. Now we need to configure the column attributes inside the Interactive Report:
Columns ID and NAME with be displayed as of type: "Plain Text".
Column CSS will not be displayed: "Hidden Column"
Column DETAIL_COL will be displayed as of type: "Link"
 - Target: 
   - Page: 2
   - Set Items: P2_ID - #ID#
 - Link Text: <span class="fa fa-search-plus" style="font-size: 160%"></span>
 - Link Attribues: class="a-Button" style="display:#CSS#;"
 We created a custom link for our detail page.


And the functionality to display the detail button conditionally is set by the column "CSS".


The result looks like this:

You can even filter the button by some readable data:


Security issue:
On the detail page you need a validation to check if the user is allowed to edit the data of the transmitted ID. An alternative way is to update the select so that no ID value will be generated if the user has no rights.
Example:
WITH MY_DATA AS
select 
  case when nvl(CONDITIONAL_COL,99) = 1 then
       ID
  else NULL end as ID,
  ID as ID_DISPLAY
  NAME,
  case when nvl(CONDITIONAL_COL,99) = 1 then
    'inline-block'
  else 'none' end as CSS,
  case when nvl(CONDITIONAL_COL,99) = 1 then
    'Visited conference'
  else 'Not visited yet' end as DETAIL_COL
from MY_DATA

Using dynamic tooltips in your Interactive Report

Von Tobias Arnhold → 5.31.2016

Inside an Interactive Report (IR) I had a comment column. The comments in this column could become really large and the users wanted the comments to be automatically trimmed if more then 60 characters were displayed. If the user moved the mouse above a trimmed comment then a tooltip should be display including all comment text.

My first idea was to check for existing plugins which could do this job for me. So I searched on apex.world and found the plugin called "APEX Tooltip" developed by Daniel Hochleitner.

The plugin looked great from what I could see in the example application. When I tried it in my application I found out that all comments must be applied manually in the dynamic action.
Not exactly what I needed. :)

Luckily it was really easy to extend the plugin in a way that I could use it in my IR. To achieve what I need I had to update the javascript file: apextooltip.js
// APEX Tooltip functions
// Author: Daniel Hochleitner
// Updated by Tobias Arnhold
// Version: 1.1

// global namespace
var apexTooltip = {
  // parse string to boolean
  parseBoolean: function(pString) {
    var pBoolean;
    if (pString.toLowerCase() == 'true') {
      pBoolean = true;
    }
    if (pString.toLowerCase() == 'false') {
      pBoolean = false;
    }
    if (!(pString.toLowerCase() == 'true') && !(pString.toLowerCase() == 'false')) {
      pBoolean = undefined;
    }
    return pBoolean;
  },
  // function that gets called from plugin
  showTooltip: function() {
    // plugin attributes
    var daThis = this;
    var vElementsArray = daThis.affectedElements;
    var vTheme = daThis.action.attribute01;
    var vContent = daThis.action.attribute02;
    var vContentAsHTML = apexTooltip.parseBoolean(daThis.action.attribute03);
    var vAnimation = daThis.action.attribute04;
    var vPosition = daThis.action.attribute05;
    var vDelay = parseInt(daThis.action.attribute06);
    var vTrigger = daThis.action.attribute07;
    var vMinWidth = parseInt(daThis.action.attribute08);
    var vMaxWidth = parseInt(daThis.action.attribute09);
    var vLogging = apexTooltip.parseBoolean(daThis.action.attribute10);
    // Logging
    if (vLogging) {
      console.log('showTooltip: affectedElements:', vElementsArray);
      console.log('showTooltip: Attribute Theme:', vTheme);
      console.log('showTooltip: Attribute Content:', vContent);
      console.log('showTooltip: Attribute Content as HTML:', vContentAsHTML);
      console.log('showTooltip: Attribute Animation:', vAnimation);
      console.log('showTooltip: Attribute Position:', vPosition);
      console.log('showTooltip: Attribute Delay:', vDelay);
      console.log('showTooltip: Attribute Trigger:', vTrigger);
      console.log('showTooltip: Attribute minWidth:', vMinWidth);
      console.log('showTooltip: Attribute maxWidth:', vMaxWidth);
      console.log('showTooltip: Attribute Logging:', vLogging);
    }
    for (var i = 0; i < vElementsArray.length; i++) {
      var vaffectedElement = daThis.affectedElements.eq(i);
      // call tooltipster plugin
      $(vaffectedElement).tooltipster({
        theme: vTheme,
        content: $(vaffectedElement).find('.rep_complete_comment').html(),
        contentAsHTML: vContentAsHTML,
        animation: vAnimation,
        position: vPosition,
        delay: vDelay,
        touchDevices: false,
        trigger: vTrigger,
        minWidth: vMinWidth,
        maxWidth: vMaxWidth,
        debug: vLogging,
        functionBefore: function(origin, continueTooltip) {
          $(vaffectedElement).trigger('apextooltip-show');
          continueTooltip();
        },
        functionAfter: function(origin) {
          $(vaffectedElement).trigger('apextooltip-hide');
        }
      });
    }
  }
};
Of course I had to update my IR as well.
First I trimmed the comment column and added a new hidden comment column:
SELECT
-- ...
  CASE WHEN LENGTH(COMMENT_COL) > 60 THEN SUBSTR(COMMENT_COL,1,60)||'...' ELSE COMMENT_COL END AS COMMENT_COL,
  COMMENT_COL as COMPLETE_COMMENT_COL,
from my_table
Then I updated the column attributes:
COMMENT_COL as plain text
- Static ID: rep_comment
- HTML Expression:
#COMMENT_COL#<div class="rep_complete_comment">#COMPLETE_COMMENT_COL#</div>



COMPLETE_COMMENT_COL as hidden column



I also had to add a small css snippet in the page attributes > CSS > Inline to hide the complete comment:
.rep_complete_comment {
display: none;
}

And as a last step I had to implement the tooltip plugin after refreshing the IR:
I used an advanced jQuery selector to get only those comments affected which had more then 60 characters.
td[headers='rep_comment']:contains('...')


The result looked like this:


APEX-AT-WORK no image

The APEX CONNECT and the APEX dashboard competition 2016

Von Tobias Arnhold → 4.30.2016
Before I tell you about the APEX dashboard competition winner I want to share my impressions from the APEX CONNECT 2016 in Berlin with you.

We had 3 days fully packed with APEX, SQL, PL/SQL, SQL Developer and different JavaScript technology topics.

In every way Niels de Brujin, Carsten Czarski, Christian Schwitalla and the whole DOAG team created an amazing event and has raised the quality compared to the last year.

We had more attendances, more international speakers and all known national speakers on board.
In my opinion and what I have heard from others, the general quality of the presentations has increased as well.

Thank you for letting me enjoy the event and be part of this great community!

What made the event so special? Before I start to write two more pages I decided to show you the uniqueness by letting the community speak:

THE LOCATION


THE GUYS WHO MADE IT POSSIBLE

THE #ORCLAPEX COMMUNITY

THE SPEAKERS


THE LEGEND

THE NEW STUFF WE WERE WAITING FOR

THE APEX DASHBOARD COMPETITION

THE TWITTER COMPETITION


THE STICKERS

THE CITY


MY OWN PRESENTATION
AND EIGHT HOURS BEFORE :)

MEETING MY FRIENDS FROM MY LOCAL USER GROUP

ASK THE GURUS

DAY 2

LAST BUT NOT LEAST: THE NEW ONES!


Now I'm happy to announce all winners including their applications:



WINNER:
Pavel Glebov
https://apex.oracle.com/pls/apex/f?p=7777708

SECOND:
Dick Dral
https://apex.oracle.com/pls/apex/f?p=7777710
Description: http://dickdral.blogspot.de/2016/04/apex-world-dashboard.html

THIRD:
Till Albert
https://apex.oracle.com/pls/apex/f?p=7777702

4.:
Sebastian Reinig
https://apex.oracle.com/pls/apex/f?p=7777704

5.:
Trent Schafer
https://apex.oracle.com/pls/apex/f?p=7777701
Description: http://apextips.blogspot.de/2016/04/the-making-of-my-apex-competition.html

6.:
Daniel Balzer
https://apex.oracle.com/pls/apex/f?p=7777711

7.-10.:
Arthur Rinberger
https://apex.oracle.com/pls/apex/f?p=7777712

7.-10.:
Matt Mulvaney
https://apex.oracle.com/pls/apex/f?p=7777709

7.-10.:
Sven Weller
https://apex.oracle.com/pls/apex/f?p=7777703

7.-10.:
Ronny Weiß
https://apex.oracle.com/pls/apex/f?p=7777705

11.-13.:
Peter Raganitsch
https://apex.oracle.com/pls/apex/f?p=7777707

11.-13.:
Archana Hebbar
https://apex.oracle.com/pls/apex/f?p=7777714

11.-13.:
Renat Kalimulin
https://apex.oracle.com/pls/apex/f?p=7777706

THE DOWNLOAD IS NOW AVAILABLE ON: http://apexcompetition.org/