APEX-AT-WORK by Tobias Arnhold

Browsing "Older Posts"

First impressions of developing an APEX plugin

Von Tobias Arnhold → 12.20.2010

After spending a couple of hours in building my first plugin. I came to the conclusion to create a plug-in which uses the UILayout technology. My first idea was to build my own solution. After studying the documentation of UILayout I only thought that's the tool I want to work with! :)

Now after I created a working template I thought of how to include this into a plug-in?

First idea: Don't change anything on the page template if you have a similar organization of your page (North, West, South, East, Center)!
You say what you want in what area of the UILayout (West, North, Center...) inside the plug-in. To achieve that you take ID's or Classes of the main elements of your page template.
Part of the standard page template:

Plugin settings:

As you see I almost only use the standard ID's. Only the west div is not part of the original template.

After I created a first easy solution. I thought about the source code and the available attributes in the APEX plugin (max. 10)! 10 Attributes would only be enough for the sizing of the areas and would never be enough for all settings I need.

Second idea: I will build several modular plug-ins where everyone creates a special part of the layout.
1. Layout creation and region assigning
2. Area sizing (Size, Min-Size, Max-Size, No area at all)
3. Look and Feel (background-color,border-color,...)
4. Place for more ideas like Dynamic Layout Add-ons

What do you think? Developing a plug-in like that makes it maybe more complicated to configure. On the other side I would need comma separated lists if I only would use one plug-in!

Your opinion is appreciated.

Anyway I wish everybody a Merry Christmas and a successful APEX year 2011!

APEX issues

Problem refreshing Interactive Report with Dynamic Action

Von Tobias Arnhold →

It happened now two times that I wanted to update an IRR by a Dynamic Action. It just did not worked! The Dynamic Action fired (I saw that by debugging with Firebug and APEX) but the Report did not update. Finding the solution costs me some minutes/hours!

Reason: I assigned no Region template to the IRR (User Interface > Template : No Template)

Solution: I created a new empty region template and used that instead of "No Template".

APEX issues

Ein paar nützliche Informationen zum APEX Autocomplete Item

Von Tobias Arnhold → 12.15.2010

1. Das Item basiert auf dem JQuery Plugin: Autocomplete. Wenn Sie nähere Informationen zur Funktionsweise suchen, dann schauen Sie auf dieser Seite nach.

2. Tyler Muth erstellte unter APEX 3 eine Erweiterung für APEX: jquery-autocomplete-for-apex

3. Seit APEX 4 ist es ein Standard Item Type

4. Innerhalb des APEX Items können Sie spezielle Einstellungen für diesen Item Type vornehmen. Die wichtigste Einstellung ist die "Search"-Einstellung, Sie beinhaltet "Exact" oder "Contains" zur Auswahl.
Der entscheidende Unterschied ist die Art der Suche!
Bei dem Exact Verfahren wird ein Select ähnlich diesem während der Laufzeit aufgebaut:


-- Exact
SELECT a.*
FROM (SELECT DISTINCT CITY AS RV
      FROM  address_data ad
      WHERE ad.country = :P1_COUNTRY 
      ORDER BY 1) a
WHERE "RV" LIKE :p$_search_string || '%' AND ROWNUM <= :p$_max_rows;

Wobei bei dem Contains Verfahren die INSTR Funktion verwendet wird:


SELECT   a.*
  FROM   (  SELECT   DISTINCT CITY AS RV
              FROM   address_data ad
             WHERE   country_id = :P1_COUNTRY
          ORDER BY   1) a
 WHERE   INSTR ("RV", :p$_search_string) > 0 AND ROWNUM <= :p$_max_rows;

Bei der Verwendung von Indizes kann diese Info von entscheidenden Vorteil sein.
Ich habe zu diesem Thema vor kurzem auch eine Forum Anfrage gestartet: Problem with Textfield autocomplete --> only uses the INSTR function

5. Speichern Sie Werte nach jeder Eingabe durch eine Dynamic Action in der APEX Session *:
Event: Lose Focus
Selection Type: Item(s)
Item(s): P1_CITY
TRUE Action: Execute PL/SQL Code
PL/SQL Code: null;
Page Items To Submit: P1_CITY

6. Löschen Sie den Cache der Autocomplete Ergebnisliste durch eine Dynamic Action *:
Event: Lose Focus
Selection Type: Item(s)
Item(s): P1_CITY
TRUE Action: Execute Javascript Code
Code: $('#P1_STREET').flushCache(); $('#P1_ZIP').flushCache();

7. Sie können durch ein wenig Aufwand auch direkt durch TAB in das nächstes Input Item springen (Achtung: Item zurück SHIFT-TAB ist in der Lösung nicht möglich!)
Event: Key Release
Selection Type: Item(s)
Item(s): P1_CITY
TRUE Action: Execute Javascript Code
Code:


var vEvent  = this.browserEvent.keyCode;
var vEl     = this.triggeringElement;
var vName   = this.triggeringElement.id;
var vButton = "9"; /* TAB Key */

/* Check ob Event TAB Press ist */
if (vEvent == vButton){

  if (vName == 'P1_CITY'){
    /* Flush Cache aller anderen Autocomplete Items */
    $('#P1_ZIP').flushCache(); $('#P1_STREET').flushCache();
    /* Sprung in naechstes Item - manuelle Vorgabe */
    setTimeout(function() {$('#P1_STREET').focus();}, 1);
  }

  if (vName == 'P1_STREET'){
    $('#P1_ZIP').flushCache(); $('#P1_CITY').flushCache();
    setTimeout(function() {$('#P1_STREET_NO').focus();}, 1);
  }
}

8. Aktuell fehlt noch das Lade-Icon das während der Suche im Item angezeigt wird. Dieses Problem wird in APEX 4.1 behoben sein. Workaround: APEX Page item Text Field with autocomplete doesn't show load icon

* Wichtig bei der Verwendung mehrerer Autocomplete Items auf einer Seite, da sonst der Such-Wert fehlen könnte!

Working on my first APEX Plugin

Von Tobias Arnhold → 12.12.2010

It gets time to start developing my first APEX Plugin. I thought first to develop an addon with the JQuery UI.Layout for APEX.

Layout manager like ExtJS or UI.Layout are nice but hard to integrate. Especially for the different APEX templates and there specific design.

Instead of using one of these plugins I develop my own one. Which only uses a dynamic left area (west) and makes the center area (body) depended on the left area.

My goal is that the new plugin runs nice with the new standard APEX templates. Older templates should easily be migrate able.

First pictures:

APEX examples

Preload images in APEX

Von Tobias Arnhold → 12.11.2010

Sometimes images doesn't appear immediately (for example loading icons called before onSubmit processes). Especially new users who doesn't have these images inside there browser cache could be happy to see them. :)

The workaround is quite easy. Just create a Dynamic Action like below:

Create Dynamic Action: preload_images
Event: Page Load
Action: Execute JavaScript Code
Code:


var a_imgList = new Array();
// Your images
a_imgList[0] = "/i/company/loading1.gif";
a_imgList[1] = "/i/company/loading2.gif";
a_imgList[2] = "/i/company/button1.gif";
a_imgList[3] = "/i/company/button2.gif";

var a_images = new Array()

for (i = 0; i < a_imgList.length; i++) {
    a_images[i] = new Image();
    a_images[i].src = a_imgList[i];
}

Create Dynamic Action

That's it! You probably would add this event on your login page 101.

Development tools

Troubleshooting in der Firefox Entwicklung

Von Tobias Arnhold → 11.20.2010

In der Entwicklung von APEX Anwendungen verwende ich ausschließlich den Firefox (FF) Browser. Zum abschließenden Test kommt dann der entsprechende Standard-Browser zum Einsatz. Für gewöhnlich ist das der IE und leider ist dieser nicht so gut für die Entwicklung geeignet wie der FF.
Manchmal kann es vorkommend das unter bestimmten Situationen der FF nicht das darstellt was er soll. Dies kann leicht mit einem anderen Browser gegen geprüft werden. In so einem Fall können folgende 3 Optionen angepasst werden, um den Fehler auf die Spur zukommen.

1. Starten Sie ihre Seite ohne den Zugriff auf Cache-Informationen
- Nutzen Sie für die temporäre Nutzung den Privat-Modus

- Oder für die ständige Nutzung die entsprechende Einstellung in den Firefox Einstellungen

2. Schauen Sie ob die Proxy-Einstellungen korrekt sind und ihr APEX Server über die Proxy-Ausnahmen definiert ist

3. Unter umständen verwenden Sie unterschiedliche Sprachen, manchmal kann es helfen die Original-Sprache der Anwendung bzw. der Daten zu definieren

