Browsing "Older Posts"

APEX-AT-WORK no image

SQL Developer 4 and APEX 4.2.4 are out

Von Tobias Arnhold → 12.13.2013
New version of the Oracle SQL Developer is out:
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

New features can be found here:
http://www.oracle.com/technetwork/developer-tools/sql-developer/sqldev-newfeatures-v4-1925252.html

-------------------------------------------------------------------------------------------------------------

Besides the new version of SQL Developer also an update of the current APEX version got released:
http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

Joel Kallman wrote about the updated / new features:
http://joelkallman.blogspot.de/2013/12/oracle-application-express-424-now.html

I bet this is the last release before APEX 5. ;)

Disable input elements in tabular form

Von Tobias Arnhold → 12.12.2013
There is a quite often requirement to disable a tabular form column based on the row information.

I got a customer request with the following requirement:
A select list column of a tabular form should be disabled (row based) when the value of another column is like SYSDATE.

There are 3 options to follow:
1. Create a manual tabular form and disable the column with a case statement inside the select
2. Deactivate the column with some jQuery code
3. Wait for APEX 5. I think there is some development ongoing about this problem.

Some thoughts about solution 2

I assume that the SYSDATE is: 12.12.2013
Our result should look like this:


How to achieve this?
First I need to extend the report with one extra column plus some column customization
Second is some jQuery code in form of two dynamic actions.

The SELECT statement:
SELECT -- Original columns:
       ANIMAL_OWNER_ID, ANIMAL_OWNER_NAME, ANIMAL_ID, to_char(LAST_DATE,'dd.mm.yyyy') as LAST_DATE, 
       -- New column:
       to_char(SYSDATE,'dd.mm.yyyy') as CURRENT_DATE
FROM   CUST_TABLE
Now I change the report attributes of column: LAST_DATE
Column Formatting >> HTML Expression:
<span class="tbl_date">#LAST_DATE#</span><span class="tbl_cur_date" style="display:none;">#CURRENT_DATE#</span>

Now I need to add two dynamic actions to change the appearance of our column: ANIMAL_ID
1. Deactivate the affected rows
Event: Page Load
Action: Execute JavaScript Code
$('.tbl_date').each(function( index ) {
 if ( $(this).html() == $(this).parent().find('.tbl_cur_date').html() ) 
  {
    $( this ).parent().parent().find('select[name="f01"]').prop('disabled', 'disabled');
  }
});
What am I doing here?
I search for all elements with the CSS class "tbl_date"
Now I compare the HTML value with the value of the class "tbl_cur_date".
If the result is TRUE then the select list will be disabled.
To find the select list I search for the name "f01". Actually because I only have one select element I  could use this code snippet as well: find('select')

2. Activate the affected rows before page submit. Otherwise I would get an tabular form error.
Event: Before Page Submit
Action: Execute JavaScript Code
$('.tbl_date').each(function( index ) {
 if ( $(this).html() == $(this).parent().find('.tbl_cur_date').html() ) 
  {
    $( this ).parent().parent().find('select[name="f01"]').prop('disabled', '');
  }
});
Now I have a huge security hole in my application. To close this one I need a tabular form validation. The validation must check for changed select elements. Of course only those which should not be able to change.
You can follow this blog post which is providing a hint how to achieve it: http://www.apex-at-work.com/2012/06/tabular-form-validation-issue.html

Working with multiple browser tabs

Von Tobias Arnhold → 12.10.2013
When I develop APEX applications I use several browser tabs to navigate between the executed application and the application builder. There is one side affect which drives me crazy sometimes.

TAB 1: Executed application
TAB 2: Application Builder

How do I work?
I edit an item inside the application builder (for example the LOV select statement) and click on save. I immediately change the TAB and press F5 to update the application.

What drives me crazy?
If I have made a mistake in the select statement of my LOV I just get a error message inside the browser page. But I changed the TAB faster then the result appeared and I didn't recognize it.

It would be great if the TAB title would have changed too. So that I could see that an error occurred.



Update 12.12.2013:
New APEX feature request added on: apex.oracle.com/vote
ID: AIRU
Text: Browser TAB error message


APEX-AT-WORK no image

Working with XML files and APEX - Part 2: Selecting data from XMLType column with XMLTable

Von Tobias Arnhold → 12.05.2013
After I described how to successfully import XML files into an APEX application. It's time to start to analyze them.

