Example using the analytical function: LAG

APEX examples

Example using the analytical function: LAG

Von Tobias Arnhold → 11.13.2013

I'm actually a big fan of using analytical functions instead of using SUB-Selects or custom PL/SQL functions.

The reason is quite simple: 
You save a lot of SQL executing time.

Another positive side is: 
The amount of code lines is also less then the other two solutions would need.

Negativ aspect:
You need to understand the logic behind analytical functions and you need to practice with them. :)

What was my problem?
I had some incomplete data to fix. Some rows of one column in my table were not filled. For my luck I did know that the previous row included the right value.

Here the example:

/* Using the LAG function */
select  OE2.ID, 
        OE2.CAR_NO,
        CASE WHEN OE2.CAR_NO IS NULL THEN
                  LAG(OE2.CAR_NO, 1, 0) OVER (ORDER BY OE2.ID)
            ELSE  OE2.CAR_NO END as CAR_NO_FIXED
 from TBL_ORDER_LIST OE2
 
/* Using the SUB-Select */
select  OE2.ID, 
        OE2.CAR_NO,
        CASE 
        WHEN OE2.CAR_NO IS NULL THEN ( SELECT OE1.CAR_NO
                                      FROM TBL_ORDER_LIST OE1 
                                      WHERE OE2.ID = OE1.ID-1
                                     )
        ELSE OE2.CAR_NO END AS CAR_NO_FIXED
 from TBL_ORDER_LIST OE2

The more rows you have in the table the bigger will be the difference in execution time.

If you want to know more about the LAG function.
Try this link:
http://www.oracle-base.com/articles/misc/lag-lead-analytic-functions.php

jQuery ModalDialog with iFrame

APEX examples

jQuery ModalDialog with iFrame

Von Tobias Arnhold → 10.30.2013

Using iFrames can sometimes be really helpful. Especially if you have information which should be served on several pages.

A simple solution using the jQuery UI dialog with iFrames in APEX is the following:

Add a class called callModalDialog to each of your links which should be opened in a modal dialog (referenced by an iFrame).

Example link:
<a class="callModalDialog" href="f?p=&APP_ID.:1000:&SESSION.::">Information about something</a>

Example when you have a link inside an APEX report:
Column Attributes > Column Link > Link Attributes: class="callModalDialog"

Now create a new dynamic action:

Event: Click
Selection Type: jQuery Selector
jQuery Selector: .callModalDialog

Action: Execute JavaScript Code
Execute on Page Load: No
Code:

/* prevent default behavior on click */
var e = this.browserEvent;
e.preventDefault();
/* Trigger JQuery UI dialog */
var horizontalPadding = 30;
var verticalPadding = 30;
$('<iframe id="modalDialog" src="' + this.triggeringElement.href + '" frameborder="no" />').dialog({
   title: "Information about something",
   autoOpen: true,
   width: 900,
   height: 600,
   modal: true,
   draggable: false,
    resizable: false,
   close: function(event, ui) { $(this).remove();},
   overlay: {
       opacity: 0.2,
       background: "black"}
}).width(900 - horizontalPadding).height(600 - verticalPadding);
return false; 

This solution takes the URL of your link and adds it to the iFrame inside the UI dialog.

Mobile APEX Anwendung mit Unterschrifts-Feld

jQuery Mobile

Mobile APEX Anwendung mit Unterschrifts-Feld

Von Tobias Arnhold →

Eigentlich eine simple Anforderung. Baue eine mobile Eingabemaske mit einem zusätzlichen Feld für eine digitale Unterschrift.
Wie für sehr vieles im WWW gab es auch dafür diverse Lösungen. Die Einzige die mir auf Anhieb wirklich gefallen hat, war: jSignatur