Fehler dieser Sorte treten meist zu Beginn der Anwendungsentwicklung auf und sind während der Entwicklung sehr selten anzutreffen.

Select current APEX version and user schema

Von Tobias Arnhold → 11.19.2010

If you didn't know how you can easily select the current APEX version or the current APEX database user APEX_xxxxx. You can use these selects:


-- Select current APEX user over all_users/dba_users
select max(u1.username) as current_apex_user 
 from all_users u1 
 where u1.username like 'APEX%' 
 and REGEXP_LIKE (substr(u1.username,'6',1), '^[0-9]*$');

-- CURRENT_APEX_USER
---------------------
-- APEX_040000

-- Select current version over dba_registry
select comp_name, version from dba_registry
 where comp_name = 'Oracle Application Express';

-- COMP_NAME                    VERSION_NO
-------------------------------------------
-- Oracle Application Express   4.0.1.00.03  

-- Select current version over apex_release
select * from apex_release;

-- VERSION_NO     API_COMPATIBILITY    PATCH_APPLIED
----------------------------------------------------
-- 4.0.1.00.03    2010.05.13           null

DOAG

DOAG 2010 Eindrücke vom ersten Tag (16.11)

Von Tobias Arnhold → 11.18.2010

Nach einer echt teuflischen Anreise die aus Stau, Baustellen und jeder menge Regen bestand, konnte der eigentliche DOAG Tag nur besser werden.

Den ersten Vortrag hielt Sue Harper zum Thema SQL Developer 3.0: New Features:
Dem SQL Developer wurden wirklich Interessante neue Features integriert. Das für mich bisher beste neue Feature ist der Migrations-Assistent der fast komplett neu geschrieben wurde und auch schon in der EA1 fast anstandslos läuft. Online Migration vom MS SQL Server klappte schon recht gut in Version EA1 und die Migration von MS Access lief ja auch schon unter Version 2 sehr gut. Andere vorgestellte Features wurden der bisherigen Version EA1, noch nicht beigefügt oder sind noch etwas fehleranfällig. Ich rate auf den nächsten Release Kandidaten zu warten. Eventuell wird auch der Data Modeller in der neuen Version vollständig integriert sein.

Der anschließende Vortag handelte sich um APEX 4.0 Kollaboration mit Websheets & Team-Development von Marc Sewtz:
Endlich habe ich mal tiefere Einblicke in die wirklichen Hintergründe und die unterschiedlichen Funktionsweisen der neuen APEX Features nehmen können. Ein gelungener Vortrag mit einigen Neuigkeiten rund um Websheets und Team-Development.

Weiter ging es zum Raum Seoul: BMW ConnectedDrive auf Basis von Oracle Spatial (Carsten Czarski und Christian Hüls):
Einer der besten Vorträge an diesem Tag, der zeigte was mit heutiger Technik praktisch möglich ist. Diese Features würden mich glatt dazu bekräftigen mir einen BMW zuzulegen, aber mein Leasing läuft noch ne Weile! :) Egal... Super Vortrag von beiden Vortragenden!

Danach gab es ne praktische Session von Marc Sewtz und Patrick Wolf zu neuen APEX 4.0 Features:
Die meisten Dinge kannte ich bereits, allerdings gab es eine Neuigkeit die mir wirklich weiterhilft. Wie Update ich einen Report mit Dynamic Actions der auf dynamisch geänderten APEX Items in einer WHERE-Klausel reagieren soll: Report filtering with APEX 4.0, dynamic action style
Vielen Dank nochmal Patrick für den tollen Tipp. Die Session war klasse!

Tom Kyte mit dem Thema: Was machen wir noch immer falsch zum Thema (Man glaubt es kaum) PL/SQL!
Klasse Beispiele die einmal mehr aufzeigten wie wichtig es ist, erst einmal die Probleme richtig zu durchdenken und dann mit programmieren anzufangen. Dies gilt im übrigen auch für APEX Projekte! :)

Die nächsten Session musste ich für einige Interessante Gespräche ausfallen lassen.

Die letzte Session ging um APEX und Reporting mit BIRT (Michael Pergande): Interessante Lösung die aber leider kaum Doku in Verbindung mit APEX bietet. Ich denke im Moment ist als kostenlose Alternative, Jasper Reports die beste Lösung.

Eine tolle Veranstaltung die leider viel zu kurz war...

News rund um APEX

Von Tobias Arnhold → 11.12.2010

APEX Patch 4.0.2
Laut Joel Kallman wurde die APEX Testinstanz apex.oracle.com auf Version 4.0.2 umgestellt. Das bedeutet das in wenigen Tagen/Wochen das APEX Update freigegeben wird. Neuerung: 2 Neue Themes

Update des APEX Blog Aggregators:
Dimitri Gielis hat eine neue Version des APEX Blog Aggregators entwickelt. Dieser wird in den nächsten Tagen released. Er hat die Anwendung komplett neu in APEX 4.0 aufgesetzt und den Synchronisations-Mechanismus geändert. Schaut einfach mal rein: http://apexblogs.info
Meines Erachtens trägt dieser Blog-Aggregator entscheidend zur schnellen Verbreitung von APEX bei. Danke Dimitri für deine Mühen!

APEX 4.0 New Features - Wiederholung
Denes Kubicek und Dietmar Aust wiederholen ihr APEX 4.0 Training. Das Training findet vom 24-25.01.2010 statt. Weitere Infos unter: http://www.opal-consulting.de

DOAG 2010
Ich selber halte dieses Jahr keinen Vortrag auf der DOAG, aber vielleicht sieht man ja den einen oder anderen (am Dienstag den 16.11) zwischen all den APEX Vorträgen. Ich dachte ich könnte im Raum NRW oder Sachsen nen Vortrag zu APEX Navigationskonzepten halten. Mal sehen ob das die Zeit her gibt! ;)

Neuer APEX Blogger:
Es gibt auch einen neuen (Ok ist schon ein paar Tage her) Deutsch/Englisch sprachigen APEX Blog: Linh Dinh - http://www.dinh.de/wordpress/

Kleine Linksammlung rund um APEX Themen (damit ich nicht immer Google befragen muss!)

Tipps zu sicheren APEX Anwendungen/Session State Protection:
http://www.oracle.com/global/de/community/tipps/securitytipps/index.html
http://download.oracle.com/docs/cd/E14373_01/appdev.32/e11838/sec.htm
http://www.talkapex.com/2009/05/apex-page-access-protection-and-session.html
http://dgielis.blogspot.com/2007/03/session-state-protection-and-url.html
http://apps2fusion.com/at/64-kr/400-preventing-url-tampering-using-apex-session-state-protection

Einmalige APEX Anmeldung:
http://www.oracle.com/global/de/community/tipps/apex-mini-sso/index.html
http://dgielis.blogspot.com/2007/07/oracle-apex-behind-scenes-logout.html

APEX Private Interactive Reports:
http://dpeake.blogspot.com/2009/01/preserving-user-saved-interactive.html
http://www.talkapex.com/2009/10/saving-saved-interactive-reports-when.html

APEX schönere URL's:
http://www.inside-oracle-apex.com/nicer-url-for-an-oracle-apex-application/

APEX examples

Ne einfache Lösung für Oracle's Hochkomma Problem

Von Tobias Arnhold → 11.02.2010

Wer kennt es nicht:


DECLARE
   v_text VARCHAR2 (2000);
BEGIN
   v_text := 'insert into my_table (my_column) values (''Einfach zu ''''unübersichtlich''''!'')';
   EXECUTE IMMEDIATE v_text;
END;

Die Lösung beschreibt Christian Rokitta in seinem Blog:
http://rokitta.blogspot.com/2010/11/its-little-things-in-life.html


DECLARE
   v_text VARCHAR2 (2000);
BEGIN
   v_text := q'[insert into my_table (my_column) values (q'<Einfach zu 'unübersichtlich'!>')]';
   EXECUTE IMMEDIATE v_text;
END;

Danke Christian!

OHS

Oracle HTTP Server - OHS (Part of the Oracle Web Tier Utilities)

Von Tobias Arnhold → 11.01.2010

For all those who hasn't noticed and those who are watching out for the current version of the Oracle HTTP Server. It is now part of the Oracle Web Tier Utilities and can be downloaded at:
http://www.oracle.com/technetwork/middleware/downloads/fmw-11-download-092893.html

The 10g version is part of the Oracle 10gR3 Companion CD and is available at:
http://www.oracle.com/technetwork/middleware/ias/downloads/index.html