I still assume that this is the example XML file:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<leagues>
  <league id="1" name="2. Bundeliga">
    <teams>
      <team>
        <id>1</id>
        <name location="Dresden" stadium="Glücksgas-Stadion">SG Dynamo Dresden</name>
      </team>
      <team>
        <id>2</id>
        <name location="Cologne" stadium="RheinEnergieStadion">1. FC Köln</name>
      </team>
      <team>
        <id>3</id>
        <name location="Berlin" stadium="Alte Försterei">1. FC Union Berlin</name>
      </team>
      <team>
        <id>4</id>
        <name location="Düsseldorf" stadium="Esprit Arena">Fortuna Düsseldorf</name>
      </team>
    </teams>
  </league>
</leagues>
The first select will find all team names and locations:
-- table: IMP_FM
-- XMLType column: XML_FILE

SELECT T.TEAM, 
       T.LOCATION
FROM
       IMP_FM FM,
       XMLTable('/leagues/league/teams/team/name' PASSING FM.XML_FILE
       COLUMNS  "TEAM"         VARCHAR2(255) PATH 'text()',
                "LOCATION"     VARCHAR2(255) PATH '@location'
                ) T
What am I doing here?
In the FROM-clause I define the XML table "IMP_FM" and right after that I generate a new XMLTable object. Inside this object I define the start entry "/leagues/league/teams/team/name" from where I want to select the data. In the PASSING-clause I define the XMLType column from where the data is coming.
Next step is to set the SQL columns based on the XML data.
To get the team names I need to select the text from the xml element "name". To do that I use the function "text()".
To select the attribute "location" I need to use an "@" in front of the attribute name.
 This newly created table will have the name "T" and can be selected in the SELECT-clause.

In the next example I will select league and team names. Actually they would be two tables with an 1-n relationship.
Because of that I have to implement two XMLTable objects in the select statement.
SELECT L.LEAGUE,
       T.TEAM, 
       T.LOCATION
FROM
       IMP_FM FM,
       XMLTable('/leagues/league' PASSING FM.XML_FILE
       COLUMNS  "LEAGUE"     VARCHAR2(255) PATH '@name',
                "T_XML"      XMLTYPE       PATH 'teams/team'
                ) L,
       XMLTable('team/name' PASSING L.T_XML
       COLUMNS  "TEAM"         VARCHAR2(255) PATH 'text()',
                "LOCATION"     VARCHAR2(255) PATH '@location'
                ) T
What am I doing here?
In the first XMLTable object I generate a XMLType column with the data from path "teams/team". This column is named as "T_XML". The generated table is named as "L".
Now I create a new XMLTable object based on the data of the XMLType column "L.T_XML". I follow the same logic as in first select and just use a shorter PATH "team/name".
More information about XML sub-selects can be found in this blog post: New 12c XMLTABLE’s “RETURNING SEQUENCE BY REF” clause

In the last example I want to show how to work with XML namespaces. For that our XML changes a bit.
<?xml version="1.0" encoding="UTF-8"?>
<leagues xmlns:tt="http://www.footballleagues_xml.org/schemas/team" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.footballleagues_xml.org/schemas http://www.footballleagues_xml.org/schemas/xml/XML.xsd" version="2.1" xmlns="http://www.footballleagues_xml.org/schemas">
  <league id="1" name="2. Bundeliga">
    <teams>
      <team>
        <tt:id>1</tt:id>
        <tt:name location="Dresden" stadium="Glücksgas-Stadion">SG Dynamo Dresden</tt:name>
      </team>
      <team>
        <tt:id>2</tt:id>
        <tt:name location="Cologne" stadium="RheinEnergieStadion">1. FC Köln</tt:name>
      </team>
      <team>
        <tt:id>3</tt:id>
        <tt:name location="Berlin" stadium="Alte Försterei">1. FC Union Berlin</tt:name>
      </team>
      <team>
        <tt:id>4</tt:id>
        <tt:name location="Düsseldorf" stadium="Esprit Arena">Fortuna Düsseldorf</tt:name>
      </team>
    </teams>
  </league>
</leagues>
To select the data now is a bit more complicated:
SELECT T.TEAM, 
       T.LOCATION
FROM
       IMP_FM FM,
       XMLTable(XMLNameSpaces('http://www.footballleagues_xml.org/schemas/team' as "tt", 
                              default 'http://www.footballleagues_xml.org/schemas'),
               '/leagues/league/teams/team' PASSING FM.XML_FILE
       COLUMNS  "TEAM"         VARCHAR2(255) PATH 'tt:name/text()',
                "LOCATION"     VARCHAR2(255) PATH 'tt:name/@location'
                ) T