Warum:
 - Leicht zu integrieren
 - wenig JS Code + nur eine JS-Datei
 - funktioniert mit jQuery UI und jQuery Mobile
 - es werden alle gängigen Browser (inklusive IE 7 unterstützt)
 - keine Kommunikation mit anderen Diensten/Servern notwendig
 - Export / Import Funktionalität
 - Speicherung als String / Base64 Code

Ich bin zwar gerade noch bei der Integration und noch längst nicht fertig. Aber soweit ich das einschätzen kann, wird es nicht allzu kompliziert. Für einen ersten Entwurf habe ich 2 Stunden gebraucht (inklusive Test auch anderer Signatur-Plugins), da passt die Kosten-Nutzen Rechnung. :)

 

Ok ich werde wohl nie ein Grafiker werden...

Nachtrag 10.11.2013:
Die achso einfache Lösung hat mir einiges abverlangt.
Grund: Fehlendes Wissen im Bereich jQuery Mobile + 32k Übertragungsproblem

Nichts desto trotz kann ich sagen, der Upload/Dowload funktioniert! :)

Download

Pivot Lösungen in APEX

DE

Pivot Lösungen in APEX

Von Tobias Arnhold → 10.21.2013

Vor über einem Jahr habe ich eine Pivot Beispiel Applikation gebaut und diese bei einem DOAG Treffen präsentiert. Wie ich in den letzten Wochen bemerkt habe, ist das Thema immer noch sehr aktuell.

Morten Braten hat eine weitere beeindruckende Pivot-Lösung als APEX Plugin gebaut: Pivot Table plugin for Apex

Bei der kommenden DOAG 2013 wird es zum Thema Pivot auch den ein oder anderen Vortrag geben.

Ich weiß gar nicht ob es in APEX 5 neue Reporting Ansätze zum Thema Pivot geben wird?

UPPER first character

APEX examples

UPPER first character

Von Tobias Arnhold →

Seems to be a simple task but there are hundreds of solutions.
For me as an APEX developer I have to decide between an JS/jQuery or a SQL/PLSQL solution.

Easiest would be using the initcap function from Oracle but I was only allowed to upper case the first character of the field.

Example:
tobias likes tasty food.

Wrong:
Tobias Likes Tasty Food.

Correct:
Tobias likes tasty food.

After searching for a couple of minutes i found two easy ways to fix this issue:
jQuery solution on stackoverflow
PL/SQL solution on forums.oracle.com

I decided to use this Oracle solution:
upper( substr(:P1_FIRSTNAME,1,1) ) || substr(:P1_FIRSTNAME,2)

Instead of this jQuery solution: 
var txt = $('#P1_FIRSTNAME').val();
txt = txt.substring(0, 1).toUpperCase() + txt.substring(1);
$('#P1_FIRSTNAME').val(txt);

Why:
One line of code and for most APEX developers SQL functions are still easier to understand. Luckily I didn't had to face performance issues in this example. I mean bad performance is even more lousy then complicated code.

SQL Developer a great tool but...

Tools

SQL Developer a great tool but...

Von Tobias Arnhold → 8.21.2013

Actually I'm impressed from the speed (even so it is a Java based application), the easy handling and the integration into APEX.
For example remote debugging possibilities inside an APEX application: http://www.oracle.com/webfolder/technetwork/de/community/apex/tipps/remote-debug/index.html

Currently there are two things I really don't like.

Autocomplete Feature when I open a table and checking the data. 
If I click on the autocomplete it sometimes adds it at the end of my text. Instead of dropping my text and replacing it with the autocompleted text.

View with Trigger
When I use a view with an InsteadOf-Trigger and later I need to update the view. With the SQL Developer View Editor actually it does delete my Trigger. Even the Fast DDL feature does not include the trigger. Hope this is fixed in the next version?
Example: http://www.apex-at-work.com/2013/03/apex-tabular-form-auf-basis-einer-view.html
My workaround is to add the Instead of Trigger as Comment behind my the sql of the view.

Example Merge Procedure

APEX examples

Example Merge Procedure

Von Tobias Arnhold → 8.18.2013