Oracle Web Tier contains the following primary components:
- Oracle HTTP Server (OHS)
- Oracle Web Cache
- Oracle Process Manager and Notification Server (OPMN)

More information about the HTTP Server can be found at:
http://www.orafaq.com/wiki/HTTP_Server_FAQ

Install guide:
http://download.oracle.com/docs/cd/E12839_01/doc.1111/e14260/toc.htm

A nice install guide with pictures was made by Sandeep Bafna and can be found at:
http://orafusionmiddleware.blogspot.com/2010/01/installation-of-web-tier-utilites-11g.html

German version:

Für all die, die noch nicht wissen wo Sie die aktuelle Version des Oracle HTTP Server finden können bzw. sich fragen was damit passiert ist. Es ist jetzt Teil der Oracle Web Tier Utilities (11g) und kann hier runtergeladen werden:
http://www.oracle.com/technetwork/middleware/downloads/fmw-11-download-092893.html

Die 10g Version ist Teil der Oracle 10gR3 Companion CD und kann hier runtergeladen werden:
http://www.oracle.com/technetwork/middleware/ias/downloads/index.html

Oracle Web Tier Utilities beinhalten die folgenden Kernkomponenten:
- Oracle HTTP Server (OHS)
- Oracle Web Cache
- Oracle Process Manager und Notification Server (OPMN)

Wenn Sie Informationen über die Funktionsweise des Oracle HTTP Server benötigen, dann schauen Sie doch mal hier rein:
http://www.orafaq.com/wiki/HTTP_Server_FAQ

Installationsanleitung:
http://download.oracle.com/docs/cd/E12839_01/doc.1111/e14260/toc.htm

Eine weitere Installationsanleitung mit Bildern kann hier angeschaut werden (Die Anleitung stammt von Sandeep Bafna und ist kein offizielles Dokument):
http://orafusionmiddleware.blogspot.com/2010/01/installation-of-web-tier-utilites-11g.html

APEX issues

Restore APEX Report Region Selects

Von Tobias Arnhold → 10.27.2010

Maybe you came in the situation when you created an APEX report and during your development you unfortunately saved it with the wrong selection.

It happened a couple of times to me...

I found a way how you can restore such a lost selection easily within a SQL statement:


select PAGE_ID,
       PAGE_NAME,
       REGION_NAME,
       TEMPLATE,
       STATIC_ID,
       REGION_SOURCE,
       SOURCE_TYPE
  from APEX_APPLICATION_PAGE_REGIONS
    as of timestamp sysdate - 100 / (24 * 60) 
  -- Minus 100 Minutes
 where APPLICATION_ID = '100'
   and PAGE_ID = 100;

Just use the Oracle flashback feature inside the APEX View "APEX_APPLICATION_PAGE_REGIONS" and restore it from your redolog files. Column REGION_SOURCE includes the original selection.

Tooltip

Tooltip der Extraklasse

Von Tobias Arnhold → 10.23.2010

Ich hatte ja vor nicht allzu langer Zeit schon erwähnt das ich ne Tooltip Anwendung erstellen wollte. Leider hat es die Zeit nicht ganz zugelassen.

Seit APEX 4.0 sind die Tooltips ja eh dynamisch und eigentlich brauch jetzt niemand mehr ne Speziallösung! Eigentlich...

Es gibt eine Tooltip Art die APEX noch nicht von Haus aus unterstützt: Moveover-Tooltips
Anzeige einer Infobox wenn ich mich über dem Item befinde.

Beispiel: http://www.ta-it-consulting.de/tooltip_example.html

Die Lösung basiert auf dem Quellcode von Swazz mit ihrem Tooltip Plugin: Boxover.
Paulo Vale schrieb bereits über die Integration dieses Tooltips. Damit auch die Deutsche Community dran teilhaben darf, dachte ich mir, ich erweitere die Anleitung und zeige was alles möglich ist.
Leider ist die offizielle Beispielseite nicht mehr Online. Die Javascript Datei könnt ihr hier runterladen: http://ta-it-consulting.de/boxover_tooltip.js

1. Installation - HTML Header:


<!-- TOOLTIP START -->
// Source code
<script type="text/javascript" src="#APP_IMAGES#boxover_tooltip.js"></script>

// Erweitertes Layout
<style type="text/css">
.hlpHeader{font-weight:bold;width:320px;font-family:arial;border:1px solid #6F6F6F;padding:3;fontsize:11;
color:#FFFFFF;background:#8F8F8F;filter:alpha(opacity=85);opacity:0.85;
}

.hlpBody{width:320px;font-family:arial;border-bottom:1px solid #6F6F6F;border-left:1px solid #6F6F6F;
border-right:1px solid #6F6F6F;padding:3;fontsize:11;color:#FFFFFF;background:#BFBFBF;filter:alpha(opacity=85);
opacity:0.85;
}
</style>
<!-- TOOLTIP END     -->

2. Konfiguration - Item > HTML Form Element Attributes:


Beispiel 1:
TITLE="header=[Information] body=[Hilfetext fuer dieses Item]"

Beispiel 2:
TITLE="cssbody=[hlpBody] cssheader=[hlpHeader] header=[Information] body=[Hilfetext]"

Beispiel 3:
TITLE="fixedrelx=[1] fixedrely=[-56] header=[Information] body=[Hilfetext]"

Anleitung:
http://opatia.com/js/BoxOverParameters.htm

Einstellungsmöglichkeiten:

Parameter	Mögliche Werte	Default	Beschreibung
header	Any character	blank	Specifies the header text of the caption
body	Any character	blank	Specifies the body text of the caption
fixedrelx	Any integer	N/A	Forces the X-coordinate of the caption to stay fixed (offset is relative to the annotated HTML element)
fixedrely	Any integer	N/A	Forces the Y-coordinate of the caption to stay fixed (offset is relative to the annotated HTML element)
fixedabsx	Any integer	N/A	Forces the X-coordinate of the caption to stay fixed (X is an offset relative to the body of the HTML document)
fixedabsy	Any integer	N/A	Forces the Y-coordinate of the caption to stay fixed (Y is an offset relative to the body of the HTML document)
windowlock	On / Off	On	Make caption stick to side of the window if user moves close to the side of the screen.
cssbody	Any defined style class	Built in styles	Specifies CSS class for styles to be used on caption body.
cssheader	Any defined style class	Built in styles	Specifies CSS class for styles to be used on caption header.
offsetx	Any integer	10	Horizontal offset, in pixels, of the caption relative to the mouse cursor.
offsety	Any integer	10	Vertical offset, in pixels, of the caption relative to the mouse cursor.
doubleclickstop	On / Off	On	Specifies whether to halt the caption when the user double clicks on the HTML element with the caption.
singleclickstop	On / Off	Off	Specifies whether to halt the caption when the user single clicks on the HTML element with the caption. - if both singleclickstop and doubleclickstop are set to "on", singleslclickstop takes preference.
requireclick	On / Off	Off	Specifies whether the user must first click the element before a tooltip appears. Intended for use on links so that information appears while the link is followed.
hideselects	On / Off	Off	Specifies whether to hide all SELECT boxes on page when popup is activated.
fade	On / Off	Off	Specifies whether to fade tooltip into visibility.
fadespeed	Number between 0 and 1	0.04	Specifies how fast to fade in tooltip.
delay	Any integer	0	Specifies delay in milliseconds before tooltip displays.

Dynamic Actions

Dynamic Actions Beispiel

Von Tobias Arnhold → 10.10.2010

Vor einer Weile wurde eine super Anleitung für die Verwendung von Dynamic Actions in APEX 4.0 auf der deutschen APEX Community Seite erstellt. http://www.oracle.com/webfolder/technetwork/de/community/apex/tipps/apex40-dynamicactions/index.html
Damit auch der letzte die Vorteile von Dynamic Actions versteht, habe ich auf dieser Anleitung basierend eine kleine Anwendung erstellt.

Beispiel: http://ta-it-consulting.de/trigger_example.html

Wer Interesse am Code hat oder nähere Infos zu APEX 4.0 bzw. Dynamic Actions benötigt. Kann mich unter tobias-arnhold@hotmail.de einfach anmailen.

Die nächsten Tutorials warten bereits...

Von Tobias Arnhold → 9.01.2010

In den nächsten Wochen und Monaten habe ich vor ein paar wirklich nützliche APEX Erweiterungen vorzustellen. In Form von Anleitungen und Beispielapplikationen soll jeder in die Lage versetzt werden, professionelle Anwendungen mit den neuesten Techniken zu entwickeln.