What am I doing here?
Inside the XMLTable object I define a "XMLNameSpace" which is marked as "tt".
Inside the column definition I use "tt:" to select the data.
More information about multiple Namespaces can be found here: https://forums.oracle.com/thread/2381998

That's it for today. Cheers Tobias

New beta version of the APEX Blog-aggregator is online

Von Tobias Arnhold → 12.03.2013
Check out the updated blog aggregator on http://www.odtug.com/apex

Finally some major usability extensions were integrated. Looks like APEX to me.

Advantages:
  - You can search the blog posts at least back into the year 2008.
  - Watch the last 100 posts
  - No more problems with duplicated posts or tweets
  - Seems really fast to me



Update 04.12.2013:
Got a comment from Buzz Killington and unfortunately deleted it (damn Smartphone). Here is the text:

======

Here are my thoughts:

1) They need to get rid of the double-scrollbar iframe thing. It is pretty unusable.

2) I'm pretty sure it's an APEX feature, but if you scroll to page 2 (11-20) and then close your browser, the next time you get back you're still on page 2. It's counter-intuitive - you should always go back to page 1 otherwise you'll miss new posts.

======

My thoughts:
1) I understand the handling issues you have. As far as I know there are not much options to change the behavior in including an iframe.

2) Right. I think this can be easily changed using a reset pagination in the iframe URL.


APEX-AT-WORK no image

Update an APEX tree dynamically

Von Tobias Arnhold → 12.01.2013
I was asked by an APEX developer if it is possible to dynamically update an APEX tree by changing an APEX item.

Example select:
select case when connect_by_isleaf = 1 then 0 when level = 1 then 1 else -1 end as status,
       level,
       ENAME as title,
       NULL  as icon,
       EMPNO as value,
       ENAME as tooltip,
       NULL  as link
from EMP
start with (:P1_MGR is null AND MGR is null OR :P1_MGR is not null and MGR = :P1_MGR)
connect by prior EMPNO = MGR
order siblings by ENAME


As far as I know APEX has no out of the box function for that. The dynamic action to refresh reports doesn't work with trees. Or am I wrong here?
You can follow different workarounds no one of them will fulfill all your hopes:

1. Wait for APEX 5.0 maybe the APEX team will include a dynamic tree update functionality?
2. Build some custom JS code to dynamically change the tree.
3. Don't make it dynamically. Submit the page after you have changed the APEX item.
4. Use a third party solution for example: dhtmlx tree
5. Build your own tree solution (plug-in).

I would tend to solution 3 and would wait for the next version of APEX. If the customer doesn't accept it and would pay the more efforts I would tend to a third party solution.  

Update 03.12.2013:
Take a look at Tom's blog he made a couple of really good tree based blog posts.
APEX-AT-WORK no image

Working with XML files and APEX - Part 1: Upload

Von Tobias Arnhold → 11.28.2013
Working with Oracle and XML can be a pain in the ass especially at the beginning when you don't know the hidden secrets. :) That's why I want to give some major hints how to integrate XML files in APEX applications.

This time I will provide an easy way how to upload a file into a table with a XMLType column.

Let's assume that this is our example XML file:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<leagues>
  <league id="1" name="2. Bundeliga">
    <teams>
      <team>
        <id>1</id>
        <name location="Dresden" stadium="Glücksgas-Stadion">SG Dynamo Dresden</name>
      </team>
      <team>
        <id>2</id>
        <name location="Cologne" stadium="RheinEnergieStadion">1. FC Köln</name>
      </team>
      <team>
        <id>3</id>
        <name location="Berlin" stadium="Alte Försterei">1. FC Union Berlin</name>
      </team>
      <team>
        <id>4</id>
        <name location="Düsseldorf" stadium="Esprit Arena">Fortuna Düsseldorf</name>
      </team>
    </teams>
  </league>
</leagues>

Now we need the necessary DDL:

--------------------------------------------------------
--  DDL for XML Table IMP_FM
--------------------------------------------------------
CREATE TABLE IMP_FM 
(
  ID NUMBER NOT NULL 
, USER_NAME VARCHAR2(20 BYTE) 
, XML_FILE XMLTYPE 
, CONSTRAINT IMP_FM_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
) 
XMLTYPE XML_FILE STORE AS BINARY XML ALLOW NONSCHEMA;

/

CREATE SEQUENCE IMP_FM_SEQ INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCACHE;

/

CREATE OR REPLACE TRIGGER "IMP_FM_BI_TR" BEFORE
INSERT ON "IMP_FM" FOR EACH row 
BEGIN
 IF :NEW.USER_NAME IS NULL THEN 
    :NEW.USER_NAME := UPPER(NVL(v('APP_USER'),USER));
 END IF;
 IF :NEW.ID IS NULL THEN 
    SELECT IMP_FM_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
 END IF;
