APEX-AT-WORK by Tobias Arnhold

Browsing "Older Posts"

Icon Collection with dynamic CSS code

Von Tobias Arnhold → 7.15.2013

Take a look at this little goody: http://apex.oracle.com/pls/apex/f?p=29999:1:
Nice idea.

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 examples

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 examples

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 examples

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 issues

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