I often have used UPDATE and INSERT statements during my development. In some complex updates especially when I had to select from other tables I sometimes got strange problems.

I even was able to update the wrong data because my statement was not correctly designed. After this experience I decided to switch to MERGE-statements. Merge Statements are easy to read especially when you use difficult select statements inside.

Here is an example package with an MERGE statement inside. It should show you how such an statement could look like and for me it is a good reminder how to design the code.

create or replace 
PACKAGE BODY PKG_MERGE_EXAMPLE AS
/* Package Variables */
  gv_proc_name    VARCHAR2(100);
  gv_action       VARCHAR2(4000);
  gv_ora_error    VARCHAR2(4000);
  gv_custom_error VARCHAR2(4000);
  gv_parameter    VARCHAR2(4000);
  gv_user         VARCHAR2(20) := UPPER(NVL(v('APP_USER'),USER));
  
/* Save errors */
/*
  --------------------------------------------------------
  --  DDL for Table ZTA_ERR_LOG
  --------------------------------------------------------

  CREATE TABLE "ERR_LOG" 
   ( "PROC_NAME" VARCHAR2(200), 
 "ACTION" VARCHAR2(4000), 
 "APP_ID" NUMBER, 
 "APP_PAGE_ID" NUMBER, 
 "APP_USER" VARCHAR2(20), 
 "ORA_ERROR" VARCHAR2(4000), 
 "CUSTOM_ERROR" VARCHAR2(4000), 
 "PARAMETER" VARCHAR2(4000), 
 "TIME_STAMP" DATE
   ) ;
/

*/
PROCEDURE ADD_ERR  IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
     INSERT
     INTO ERR_LOG
      ( PROC_NAME,ACTION,APP_ID,APP_PAGE_ID,APP_USER,ORA_ERROR,CUSTOM_ERROR,PARAMETER,TIME_STAMP )
      VALUES
      ( gv_proc_name,gv_action,nvl(v('APP_ID'),0),nvl(v('APP_PAGE_ID'),0),nvl(nvl(v('APP_USER'),USER),'Unknown'),
        gv_ora_error,gv_custom_error,gv_parameter,sysdate );
     COMMIT;
END;


/* ************************************************************************************************************************************** */
/* Merge Example                                                                                                                          */  
/* ************************************************************************************************************************************** */

PROCEDURE prc_merge_example
IS
BEGIN
  gv_proc_name := 'pkg_merge_example.prc_merge_example';
  gv_parameter := '';
  
  gv_action := 'Merge Data instead of update and insert'; 
  MERGE INTO TBL_MERGE_FACILITY t1
   USING (
          SELECT  t2.id,
                  t2.facility_name,
                  t3.address
                  gv_user as current_user,
                  sysdate as current_timestamp
          FROM TBL_FACILITY t2, TBL_ADDRESS t3
          WHERE t2.address_id = t3.id
          AND   t2.activ = 1
         ) t4
  ON (t1.facility_id = t4.id)
  WHEN MATCHED THEN
    UPDATE SET
      t1.facility_name    = t4.facility_name,
      t1.facility_address = t4.address,
      t1.updated_by       = t4.current_user,
      t1.updated_on       = t4.current_timestamp
  WHEN NOT MATCHED THEN
    INSERT
  (
    facility_id,
    facility_name,
    facility_address,
    created_by,
    created_on
  )
  VALUES
  (
    t4.id,
    t4.facility_name,
    t4.address,
    t4.current_user,
    t4.current_timestamp
  );

  gv_action := 'Updated rows: ' sql%rowcount; 
  
  COMMIT;
EXCEPTION
WHEN OTHERS THEN   
      gv_ora_error := SQLERRM;
      gv_custom_error := 'Internal Error. Action canceled.';
      ROLLBACK;
      ADD_ERR; raise_application_error(-20001, gv_custom_error);
END;
 
END PKG_MERGE_EXAMPLE;