END;
/

--------------------------------------------------------
--  DDL for Table ERR_LOG
--------------------------------------------------------

  CREATE TABLE "ERR_LOG" 
   ( "AKTION" VARCHAR2(4000), 
 "APP_ID" NUMBER, 
 "APP_PAGE_ID" NUMBER, 
 "APP_USER" VARCHAR2(10), 
 "ORA_ERROR" VARCHAR2(4000), 
 "CUSTOM_ERROR" VARCHAR2(4000), 
 "PARAMETER" VARCHAR2(4000), 
 "TIME_STAMP" DATE, 
 "PROC_NAME" VARCHAR2(500), 
 "CLOB_FIELD" CLOB
   ) ;
/

  ALTER TABLE "ERR_LOG" MODIFY ("PROC_NAME" NOT NULL ENABLE);
  ALTER TABLE "ERR_LOG" MODIFY ("TIME_STAMP" NOT NULL ENABLE);
  ALTER TABLE "ERR_LOG" MODIFY ("APP_USER" NOT NULL ENABLE);
  ALTER TABLE "ERR_LOG" MODIFY ("APP_PAGE_ID" NOT NULL ENABLE);
  ALTER TABLE "ERR_LOG" MODIFY ("APP_ID" NOT NULL ENABLE);
  ALTER TABLE "ERR_LOG" MODIFY ("AKTION" NOT NULL ENABLE);
/

Our XML table has the column XML_FILE from type XMLTYPE. Thats the place where our uploaded files will be saved in.
With XMLTYPE you need to define the way the XML should be saved. As I found out (thanks to Carsten Czarski) the "BINARY XML" option is the most effective one.

More details about the saving options can be found here:
http://www.oracle.com/technetwork/database-features/xmldb/xmlchoosestorage-v1-132078.pdf
http://www.liberidu.com/blog/2007/06/24/oracle-11g-xmltype-storage-options/
http://grow-n-shine.blogspot.de/2011/11/one-of-biggest-change-that-oracle-has.html

During my tests (30MB XML file) I started with the option "XMLTYPE XML_FILE STORE AS CLOB" which leaded to a real bad result time.
One of the selects had a executing time of 314 seconds
With the "XMLTYPE XML_FILE STORE AS BINARY XML" it went down to 1 second.

Ok my system did not had a lot of CPU or RAM but to show the difference out of a performance point of view it is a great example.

Last but not least we need the PL/SQL Code to upload our XML file:
For quality aspects I always use packages with some debug features when I need PL/SQL code. Thats why this example becomes maybe a bit bigger then it normally would be.

CREATE OR REPLACE 
PACKAGE PKG_IMP AS 

  procedure fm_imp (p_filename varchar2);

END PKG_IMP;

/

CREATE OR REPLACE 
PACKAGE BODY PKG_IMP 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_apex_err_txt VARCHAR2(500);
  

  GV_USERNAME     VARCHAR2(100)     := UPPER(NVL(v('APP_USER'),USER));

/* ********************* */
/* Save errors           */
/* ********************* */
PROCEDURE ADD_ERR  IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
     INSERT
     INTO ERR_LOG
      ( PROC_NAME,AKTION,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(GV_USERNAME,'Unknown'),
        gv_ora_error,gv_custom_error,gv_parameter,sysdate );
     COMMIT;
END; 

/* ********************* */
/* Import Procedure      */ 
/* ********************* */

procedure fm_imp (p_filename varchar2) AS
 
  v_blob BLOB;
  v_xml  XMLTYPE;

BEGIN
  gv_proc_name := 'pkg_imp.fm_imp';
  gv_parameter := '';
 
  gv_parameter := 'p_filename: ' || p_filename;
  
  gv_action := 'Delete old data';
  DELETE FROM IMP_FM
   WHERE user_name = GV_USERNAME;
   
 
  gv_action := 'Read file';
  SELECT blob_content 
    INTO v_blob
    FROM wwv_flow_files
   WHERE name = p_filename;
  
  gv_action := 'XML Conversion';
  v_xml := XMLTYPE (v_blob,NLS_CHARSET_ID('AL32UTF8'));
  /* UTF-8 clause because we use it in our XML file */

  gv_action := 'Insert in IMP_BESTELLLISTE_XML';
  INSERT
  INTO IMP_FM
    ( USER_NAME, XML_DATEI )
  VALUES
    ( GV_USERNAME, v_xml );

  gv_action := 'Delete file';
  DELETE FROM wwv_flow_files
    WHERE name = p_filename;

  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 fm_imp; 