Die folgenden Themen stehen derzeit auf meiner Agenda:
- Tooltip Applikation: Viele Tooltip/Infobox Beispiele für APEX gibt es im Netz, nur keiner hat einen wirklichen Überblick darüber. Diese Applikation soll Abhilfe schaffen. Ein alter Post zeigt ein paar Möglichkeiten auf: All about Tooltips
- jExpand Report: Eine geniale Reportlösung mit der es möglich ist, Reports mit vielen Spalten auch auf kleinen Raum unter zu bekommen! Eine Implementierung als APEX Template wird das Ziel sein. Beispiel mit Testdaten
- JQuery Layouts: Ähnlich ExtJS ist es möglich bewegliche Seitenbereiche auch mit JQuery umzusetzen. Ein Ansatz der bisher kaum Gehör bei den APEX Entwicklern fand und endlich auch mit OpenSource Mitteln umgesetzt werden kann. http://fabrizioballiano.net/jquery-border-layout/ und http://layout.jquery-dev.net/ Bis dahin schaut doch mal in meine ExtJS Lösung rein: APEX-AT-WORK Developer Competition 2009 Edition

Ich denke das sind Themen die viele APEX Entwickler interessieren könnten. Deswegen gebt doch einfach mal ein kurzes Feedback zu meinen Ideen.

APEX 4.0 New Feature Training in Deutschland

Von Tobias Arnhold → 8.21.2010

Falls ihr es noch nicht gehört habt, im Oktober 2010 geben Denes und Dietmar einen Oracle APEX 4.0 New Feature Kurs. Für alle die mehr Erfahren wollen, schaut doch einfach mal rein: OPAL-Consulting - Oracle APEX 4.0: New Features

Silbentrennung (Hyphenation) in APEX (GER)

Von Tobias Arnhold → 8.20.2010

Viele Entwickler in APEX kennen das Problem professionelle gut strukturierte Reports zu entwickeln, deren Spaltenbreiten nicht über die gewünschten maximalen Breiten hinausragen.
Folgende Bedingungen erschweren die einfache Entwicklung von Reports:
- Bildschirmauflösung ist eingeschränkt (1024x768)
- Zu lange Texte in Reports (Spaltennamen, Spaltenwerte)
- Nutzung verschiedener Browser (Firefox, Internet Explorer 6, 7 und 8,Safari,...)
- Übersetzung in verschiedene Sprachen notwendig
- Nutzung unterschiedlicher Layouts mit verschiedenen CSS Eigenschaften

Vor einer Weile habe ich einen Tipp beschrieben der die Problematik ein wenig verbessern kann: Automatic linebreak if word breaks out of table element

Nun habe ich eine wirklich gut Lösung gefunden die mit ein paar einfachen Anpassungen erstaunliche Wirkungen in ihren APEX Reports hervorrufen kann. Das Zauberwort heißt Silbentrennung (Engl.: Hyphenation). Die Lösung besteht darin den dargestellten Text durch ein Javascript Plugin entsprechend der maximalen Feldbreite zu begrenzen.

Dazu wird nach jeder Aktualisierung eine Funktion gestartet, die die Felder entsprechend anpasst.
Die Anwendung heißt Hyphenator, kann in allen gängigen Sprachen dynamisch verwendet werden, ist ein Javascript Plugin und ist hier zu finden http://code.google.com/p/hyphenator/

Ein weiterer Entwickler hat ein passendes Jquery Plugin geschrieben: http://bitbucket.org/webvariants/jquery-hyphenator/wiki/Home

In dieser Kombination ist die Verwendung in APEX 3.2 oder 4.0 denkbar einfach.

Anleitung:
1. Datei downloaden
Downloaden Sie die aktuell stabile Version von hier http://hyphenator.googlecode.com/files/Hyphenator%202.5.0.zip und hier http://bitbucket.org/webvariants/jquery-hyphenator/get/v1.1.0.zip

2. Kopieren Sie das Jquery Plugin auf ihren Webspace (APEX /i/ Verzeichnis)
  /i/Hyphenator/Hyphenator.js (Umbennen von hyphenator.min.js)
  /i/Hyphenator/jquery.hyphenator.min.js">
3. Kopieren Sie den Ordner patterns aus dem Hyphenator Download in ihr Verz.
  /i/Hyphenator/patterns/de.js

4. Definieren des Plugins in ihrem APEX HTML Header


<!-- START: Hyphenator Plugin -->
<style type="text/css">
.hyph {
   /* WIDTH Parameter wird erst ueber Spalte definiert */
   display:block;
   /* DISPLAY:BLOCK ist immer zwingend erforderlich */
   overflow:hidden; float:left;
   /* Schneidet alles Sichtbare über der max. Groesse ab, dadurch entstehen keine Fehler beim verkleinern, IE6 Fehlverhalten */
   white-space:normal;
   /* Darf niemals: white-space:nowrap als Einstellung in einer Tabelle haben */
   word-wrap: break-word;z-index:999; 
   /* Fuer IE6 */
}
</style>
<script type="text/javascript" src="/i/Hyphenator/Hyphenator.js"></script>
<script type="text/javascript" src="/i/Hyphenator/jquery.hyphenator.min.js"></script>
<!-- ENDE: Hyphenator Plugin -->

5. Deaktivieren Sie in ihrem APEX Report Template folgenden CSS Einstellung
white-space:nowrap mit white-space:normal;


<!-- Column Heading Template -->
<th ... style="white-space:normal;z-index:999;word-wrap: break-word;">#COLUMN_HEADER#</th>

<!-- Column Template 1 -->
<td ... style="white-space:normal;z-index:999;word-wrap: break-word;">#COLUMN_VALUE#</td>

6. Report Spalten Anpassen


<!-- Column Heading: -->
<span class="hyph" style="width:50px;text-decoration: underline;">Anwendungsname</span>

<!-- HTML Expression -->
<span class="hyph" style="width:50px;">#NAME#</span>

Info: Hier definieren Sie die maximale Breite und die Option Hyphenation zu verwenden.

7. APEX Dynamic Action für Report anlegen

After Report Refresh Trigger mit javascript Ausführung: $('.hyph').hyphenate();
Im Idealfall sollte sollte ein ähnlicher Report wie in meiner Beispielanwendung herauskommen:
http://ta-it-consulting.de/hyphenation.html

Info 1: Die LANG Option im HTML Tag definiert die Übersetzungssprache <html lang="de">
Info 2: Das Plugin kann auch in APEX 3.2 verwendet werden, außerdem ist der IE6 ohne Probleme verwendbar.

Das Ergebnis ist eine universelle Lösung die all ihre Reportprobleme lösen könnte.

Der einzige Haken ist eine erhöhte CPU Auslastung auf der Clientebene. Also Vorsicht vor verschwenderischer Übersetzung.

Mal was anderes: Inzwischen ist auch der AAW-DBMON ONLINE: http://ta-it-consulting.de/dbmon.html

APEX examples

Debugging hint for your APEX pl/sql processes

Von Tobias Arnhold → 8.12.2010

Often I use to much pl/sql code inside my APEX processes. As we all know it is just better using complex code inside packages. In case you got trouble with your APEX code and you can't find a fast way debugging it. Just use some javascript popup boxes:


-- Test Box
htp.prn('<script>alert("TEST");</script>');

-- Box with item values showing an IF validation:
htp.prn('<script>alert("'||:P1_TEST1 || ' = ' || :P1_TEST2 || '");</script>');

Summer is coming to an end and it becomes time to blog some more. Right now I'm developing a new small APEX application which describes a way how you can easily use hyphenation inside your application. In that combination I also found a trick how you can prevent the misbehavior of IE6 with fixed column width inside your Reports.

Tobias

APEX examples

All about tooltips

Von Tobias Arnhold → 6.09.2010

There a couple of tooltip examples/HowTos for APEX available and it becomes time to get them all together:
Paulo Vale's solution: http://apex.oracle.com/pls/otn/f?p=25110:5
HowTo: http://apex-notes.blogspot.com/2008/01/javascript-tootip-integration.html

Dan McGhan's solution: http://apex.shellprompt.net/pls/apex/f?p=apex_demo:3
HowTo: http://www.danielmcghan.us/2008/06/ajax-item-help.html

Patrick Wolf's solution: http://www.inside-oracle-apex.com/using-tooltips-in-oracle-apex-applications/

Martin Giffy D'Souza solution: http://apex.oracle.com/pls/otn/f?p=20195
HowTo:
http://www.talkapex.com/2009/09/tooltip-help-in-apex-alternative-to.html

eDBA solution: http://application-express-blog.e-dba.com/?p=281

Flavio Casetta's solution: http://apex.oracle.com/pls/otn/f?p=38250:11:0

Carsten Cerni's solution: http://www.cc13.com/wordpress_21/2009/03/12/apex-jquery-und-cluetip/

Hmm I wonder what nice APEX Plug-ins can be build for functionality like that. :D

Post if there are more solutions available.

APEX examples

Access inner HTML/child elements

Von Tobias Arnhold → 6.02.2010

Sometimes when I worked with Reports and special HTML objects I came across the problem to access different in-line html elements by javascript.

This link provides really useful know how about the access of DOM elements:

http://www.javascriptkit.com/javatutors/dom2.shtml

Some javascript alertbox examples:
alert(document.getElementById(col_rownum).childNodes[1].value);
alert(document.getElementById(col_rownum).firstChild.innerHTML);

If one of you know even better documentation. Just add a comment.

APEX examples

Button with changing icon

Von Tobias Arnhold → 5.24.2010

A while ago I wrote how to create your own Javascript buttons inside a region:
http://apex-at-work.blogspot.com/2009/05/building-your-own-button-in-apex-region.html

Here the example about how to change the icon "on mouse over" event:
Changing Images on Mouseover Using JavaScript


<img
 id="#CURRENT_ITEM_ID#_btn"
 src="/i/unpressed-icon.gif"
 alt="Search"
 onclick="javascript:doSubmit('SEARCH')"
 style="cursor: pointer"
 title="Search"
 onmouseover="document.#CURRENT_ITEM_ID#_btn.src='/i/pressed-icon.gif'"
 onmouseout="document.#CURRENT_ITEM_ID#_btn.src='/i/unpressed-icon.gif'"
>

Update 01.06.2010:
In case you want to use it inside your report then use this code snippet:


SELECT...
  '<img src="/i/bilder/icon-unpressed.gif"'
  || ' id="btn_ID' || rownum || '" style="cursor: pointer"'
  || ' title="Save changes!" alt="Save changes!"'
  || ' onmouseover="document.btn_ID' || rownum || '.src=''/i/bilder/icon-is-pressed.gif''"' 
  || ' onmouseout="document.btn_ID' || rownum || '.src=''/i/bilder/icon-unpressed.gif''" />' as BTN_PRESS
FROM ...

AAW-DBMON geht in erster Beta Version unter APEX 4 EA3 ONLINE

Von Tobias Arnhold → 5.15.2010

The following post is targeted towards a German audience, thus it is in German:

Wie vor einer ganzen Weile versprochen habe ich es endlich geschafft ein erstes Beta Release der AAW-DBMON Anwendung Online zu bringen! Das Release läuft schon unter APEX 4.0! Also schaut es euch mal an. Es können aus administrativen Gründen keine Daten dynamisch generiert werden. Also gebt GROßE Zeiträume bei den Charts an.
AAW-DBMON - http://tryapexnow.com/apex/f?p=2508:1:
Account:
Admin, Password: admin
Monitoring, Passwort: monitoring

Die Beta-Tester erhalten in den nächsten Tagen das aktuelle Release + Anleitung.

APEX examples

Call Javascript function from APEX report column link

Von Tobias Arnhold → 5.14.2010

I know it is not really new but it is always good to have an example just for the case you don't have all code in your head! :D