END PKG_IMP;

Inside APEX you call our procedure as SUBMIT PL/SQL Process:

  PKG_IMP.FM_IMP (:P1_FILE);
  -- P1_FILE is the file browse item.
That's it.

BTW: My SQL Developer stopped working / got really slow when I tried to update a BINARY XML column.

Next time I will write about my troubles in selecting XML data as readable SQL result.
APEX-AT-WORK no image

Switching from Windows to Mac

Von Tobias Arnhold → 11.24.2013
A year ago I bought a Macbook Pro and tried to develope APEX applications with it successfully.
You may ask yourself why? I just want to stay "up to date" and work with the best technique on the market. A couple of colleagues mentioned the performance is better with Mac. Reason enough for me to check it out.

I never needed many special developer tools to build APEX applications in Windows:

 - SQL Developer / Data Modeler - SQL/PLSQL development
 - Firefox + Firebug - APEX development
 - Notepad++ - Universal code editor
 - Greenshot - Make screen copies
 - WinMerge - Compare files
 - Gimp - Working with images
 - MS Office - Documentation / Importing / Presentation
 - Virtual Box - Virtual environment

All these tools (except MS Office) do not need an installation (portable version available) and became my standard apex-at-work-kit for each company. I hated to get used to different development-tools all the time.

With Mac I tried to find the same or at least similar tools which I was used to work with in Windows before:

 - SQL Developer / Data Modeler
 - Firefox + Firebug
 - Ultraedit (is payware but no freeware came so close to Notepad++ like Ultraedit)
 - Skitch
 - No good alternative found yet, but you could check out this link: apple.stackexchange.com
 - Gimp
 - MS Office
 - Virtual Box

The performance of my Macbook Pro (bought on Ebay and extended with SSD and RAM) is actually amazing. At the moment there is no reason to change from testing mode into buying the newest model mode. :)

Next step is to test APEX development on Windows 8.1 :)

I don't want to decide about the best OS. For me it is just interesting finding the most effective way in developing APEX applications.

Btw.: You may wonder why I didn't say anything about SVN or similar tools. Most companies have their own software versioning-tools which means company dependent solutions. My point of interest is company-independent-tools.

There are a lot of more necessary tools but to 95 % of the time I work with the described ones.





APEX-AT-WORK no image

APEX sei ein langsames Tool

Von Tobias Arnhold → 11.14.2013
Nachdem ich den Blogpost von Joel Kallman gelesen habe, dachte ich mir ein paar eigene Erfahrungen zum Thema APEX und Performance beizutragen.

Die Aussage APEX sei ein langsames inperformantes Tool, ist einfach nur FALSCH!
Wenn jemand ein Auto mit angezogener Handbremse fährt, dann liegt die langsame Geschwindigkeit nicht am Auto sondern am Fahrer.
Wenn ich in meiner APEX Anwendung inperformanten PL/SQL, SQL oder JS Code einbaue, dann liegt es nicht an APEX sondern an mir, der die internen Funktionalitäten nicht versteht. Der Aussage lieber in Oracle Skills statt in APEX Skills zu investieren kann ich nur beipflichten. Wobei ich statt Oracle Skills eher die SQL Skills im Vordergrund sehe.

In meiner Zeit als Festangestellter und als Freelancer habe ich mit verschiedensten APEX Versionen in unterschiedlichsten Hardware Umgebungen gearbeitet.
Von einer virtuellen 1 CPU Umgebung bis hin zur Exadata war alles dabei. Die inhaltlichen Anforderungen waren bei den LowCost Systemen teilweise anspruchsvoller als in den High Performance Umgebungen.

Wo liegt nun der Schlüssel zum Erfolg?
Meiner Meinung nach in weniger PL/SQL und vielmehr in guten SQL, durchdachter Konzeption und der Verwendung von möglichst viel APEX Standard Funktionalität! 
Die meisten komplexen PL/SQL Themen kann ich mit Hilfe von Oracle Standard SQL Funktionen lösen. Beispiel: "Analytische Funktionen"

Ich meine, Sie arbeiten mit einer der schnellsten Datenbanksoftware auf dem Markt.
Was kann diese am Besten? SQL.

Natürlich ist und bleibt ein erheblicher Erfolgsfaktor einer jeden APEX Lösung, die notwendige Konzeptarbeit am Anfang eines jeden Projektes zu leisten. Da kann auch das Beste SQL schnell zum scheitern Verurteilt sein. Folgende Fragen sollten dabei immer in Betracht gezogen werden?

Was mache ich eigentlich für ein Projekt: OLTP oder OLAP? 
Wie komplex sind die Tabellenstrukturen?
Wie sieht das Mengengerüst aus ?
Wie viele Datensätze werden die Tabellen haben?
Kann ich eventuell Daten auch redundant abspeichern?
Wird das System auf Dauer erheblich mehr Datensätze generieren?
Wenn ja, wie viele? Welche Tabellen sind betroffen? Benötige ich spezielle Tests?
Sind Bottlenecks erkennbar?
Welche Schnittstellen müssen integriert/geschaffen werden?
Gibt es bereits Erfahrungen mit diesen Schnittstellen?
Werden sich auch nach Fertigstellung der Anwendung häufig die Anforderungen ändern?
Haben schon andere Entwickler versucht die Anforderungen umzusetzen? Wenn ja, dann gilt es hier besonders zu Glänzen. :) ...und den Kunden nicht wieder zu enttäuschen.

Statt sich einzig und allein an Vorstudien oder Konzepten zu orientieren, sollte der Kontakt zum Kunden gesucht werden.

Warum?
Die meisten Dokumente die den Inhalt einer neuen Anwendung transportieren sollen, hören dabei immer bei den komplexen Themen auf Antworten zu liefern. Nur die Fachverantwortlichen können einem frühzeitig mit entsprechenden Hinweisen aufkommende Probleme erkennen lassen.
Außerdem kann der Kunde so frühzeitig auf visuelle und funktionale Entscheidungen einwirken.

Nur weil jeder mit APEX sehr schnell Lösungen generieren kann, heißt dies nicht, dass jegliche Softwareentwicklungsprinzipien außer Kraft gesetzt werden können und dürfen.





APEX-AT-WORK no image

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
APEX-AT-WORK no image

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

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

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?
APEX-AT-WORK no image

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...

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.

APEX-AT-WORK no image

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;
APEX-AT-WORK no image

Connect a grouped report to an ungrouped report with virtual ID column

Von Tobias Arnhold → 8.16.2013
Seems to be a simple problem. I have and grouped report where I want to see all facilities on a address.

For example:
Grouped View
EditAddressAmount of facilities
xGermany, Dresden, Dresdner Strasse 12
xGermany, Frankfurt, Frankfurter Strasse 13

Detail View
AddressFacility
Germany, Dresden, Dresdner Strasse 1Computer System EXAXY
Germany, Dresden, Dresdner Strasse 1Computer System KI
Germany, Frankfurt, Frankfurter Strasse 1Manufactoring System 007
Germany, Frankfurt, Frankfurter Strasse 1Manufactoring System 009
Germany, Frankfurt, Frankfurter Strasse 1Manufactoring System 028

How to achieve this when we do not have a primary key and our key column includes commas which breaks the link. We easily generate an own ID column via an analytical function. In both reports we use an Oracle view to get the data from and our view looks like that:
select   facility_address,
         facility_name,
         dense_rank() over (order by facility_address) as facility_id
from     facilities
Via the function dense_rank and the partition by facility_address we get an unique ID for all facilities based on the address.
The grouped report looks like that:
select   facility_id,
         facility_address,
         count(facility_name) as amount_of_facilities
from     facilities
The detail report looks like that
select   facility_address,
         facility_name
from     facilities
where    facility_id = :P2_FACILITY_ID
Now you need to add a link in the report attributes section inside the grouped report. We set the item :P2_FACILITY_ID with our column #FACILITY_ID#.
That's it.

Getting the amount of rows from report with jQuery

Von Tobias Arnhold → 5.16.2013
I had the task to show the amount of displayed rows from a standard report on another position of the page. As "Pagination Scheme" in the "Report Attributes" I used: "Row Ranges X to Y from Z"

To get this Z value I needed to check the HTML code:



<td nowrap="nowrap" class="pagination">
   <span class="fielddata">Zeile(n) 1 - 15 von 329</span>
</td>
Inside class "fielddata" was my value Z. To get the value I needed this little piece of jQuery:
var v_txt = $('.fielddata').html();
var v_num = v_txt.split(' ');
var v_return= v_num[v_num.length - 1]; 
APEX-AT-WORK no image

Expand APEX tree after page load

Von Tobias Arnhold → 5.03.2013
This little snippet of code will expand the APEX tree:
$("input:[value='Expand All']").click();
This solutions finds the HTML objects by the displayed value. Use it in a "Dynamic Action > Page Load > Execute Javascript" process.