Column-Link:
Link Text: <img src="/i/my_icons/small_icon.png" alt="Short preview">
Link Attribute: title="Short preview"
Target: URL
URL: javascript:fnc_preview('111',#ID#);

Tip: GIF's with empty background can be displayed by IE6. PNG files can be displayed by IE6.

EXTJS

onClick event over whole EXTJS tree node

Von Tobias Arnhold →

I while ago I had an issue with the ExtJS tree. Somebody mentioned that not all parts of an ext tree node (for example the icons or the empty area) get called by an onClick event. There are just two line of code you need to add to your tree:


tree.on('click', function(node){ // tree is the created ExtJS tree object
   fnc_openWindow(node.id); //this is the javascript function I call
});

Watch the whole post in the ExtJS forum to get more information about it:
http://www.extjs.com/forum/showthread.php?95597-Ext-tree-href-event-on-whole-div-element

APEX examples

Highlight last inserted rows inside a standard report

Von Tobias Arnhold → 5.13.2010

To show last updated rows inside a report isn't as hard as it sounds.
All you need is a time column which saves the insert date and some changes on your report template!

Go into your report template and change it like this:


Column Template 1: 
<td class="new_css_class_with_different_color" #ALIGNMENT# id="#COLUMN_HEADER#_#COLNUM#_#ROWNUM#">#COLUMN_VALUE#</td>
PL/SQL Condition:
to_date('#DATE_COLUMN#','DD.MM.YYYY HH24:MI:SS') > (SYSDATE - INTERVAL  '10' MINUTE)

Column Template 2: 
<td class="old_css_class" #ALIGNMENT# id="#COLUMN_HEADER#_#COLNUM#_#ROWNUM#">#COLUMN_VALUE#</td>

In this example all rows which are younger than 10 minutes will be shown with a different background color. Which is defined inside the CSS class.

APEX examples

Get radio button value with javascript

Von Tobias Arnhold →

Most of you know how to get an HTML session value inside APEX:
$x('P1_NAME').value
To get the current value from a radio button use this APEX function:
$f_ReturnChecked('P1_DEPARTMENT')


// 1. Item: HTML Form Element Attributes:
onChange="javascript:fnc_setRadioItem('P1_DEPARTMENT')";

// 2. Page: HTML Header
<script type="text/javascript">
function fnc_setRadioItem(v_radioItem)
{ 
  // Create AJAX request object with JavaScript class "htmldb_Get"
  // 1. Parameter: Appointed for 'Partial Page Refresh', not needed
  // 2. Parameter: Determined the actual application ID 
  // 3. Parameter: Defines the application process, I use a dummy value
  // 4. Parameter: For 'Partial Page Refresh': not needed
  var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=v_dummy',0);
  get.add(v_radioItem,$f_ReturnChecked(v_radioItem))
  gReturn = get.get();
  //html_GetElement(v_radioItem).value = v_radioItem;
  get = null; 
}
</script>

You can find even more functions inside the APEX documentation: API Reference - 9 JavaScript APIs

Tip für die deutsche Community: APEX AJAX und JQuery HowTo Beispiel

Run an executable file from your browser

Von Tobias Arnhold → 4.23.2010

Internet Explorer is the only browser which can run executable files. I don't know any other browser which supports this feature. Of course there are common reason why! With IE you need to use VBScript. As we all know it is not secure way of developing web application. So be careful in creating security rules inside your Internet Explorer.

This code shows two examples:


<HTML>
  <HEAD>
  </HEAD>
  <BODY> 
 
 <SCRIPT language = "vbscript">
 'define application
 dim v_prog
 
 'set command
 v_prog = "C:\Programme\7-Zip\7zFM.exe"
 
 'create a shell
 set v_shell = createobject("wscript.shell")

        'run application 
 v_shell.run(v_prog)
 </SCRIPT>
    
 <SCRIPT language = "vbscript">
  'set variables
  dim v_shell
  dim v_return
  dim v_prog
  
  'set command
  v_prog = "%SystemRoot%\system32\ping 192.168.0.1"
  
  'create a shell
  set v_shell = CreateObject("WScript.Shell")
  
  'run application
  v_return = v_shell.Run(v_prog,1,TRUE)
  
  'check return value
  if v_return = 0 then
   MsgBox "Server is up",64,"Server status"
  else
   MsgBox "Server is down",64,"Server status"
  end if
 </SCRIPT>
 
  </BODY>
</HTML>

How to: Visual Basic Scripting - http://msdn.microsoft.com/en-us/library/t0aew7h6%28v=VS.85%29.aspx

APEX issues

Get current url string

Von Tobias Arnhold → 4.19.2010

There are a couple of ways getting the url within javascript:
Netlobo.com - Get URL Parameters Using Javascript
mattwhite.me - Get URL Parameter in Javascript
It's all about this function: document.location.href

To get the url with APEX features just follow these posts:
Oracle Apex Notebook - Get server URL in Apex
APEX forum - Display current URL on page
APEX forum - How to get the Current page's URL to an Item?
APEX forum - How to get Current URL in Apex?

APEX examples

Fill select results with blank rows

Von Tobias Arnhold → 4.14.2010

To fill selects to a specific amount of rows with blank rows. Just use the rownum connect by trick:


-- select template
SELECT *
FROM (SELECT rownum, test_col
      FROM test_table t
      UNION ALL
      SELECT *
      FROM (SELECT rownum AS er, 'empty' 
            FROM dual CONNECT BY rownum <= 99) e
      WHERE e.er > (SELECT MAX(rownum) FROM test_table));

-- emp table example for 20 rows
SELECT *
FROM (SELECT rownum, ename, job
      FROM emp em
      UNION ALL
      SELECT *
      FROM (SELECT rownum AS er, '-' as er1, '-' as er2 
            FROM dual CONNECT BY rownum <= 20) e
      WHERE e.er > (SELECT MAX(rownum) FROM emp));

I tried the select inside the APEX SQL Workshop:

APEX examples

Open Windows directory with APEX (IE only)

Von Tobias Arnhold → 4.12.2010

All you need to open a MS file system folder with APEX is this little javascript:
Header part


<script type="text/javascript">
function fnc_window()  {w = open('C:\\Users', "winLov","scrollbars=yes,resizable=no,width=600,height=400");
if (w.opener == null)
w.opener = self;
}
</script>

Open window on page load


<body onload="javascript:fnc_window()">

About a comment from Carsten
Dies ist das richtige Select:
SELECT '\\apc' || pcnummer || '\c$' as LINK FROM table....;
Interessante Idee!

APEX examples

Open your browser window in fullscreen mode

Von Tobias Arnhold → 4.07.2010

I answered a question in the APEX forum about an automatic full screen mode for your APEX application.

There are a couple of ways how you can achieve this. Anyway there is no simple solution and each way has some bad sides too.

Solution 1: Use a temporary HTML page to open your application window


<HTML>
  <HEAD>
    <TITLE>Test</TITLE>
  </HEAD>
  <BODY>
  <script type="text/javascript">
   window.opener=self;
   // fullscreen only 
   window.open('http://YOUR_URL','','fullscreen=yes');
   // fullscreen with no bars inside your browser
   window.open('http://YOUR_URL','','fullscreen=yes,menubar=no,toolbar=no,location=no,directories=no,status=no,scrollbars=yes,resizable=yes');
 window.close();
  </script>
  </BODY>
</HTML>

Solution 2: Use ActiveX (IE only)


<HTML>
 <HEAD>
 <script type="text/javascript">
  function fnc_fullscreen()
  {
  var obj = new ActiveXObject("Wscript.shell");
  obj.SendKeys("{f11}");
  }
 </script>
 </HEAD>
 <BODY onload="javascript:fnc_fullscreen()"> 
 
 </BODY>
</HTML>

Solution 3: Set size after max screen size (not good for two screens, not tested yet)


<HTML>
 <HEAD>
 <script type="text/javascript">
 function fnc_fullscreen()
  {
  window.moveTo(0,0);
  window.resizeTo(screen.availWidth,screen.availHeight);
  }
 </script>
 </HEAD>
 <BODY onload="javascript:fnc_fullscreen()"> 
 
 </BODY>
</HTML>

Source: http://www.webmasterworld.com/webmaster/3792262.htm

If you found a better way fixing this issue. I would really like to hear it.

Interactive Report

Building IR filters with AJAX - not solved yet

Von Tobias Arnhold → 3.17.2010

I got stuck on a problem with Interactive Reports.
I wanted to build filters during the application runtime with AJAX. These filters should be created if an user makes changes with select lists or trees similar to my old example:
IR with dynamic rules

What I need now are exact filters on hidden or visible fields:

My idea was to use the apex_util.ir_filter procedure:

apex_util.ir_filter(
p_page_id in number, -- Page that contains an Interactive Report
p_report_column in varchar2, -- Name of the report SQL column to be filtered
p_operator_abbr in varchar2 default null, -- Filter type
p_filter_value in varchar2); -- Value of filter, not used for N and NN

With an application process:

-- application process AP_GET_CAT
declare
  V_CAT varchar2(100);
begin
 select c.cat_name into V_CAT from categories where c_id = :P1_C_ID;

 apex_util.ir_filter(
  p_page_id=>1,
  p_report_column=>'CAT_NAME',
  p_operator_abbr=>'EQ',
  p_filter_value=>V_CAT);

 htp.prn(V_CAT);
end;

and some javascript:

// js get cat data
function set_cat(v_id) {
  var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=AP_GET_CAT',0);
  get.add('P1_C_ID',v_id)
  $x('P1_C_ID').value = v_id;
  gReturn = get.get();
  get = null; 
}

The functionality itself worked well but a submit is required for the APEX procedure apex_util.ir_filter.

My other idea was to imitate the user actions with javascript only:

// js code: set IR filter
$x('apexir_COLUMN_NAME').value = 'CAT_NAME';
$x('apexir_STRING_OPT').value = '=';
$x('apexir_EXPR').value = $x('P1_CAT_NAME').value;
gReport.column.filter();

Unfortunately it doesn't work this way... But it feels close to a solution.

It works if I use a sql where clause for my IR like this:
SELECT s_id, sales, sales_info
FROM sales, categories
WHERE s_c_id = c_id
AND s_c_id = :P1_C_ID;

and some javascript:

// not tested
var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=AP_GET_CAT',0);
get.add('P1_C_ID',v_id)
$x('P1_C_ID').value = v_id;
gReturn = get.get();
init_gReport();gReport.reset();gReport.pull();
get = null;

Maybe one of you guys did already solve this task and could help me out here.

Forum entry: http://forums.oracle.com/forums/thread.jspa?messageID=4167521

Tobias

IE issues

Automatic linebreak if word breaks out of table element

Von Tobias Arnhold → 3.11.2010

I found a great source how to prevent word break outs in <th> and <td> elements:
Peter Bromberg's: FIREFOX / IE Word-Wrap, Word-Break, TABLES FIX
How does the break out looks like:

CSS code to prevent this issue:

.prevent_breakout
{
width: 250px;
white-space: pre-wrap; /* css-3 */
white-space: -moz-pre-wrap; /* Mozilla, since 1999 */
white-space: -pre-wrap; /* Opera 4-6 */
white-space: -o-pre-wrap; /* Opera 7 */
word-wrap: break-word; /* Internet Explorer 5.5+ */
}

With javascript it could look like this:

<script type="text/javascript">
 function set_lb(v_val, v_max_width){
  var v_browser=navigator.appName; 
  // ...
  if (v_browser=="Microsoft Internet Explorer")  
  {
    $x(v_val).style.width = v_max_width; // set width
    $x(v_val).style.wordWrap   = 'break-word';  // Internet Explorer 5.5+
  }else
  {
    $x(v_val).style.width = v_max_width; // set width
    $x(v_val).style.whiteSpace = 'pre-wrap'; // css-3
    $x(v_val).style.whiteSpace = '-pre-wrap';  // Opera 4-6
    $x(v_val).style.whiteSpace = '-o-pre-wrap';  // Opera 7
    $x(v_val).style.wordWrap   = 'break-word';  // Internet Explorer 5.5+
    $x(v_val).style.whiteSpace = '-moz-pre-wrap'; // Mozilla, since 1999
  }
</script>

Thanks Peter for this great hint!

Update 07.05.2010
Report Attributes > Column Attributes > Column Formatting > CSS Style:
overflow:hidden;float:left;width:250px;word-wrap:break-word;white-space:pre-wrap;display:block;

IE issues

Weird IE behaviour with JS function String.charAt(Index)

Von Tobias Arnhold → 3.10.2010

I had a real pain with the MS Internet Explorer and the javascript function String.charAt(Index)

I wanted to update a string and checked each value. IE was able to give just an empty value back. But it was not checkable!

if ((v_browser=="Microsoft Internet Explorer")&& (v_string.charAt(x)=='')&& (y+1==x) && (v_string.charAt(x+1)=='<'||v_string.charAt(x+2)=='<'))

Even the length was 1 and not 0!
Finally I tried this function String.charCodeAt(Index) with an alert box and voilà two values where prompted: 13 and 10

13 and 10 wait... LINEBREAK!

HELL why does it do such things...

Now I could check it!

if ((v_browser=="Microsoft Internet Explorer")&&(v_check_unicode=='13'||v_check_unicode=='10'||v_check_unicode=='NaN'))

My script works and I feel released.
Now I have to watch the end of Real against Lyon.. :D

Column header groups in APEX report

Von Tobias Arnhold →

Did you ever want a report header grouped like this:

Just add some html code into your report template > column headings > Before Column Heading:

Important to know:
- Changes like this have effect on every report you use with that report template
- Make a copy of your existing report template and use the new one instead

In my example I used 8 columns and three of them should have a group column above it. Use colspan="3" to stripe it over 3 columns.

Here the generated HTML code:

For APEX Interactive Reports watch there:
Dimitri Gielis: Group Headings in an Interactive Report (APEX)
And there:
Martin Giffy D'Souza: Column Groups in APEX Interactive Reports

EXTJS

AJAX based select list in APEX - Part 2

Von Tobias Arnhold → 3.01.2010

I couple of days ago I wrote about a way using AJAX based select lists in APEX.
After some hints from Peter Raganitsch I tried the ApexLib Framework but I run into some issues with ExtJS.
Error message:
vFieldValue is undefined
populateLovField(Object { name="pFieldId"}, Object { name="pLovEntry"})ApexLib_Full.js
populateDependingLovs(Object { name="pFieldId"})ApexLib_Full.js
(?)()ApexLib_Full.js
(?)(Object { name="pEvent"})ApexLib_Full.js
vAjaxRequest.add(pLovEntry.oUsedFi...ace(/\:/g, String.fromCharCode(1)));

Error part where somehow an ExtJS function got called:
vAjaxRequest.add(pLovEntry.oUsedFieldList[ii], vFieldValue.replace(/\:/g, String.fromCharCode(1)));

I found another solution from Carl Backstrom: Ajax Selects
I modified his script a bit and used it in my environment.

Using the ApexLib would have been much easier luckily I just had a really small application.

Good for us that APEX 4.0 will solve these problems by a new standard feature:
Oracle APEX 4.0: Cascading LOVs/Select Lists

EXTJS

AaW - DBMON First pictures / Erste Bilder

Von Tobias Arnhold → 2.22.2010

The following post is targeted towards a German audience, thus it is in German:

Anfang Februar habe ich über eine Oracle DB Monitoring Anwendung für die deutsche APEX Community berichtet: Whats about the German APEX users?
Endlich habe ich es geschafft ein paar Screenshots zu erstellen. Die Anwendung basiert auf APEX und ExtJS und sammelt derzeit folgende Informationen:
- Tablespace Auslastung
- CPU Auslastung
- Buffer Hitratio Auslastung
- Session Zähler
- PGA Auslastung
- Init.ora Paramter
- Aktueller DB Status

Daten werden durch den DBMS_SCHEDULER über ein PL/SQL Package zu spezifizierten Zeiten gesammelt und können dadurch rückwirkend ausgewertet werden.
Gerade für Oracle XE Umgebungen kann dieses kleine Tool von nutzen sein.

Derzeit suche ich noch Beta-Tester, um für Ende März eine erste Testversion auf Herz und Nieren zu überprüfen. Wenn Interesse besteht, schickt einfach eine Email an: Tobias Arnhold (tobias-arnhold@hotmail.de)

Hier die ersten Screenshots:

APEX examples

Set item value with AJAX

Von Tobias Arnhold → 2.17.2010

Update an APEX item dynamically with AJAX:


// 1. Item: HTML Form Element Attributes:
onChange="javascript:fnc_setValue('P1_VALUE')";

// 2. Page: HTML Header
<script language="JavaScript1.1" type="text/javascript">
function fnc_setValue(v_item)
{ 
  var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=v_dummy',0);
  get.add(v_item,html_GetElement(v_item).value)
  gReturn = get.get();
  html_GetElement(v_item).value = v_item;
  get = null; 
}
</script>

Denes described it more detailed (plus example) in his blog:
Setting Item Session State using Ajax

Have you seen the advanced freeze panes example like you can use in Oracle Forms?
Freeze Panes in an APEX Report: Now with columns

Updated script 22.02.2010:


// 1. Item: HTML Form Element Attributes:
onChange="javascript:fnc_setValue('P1_VALUE')";

// 2. Page: HTML Header
<script type="text/javascript">
function fnc_setValue(v_item)
{ // Works with select lists, radio buttons, ...
  var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=v_dummy',0);
  get.add(v_item,$v(v_item))
  // For Display as Text items add:
  // html_GetElement('v_item').innerHTML = $v(v_item);   
  gReturn = get.get();
  get = null; 
}
</script>

OracleXE

Making a connection from Oracle XE to MySQL with ODBC

Von Tobias Arnhold → 2.15.2010

After searching the Web I found a nice description from James Koopmann: Making a Connection from Oracle to SQL Server

Then I found a Metalink note: WIN NT - Generic Connectivity using ODBC [ID 114820.1]

What software do you need? MySQL ODBC driver

Now follow these steps:
1. Add a new file:
<ORACLE HOME>\hs\admin\initMYSQL_DB.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MYSQL_DB
HS_FDS_TRACE_LEVEL = OFF

#
# Environment variables required for the non-Oracle system
#
#set =

2. Update your Listener.ora (In my case I use the existing one):
...
(SID_DESC=
(SID_NAME=MYSQL_DB)
(ORACLE_HOME=F:\oracle\app\oracle\product\10.2.0\server)
(PROGRAM=hsodbc)
)
...

3. Update your TNSNAMES.ora and restart your Listener:
...
MYSQL_DB =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.1)(PORT=1521))
(CONNECT_DATA=(SID=MYSQL_DB))
(HS=OK)
)
...