In case you use multi language applications then this solution will fit better:
http://apextips.blogspot.de/2011/03/expand-and-collapse-all-tree-nodes.html
APEX-AT-WORK no image

Generate DDL Source Code with SQL

Von Tobias Arnhold → 3.27.2013
I just found this little piece of code to create DDL source code.
SELECT dbms_metadata.get_ddl(replace(OBJECT_TYPE, ' ', '_'), OBJECT_NAME,OWNER) as DDL_SOURCE_CODE
FROM ALL_OBJECTS
WHERE OBJECT_TYPE IN 
   ('SEQUENCE',   'TABLE',           'INDEX',
    'VIEW',       'DATABASE LINK',   'MATERIALIZED VIEW',
    'FUNCTION',   'PROCEDURE',       'PACKAGE',
    'PACKAGE BODY'
    )
AND OWNER = '#SCHMEA_NAME#';
APEX-AT-WORK no image

Migrate Sequences

Von Tobias Arnhold → 3.22.2013
During one of my projects I had an issue when I copied the DDL from my test environment into my productive system. Unfortunately I needed some of the test data in the prod system as well. For that I had to migrate most of the sequences starting with their last number. SQL Developer created those sequences starting with 1. This simple code fixed my issue.
select 'DROP SEQUENCE "'||SEQUENCE_NAME||'";' || 
       ' CREATE SEQUENCE "'||SEQUENCE_NAME||'"' ||
       ' MINVALUE 1 MAXVALUE 999999999999999999999999999' ||
       ' INCREMENT BY 1 START WITH ' || to_char(last_number+1) ||
       ' NOCACHE NOORDER NOCYCLE ; ' as seq_code
from all_sequences
where sequence_owner = '#SCHEMA_NAME#';
Cheers Tobias

APEX Tabular Form auf Basis einer View

Von Tobias Arnhold → 3.18.2013
Eine der häufigen Anforderungen in der APEX Entwicklung ist es, einen änderbaren Report (Tabular Form) anzulegen bei dem einzelne Spaltenwerte verändert werden dürfen. Dies funktioniert in 90% der Fälle sehr gut. In manchen Fällen ist der Standardmechanismus leider nicht die 100% Lösung. Wenn Sie besonders viele LOV Spalten in Ihrem änderbaren Report verwenden, dann kann dies zu Performance Problemen führen. Genau auf dieses Problem gehen wir in diesem Blogeintrag näher ein.
In unserem Beispiel beziehen wir uns auf eine Bestelltabelle und in dieser darf die Spalte GESAMT_BETRAG nachträglich editiert werden. :)
Die Tabelle besteht der Einfachheit halber aus nur 4 Stammdatentabellen und einer Bestelltabelle.
(Um die langen Ladezeiten bei sich zu verursachen, müssen Sie wahrscheinlich noch ein paar mehr LOV Spalten in Ihrem Tabular Form haben.)

Der Standardweg ein Tabular Form in APEX aufzubauen läuft nun wie folgt. 
 1. Anlegen eines Tabular Form auf Basis der Tabelle BESTELLUNG
     (Create Region > Create Form > Create Tabular Form)
SELECT BESTELLUNG_NR, BESTELLUNG_DATUM, BESTELLUNG_TYP_NR, 
       KUNDE_NR, BEARBEITER_NR, SHOP_NR, GESAMT_BETRAG
FROM BESTELLUNGEN
 2. Statt einer Menge FK IDs anzuzeigen, wird bei jeder FK Spalte eine LOV hinterlegt
     (Column Attributes > Display As: "LOV Type")
Display as: Display as Text (based on LOV, does not save state)
Beispiel: BEARBEITER_NR
SELECT nachname || ', ' || vorname as d,
       bearbeiter_nr as r 
FROM bearbeiter
Dies wird bei allen anderen FK Spalten wiederholt.
Wenn Sie nun Ihren Report ausführen und um die 200 Datensätze ausgeben, kann es leicht zu erhöhten Wartezeiten
kommen. Grund? Jede LOV wird je Datensatz ausgeführt. Dies ist gut im Debugmodus ersichtlich.

Die Alternative ist eine View zu verwenden und diese änderbar zu konfigurieren.
-- View DDL
CREATE OR REPLACE VIEW VIEW_BESTELLUNGEN AS 
SELECT B.ROWID AS ROW_ID, B.BESTELLUNG_NR, B.BESTELLUNG_DATUM, 
       BT.NAME as BESTELLUNG_TYP, B.BESTELLUNG_TYP_NR,
       K.NACHNAME || ', ' || K.VORNAME as KUNDE, B.KUNDE_NR,
       BA.NACHNAME || ', ' || BA.VORNAME as BEARBEITER, B.BEARBEITER_NR,
       S.NAME as SHOP, B.SHOP_NR,
       B.GESAMT_BETRAG
FROM BESTELLUNGEN B, BESTELLUNG_TYP BT, KUNDE K, BEARBEITER BA, SHOP S
WHERE B.SHOP_NR = S.SHOP_NR
AND B.BEARBEITER_NR = BA.BEARBEITER_NR
AND B.KUNDE_NR = K.KUNDE_NR
AND B.BESTELLUNG_TYP_NR = BT.BESTELLUNG_TYP_NR

-- Neues Tabular Form Select
SELECT BESTELLUNG_NR, BESTELLUNG_DATUM, BESTELLUNG_TYP, KUNDE,
       BEARBEITER, SHOP, GESAMT_BETRAG
FROM VIEW_BESTELLUNGEN
In unserem Beispiel soll nur der Gesamtbetrag nachträglich änderbar bleiben.
Damit die View versteht wohin gespeichert werden soll, muss ein INSTEAD OF Trigger definiert werden:
CREATE OR REPLACE TRIGGER  VIEW_BESTELLUNGEN_IOU
INSTEAD OF UPDATE
ON VIEW_BESTELLUNGEN
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
UPDATE
BESTELLUNGEN
SET GESAMT_BETRAG = :new.gesamt_betrag
WHERE ID = :old.id;
EXCEPTION WHEN OTHERS THEN
-- Please, do some error handling and allow me
-- to skip this part for this time...
RAISE;
END VIEW_BESTELLUNGEN_IOU;
Info: Wenn wir eine LOV Spalte ändern wollten, dann wäre eine definierte LOV die bessere Lösung.
APEX-AT-WORK no image

Automatic language detection bug in APEX 4.2.1

Von Tobias Arnhold → 2.12.2013
I already posted a forum entry to the topic but no one had answered yet. I think the bug is a real problem so people should know about it:
https://forums.oracle.com/forums/message.jspa?messageID=10840734#10840734

Here are the details:
Automatic browser language detection can not be used in APEX applications during their runtime.

In the environment I'm working at the moment we are developing mostly German applications but database standard character set is this:
SELECT * FROM NLS_DATABASE_PARAMETERS

Parameter Value
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P15
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN


All applications are set up with "Globalization Attributes":
 Application Primary Language: German
 Application Language Derived From: Browser (use browser language preference)
 Application Date Format: DD.MM.YYYY


Default behavior should be:

In all former versions of APEX it changes the default settings from the database to the browser settings.
An now:
In the current version it does NOT change the language.

For example: 
APEX 4.1 displays number values like that
10.000,00

APEX 4.2.1 displays number values like that
10,000.00

If I check the debug log I find this:
0.04096 0.00016 S H O W: application="101" page="16" workspace="" request="" session="16485344217862" 4 
0.04110 0.00044 Reset NLS settings 4 
0.04155 0.00020 alter session set NLS_LANGUAGE="AMERICAN" 4 
0.04174 0.00016 alter session set NLS_TERRITORY="AMERICA" 4 
0.04189 0.00014 alter session set NLS_CALENDAR="GREGORIAN" 4 
0.04203 0.00014 alter session set NLS_SORT="BINARY" 4 
0.04217 0.00017 alter session set NLS_COMP="BINARY" 4 
0.04234 0.00003 ...NLS: Set Decimal separator="." 4 
0.04237 0.00014 ...NLS: Set NLS Group separator="," 4 
0.04251 0.00013 ...NLS: Set g_nls_date_format="DD-MON-RR" 4 
0.04264 0.00013 ...NLS: Set g_nls_timestamp_format="DD-MON-RR HH.MI.SSXFF AM" 4 
0.04277 0.00032 ...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR HH.MI.SSXFF AM TZR" 4 
0.04309 0.00005 NLS of database and client differs, characterset conversion needed 4 
0.04314 0.00257 ...Setting session time_zone to +01:00 4 
0.04572 0.00005 NLS: Language=       

As you see the default language is set to: NLS_LANGUAGE = "AMERICAN"
But now the second task should be to switch the language to German but this doesn't happen. Log file shows action with an empty value: NLS: Language= #empty#
 
Issue happens in all browsers: FF, Chrome, IE

Somebody else had similar issues as I did:
https://forums.oracle.com/forums/thread.jspa?threadID=2477036&tstart=0

Hope some in the APEX universe can help me here?

Thanks