4. Create database link inside your database:
create database link MYSQL_DB
connect to "test_db" identified by "test_db"
using 'MYSQL_DB';

5. SQL> select * from test_table@MYSQL_DB;

Issues I know about: Invalid identifier problem

APEX examples

AJAX based select list in APEX

Von Tobias Arnhold → 2.08.2010

I looked through the Web for a simple way using a select list with dynamic data exchange inside my APEX application. What I found was just amazing (Thanks to Scott):

ajax-select-list-code-generator for APEX
Select list code generator

Output:

OracleXE

MySQL/Oracle XE integration: Invalid identifier problem

Von Tobias Arnhold →

I linked a MySQL table into an OracleXE database (Short How to) and discovered a really strange behavior. When I tried an usual select about the MySQL table from my sqlplus client an error occurred: ORA-00904: "last_name": Invalid identifier

Here the whole description:


-- Error:
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as test_user
 
SQL> SELECT "last_name" FROM tbl_users@MYSQL_USER u; 
 
SELECT "last_name" FROM tbl_users@MYSQL_USER u
 
ORA-00904: "last_name": ungültiger Bezeichner
 
SQL> 

-- MySQL DDL TABLE:
DROP TABLE IF EXISTS 'my_sqldb'.'tbl_users';
CREATE TABLE  'my_sqldb'.'tbl_users' (
  'u_id' int(10) unsigned NOT NULL auto_increment,
  'last_name' varchar(50) NOT NULL,
  'forename' varchar(50) NOT NULL,
  'department_id' int(10) unsigned NOT NULL,
  PRIMARY KEY  ('u_id')
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

Solution:
Query MySQL table through Oracle APEX database using Oracle database link fails
Issue while querying MySQL tables through Oracle Generic Connectivity Using ODBC

Solution description:
...I changed the character set settings to "utf8" of v5.1.6 mysql odbc driver
through "Details -> Misc Options" as suggested and finally the problem was
resolved - the query returned the correct results...

TREE

Creating an ExtJS tree with PL/JSON

Von Tobias Arnhold → 2.04.2010

I searched for a smoother way building ExtJS trees in APEX and found a great post from Anja Hildebrandt: Nice trees with ExtJS (Erstellung eines ExtJS-Baums…)

She used the PL/JSON Utility from Lewis Cunningham: PL/JSON by jkrogsboell, lewiscunningham

During the time now Lewis developed a new version of the utility and Anjas code didn't work anymore. I updated her source code with the new version of PL/JSON 0.8.6.

First I show how you build JSON output with the PL/JSON scripts:
Example - ex4.sql


SQL> declare
  2    obj json;
  3    procedure p(v varchar2) as begin dbms_output.put_line(null);dbms_output.put_line(v); end;
  4  begin
  5    p('you can also put json or json_lists as values:');
  6    obj := json(); --fresh json;
  7    obj.put('text', 'Audi');
  8    obj.put('id', 100);
  9    obj.put('leaf', json_bool(true));
 10    obj.put('href', 'f?p=110:1:');
 11    obj.put('children', json_list('[{"text": "A4"},{"text": "A5"}]'));
 12    obj.print;
 13  end;
 14  /
 
 
you can also put json or json_lists as values:
{
  "text" : "Audi",
  "id" : 100,
  "leaf" : true,
  "href" : "f?p=305:1:",
  "children" : [{
    "text" : "A4"
  }, {
    "text" : "A5"
  }]
}
 
PL/SQL procedure successfully completed

PL/JSON forum example

 
SQL> 
SQL> DECLARE
  2    resultset json;
  3    row_list  json_list := json_list();
  4    columns1   json;
  5    num_rows  number := 2;
  6    columns_length  number := 3;
  7    procedure p(v varchar2) as begin dbms_output.put_line(null);dbms_output.put_line(v); end;
  8  BEGIN
  9    p('Data:');
 10    FOR i IN 1 .. num_rows
 11    LOOP
 12      columns1 := json();
 13      columns1.put('rownum', i);
 14      FOR x IN 1 .. columns_length
 15      LOOP
 16        columns1.put('column' || x, 'Testdata');
 17      END LOOP;
 18      row_list.add_elem(columns1.to_anydata);
 19    END LOOP;
 20    resultset := json();
 21    resultset.put('ResultSet', row_list);
 22    resultset.print;
 23  END;
 24  /
 
 
Data:
{
  "ResultSet" : [{
    "rownum" : 1,
    "column1" : "Testdata",
    "column2" : "Testdata",
    "column3" : "Testdata"
  }, {
    "rownum" : 2,
    "column1" : "Testdata",
    "column2" : "Testdata",
    "column3" : "Testdata"
  }]
}
 
PL/SQL procedure successfully completed
 
SQL>

Updated tree package of Anja


create or replace package PKG_EXTJS_JSON is

  -- Author  : AHILDEBRANDT
  --         : TARNHOLD
  -- Created : 10.08.2009
  -- Updated : 04.02.2010
  -- Purpose : check if category has sub-categories
  function hasChildren(i_cat_id in number) return boolean;

  -- Purpose : create JSON-Object for category;
  --           recursive
  function getJsonObject(i_cat_id in number default 0) return json;

  -- Purpose : function that calls getJsonObject and returns the result as string
  function getTreeDataDynamic return varchar2;

end PKG_EXTJS_JSON;

create or replace package body PKG_EXTJS_JSON is
/*
  -- Author  : AHILDEBRANDT
  --         : TARNHOLD
  -- Created : 10.08.2009
  -- Updated : 04.02.2010
  -- Purpose : check if category has sub-categories
*/
function hasChildren(i_cat_id in number) return boolean is
  v_count number:=0;
begin
  select nvl(count(*),0) into v_count from tbl_categories where c_par_id=i_cat_id and c_active = 'YES';
  if v_count>0 then
    return true;
  else
    return false;
  end if;
end;

/*
  -- Author  : AHILDEBRANDT
  --         : TARNHOLD
  -- Created : 10.08.2009
  -- Updated : 04.02.2010
  -- Created : 10.08.2009
  -- Purpose : create JSON-Object for category;
  --           recursive
*/
function getJsonObject(i_cat_id in number default 0) return json is
  v_json json:=json();
  v_row_list json_list := json_list();
  v_arr_id NUMBER;
  v_ele_id NUMBER;
begin

  if i_cat_id = 1 then -- if category is root then set root-node
    v_json.put(pair_name => 'id', pair_value => i_cat_id);
    v_json.put(pair_name => 'text', pair_value => 'Root');
  else -- else --> usual node; use category infos
    for cat in (select * from tbl_categories where c_id=i_cat_id and c_active = 'YES') loop
      v_json.put(pair_name => 'id', pair_value => i_cat_id);
      v_json.put(pair_name => 'text', pair_value => cat.c_name);
    end loop;
  end if;

  if not hasChildren(i_cat_id) then -- if node has no children
    v_json.put(pair_name => 'leaf', pair_value => json_bool(true)); -- mark as leaf
    v_json.put(pair_name => 'href', pair_value => 'f?p=110:1:'||v('APP_SESSION')); -- set a link if needed
    -- v_json.put(pair_name => 'href', pair_value => 'javascript:show_cat('||i_cat_id|| ');');  -- set a javascript function
  else -- sonst
    v_json.put(pair_name => 'leaf', pair_value => json_bool(false)); -- mark as node with children
    for child in (select * from tbl_categories where c_par_id=i_cat_id and c_active = 'YES') loop -- loop through all sub-categories
      -- create JSON-object for sub-category using recursive call and the append to array
      v_row_list.add_elem(getJsonObject(i_cat_id => child.c_id).to_anydata);
    end loop;
    v_json.put(pair_name => 'children', pair_value => v_row_list); -- add array for subcategories
  end if;

  return v_json;

end;

/*
  -- Author  : AHILDEBRANDT
  --         : TARNHOLD
  -- Created : 10.08.2009
  -- Updated : 04.02.2010
  -- Purpose : function that calls getJsonObject and returns the result as string
*/
function getTreeDataDynamic return varchar2 is
  v_json json:=json();
begin
  v_json:=getJsonObject(i_cat_id => 1);
  return('['||v_json.to_char||']');
end;

end PKG_EXTJS_JSON;

Utilities like this makes developing much more faster!

Whats about the German APEX users?

Von Tobias Arnhold → 2.01.2010

Why German? I come from and live in Germany! ;D
I haven't really provided lot's of APEX based information in German. This just has to change. What to do?
Last year I created a small Oracle monitoring application with APEX and ExtJS. It was developed to collect CPU, Tablespace, Init.ora, Session and even more information about Oracle databases. In combination with some other monitoring tools it was quite a good (cheap) alternative to some expensive tools.

More details soon (in German)...

APEX examples

Send HTML data with the POST method from APEX

Von Tobias Arnhold →

If you want to send data from your APEX application to an external website (like PHP) through the POST method. You can use the following example:

Add a new page (in my example it's page number 2)

Add a new button on the page where you want to send data from: BTN_SEND_DATE
With the following options:
Target is a: URL
URL Target: javascript:void(window.open('f?p=&APP_ID.:2:&SESSION.:::::', 'popup', 'toolbar=no,width=1024,height=768, resizable=yes,top=40,scrollbars=yes'));

On page 2: Add your variables
1 P2_ID Hidden -- Needs to be set before page load
2 T_NAME Hidden
3 T_DESC Hidden
3 T_USER Hidden
....

Set variable P2_ID before you open page 2

On page 2: Page Process - Before Header


BEGIN
IF :P2_ID IS NULL THEN
 :T_NAME:= 'Error - No data selected';
 :T_DESC := 'Error - No data selected';
 :T_USER := 'Error - No data selected';
ELSE
 -- Select data
 SELECT t.t_name,
        t.t_description,
        initcap(:P1_USERNAME)
 INTO   :T_NAME,
        :T_DESC,
        :T_USER
 FROM test_table               r
 WHERE t.t_id = :P2_ID;
END IF;
END;

On page 2: Page - Footer Text


<script>
html_GetElement('T_NAME').name = 'T_NAME';
html_GetElement('T_NAME').value= document.getElementById('T_NAME').value;
html_GetElement('T_DESC').name = 'T_DESC';
html_GetElement('T_DESC').value= document.getElementById('T_DESC').value;
html_GetElement('T_USER').name = 'T_USER';
html_GetElement('T_USER').value= document.getElementById('T_USER').value;

html_GetElement('wwvFlowForm').action='http://website/external.php';
html_GetElement('wwvFlowForm').method='post';
html_GetElement('wwvFlowForm').submit();
</script>

Forum entries which helped me a lot:
Thread: item name isn't what i specified
Thread: HTTP POST from APEX
Thread: HTML form in Apex page

Example page:
Jeff Eberhard's application

Thanks Jeff for your example and your posts about this topic!

APEX issues

APEX CSV exports with strange line breaks inside

Von Tobias Arnhold → 1.13.2010

I came across a problem with line breaks inside my automatically generated csv export.
It had to do with some field values which had some line breaks inside. I updated the effected columns like that:


select replace(replace(my_column,CHR(10),' '),CHR(13),' ') AS column_without_line_breaks,
from tbl_test

An alternative way could be this:
RTRIM(my_column, CHR(10) || CHR(13))

SQL Developer

Set up the SQL Developer layout/environment in English

Von Tobias Arnhold →

Just a short hint for those who wants to use the SQL Developer in an English environment.

You have to edit the following file:
<SQL_DEV_ROOT_PATH>\sqldeveloper\bin\sqldeveloper.conf

It's important to add these two lines:
AddVMOption -Duser.language=en
AddVMOption -Duser.country=US


IncludeConfFile ../../ide/bin/ide.conf

SetJavaHome ../../jdk

AddVMOption  -Doracle.ide.util.AddinPolicyUtils.OVERRIDE_FLAG=true

AddVMOption -Dsun.java2d.ddoffscreen=false

AddVMOption -Dwindows.shell.font.languages=

AddVMOption -Duser.language=en
AddVMOption -Duser.country=US

AddVMOption  -XX:MaxPermSize=128M


IncludeConfFile  sqldeveloper-nondebug.conf

Subscribe to: Posts ( Atom )