Recent Posts

Join the NextGEN group at APEX Connect 18

Von Tobias Arnhold → 4.20.2018
The #NextGEN community is planning an after conference evening activity. For that reason we published a German article with all details on doag.org. My dear friend Jonas translated the text via deepl.com/translator.

Original article:
Wir sind EINE Community!

Here you go:

------

Young people who are motivated to get involved in conference planning? Yes, that's right!
And these motivated youngsters bring 24 more youngsters far away from the Oracle APEX universe.
Before the "older ones" among us now fall into shock rigidity - stay relaxed, because we need you!

These 24 students have no idea of our special APEX community.
It's up to us to take them along and show them how much fun, passion and action they can expect in APEX everyday life.

How do you recognize the students? 
Long hair, pale skin, no crutch... well, seriously: all students wear orange bracelets.

What do we expect from you?
Talk to the students! Don't just make them feel like they're in the community, but show them what it means to live the APEX community membership.

How do you do it best?
In addition to the conference and community evening on Wednesday, there are other networking opportunities:
For example, anyone interested can meet after the Speaker Reception on Tuesday at Schweinske (Bolkerstr. 28) from 8:30 p.m. in Düsseldorf's Old Town.
On Wednesday, after the official evening event at the Apollo Varieté, a joint visit to Club El Papagayo, which you can find at Mertensgasse 2 (both on a self-payer basis).

A tip: Give the poor students a drink - this will certainly increase their attention to want to know something about APEX! :slightly_smiling_face:

With these requests and advice we hope as NextGEN to bring the young savages together with the established community.

See you at APEX Connect!

Your NextGen team:

Abby, Caro, Rebecca, Davide, Jonas, Matthias, Philipp, Sebastian and Tobias

------

See on Tuesday! =)

CREATE or COPY master data pages in APEX

Von Tobias Arnhold → 3.07.2018
In this blog post I just want to give a hint about the positive and negatives effects when you COPY master data pages for different master data tables.

Example:
You have two tables: DOCUMENT_CATEGORY and ORDER_CATEGORY
Both tables have the same columns :
ID, CATEGORY_NAME, DESCRIPTION
CREATED_ON, CREATED_BY, CHANGED_ON, CHANGED_BY

In APEX you have a Master - Detail view including 2 pages: report view and modal dialog


If you decide to copy both pages you have to adjust the following things:
Master Data Report:
 - Page Name
 - Breadcrumb
 - Navigation Menu
 - Report DDL: Table Name
 - Report Column Link
 - Create Button Link

Modal Dialog:
 - After Header - Automatic Row Fetch (Name, Table Name)
 - Processing - Automatic Row Processing (Name, Table Name)
 - After Processing - Branch
 - APEX Items (in case you have different names or use prefixes)

What is the advantage?
1. Security
Security settings get copied like "Page Authorization Scheme". Master data pages normally apply to only one role, for example: administration
2. Labels and messages get copied (items, buttons). Really helpful if you named them differently to the standard APEX naming.

What is the disadvantage?
1. Linking
If you forget to change the branches/links then you could end up on the wrong page changing the wrong data. But on the other side this is the first thing you check after you finished the editing of the pages.
2. Automatic processing
If you forget to update the table names then you end up changing the wrong data.

Conclusion:
It is a bit more risky to copy master data pages but it can save you a lot of time changing label names and to remember the security settings.

Interactive Report - Column background color

Von Tobias Arnhold → 3.01.2018
One of my customers needed an IR where half of the report columns should be visualized in another color.


It is really easy to integrate. Go into the report column attributes inside your "Page Designer" and add a "Static ID" for each column:

Column 1: rep_col_diffcolor_1
Column 2: rep_col_diffcolor_2
...


Now add this CSS snippet inside the page attributes:

.a-IRR-table tr td[headers*="rep_col_diffcolor_"]
{
    background-color: #99ccff;
}


The trick is to address every element by searching for the start part of the "Static ID" name.

APEX ist anders. Genau das gleiche gilt auch für die APEX Connect!

Von Tobias Arnhold → 2.27.2018
Es gibt zwei Dinge die ich in über 10 Jahren im Oracle Umfeld immer und immer wieder höre:

1. Die Community wird immer älter.
2. APEX ist aus Grund XY schlecht.

Was hat die APEX Connect damit zu tun?
Die APEX Connect beweist nun zum 4ten Mal, dass eine Community (wie die Technologie selbst) nicht stehenbleiben darf. Ohne die gemeinsame Arbeit an immer wieder neuen Angeboten wäre die Veranstaltung wohl nicht das was sie heute ist.

Deshalb gibt es dieses Jahr erstmals einen eigenen Track für Neueinsteiger, der von einigen DER APEX Experten präsentiert wird. Das bedeutet, Du bekommst als DOAG-Mitglied für 208 € nicht nur einen umfassenden Einstieg in die aktuelle APEX Version, sondern kannst Dir gleichzeitig noch die besten Tipps und Tricks vom Who is Who der Branche abholen.



Dabei ist es egal, ob Du Student, Azubi, ehemaliger DBA, Formsentwickler oder Quereinsteiger bist. Dieser Track ist für alle gleichermaßen perfekt geeignet: APEX for beginners am 24.04.2018


Ein eigener Newcomer-Track bedeutet aber noch viel mehr: Es öffnet die Tore für die neue Generation an APEX-Entwicklern.
Und hier ist die Community gefragt, denn neben den Studentengruppen, die über #NextGEN-Roadshows an den Universitäten bei der Connect teilnehmen, könnt ihr euren Auszubildenden oder Studenten für nur 340 € an allen drei Tagen der Konferenz inklusive Übernachtung und ÖPNV vor Ort teilnehmen lassen.


Wann gab es das schon mal, dass ein Oracle Event für die nächste Generation ein so vollumfängliches Gesamtpaket geliefert hat?
In 10 Jahren als Oracle Experte habe ich noch keines erlebt!

Nun zurück zum Anfang! Ich seh das so:
Die APEX und PL/SQL Community kämpft um die nächste Generation und bietet den kinderleichten Einstieg den es dafür braucht. Dazu gehört Mut, Begeisterung und Voraussicht. Dank dem Einsatz der DOAG, der #NextGEN-Community und den APEX Connect Verantwortlichen ist zumindest im Development-Umfeld der Verjüngungsprozess längst eingetreten.

Warum eine Technologie-Konferenz für einen jungen ITler so wichtig ist, beschreibt Carolin Hagemann:
"Ich selbst habe gerade am Anfang meiner APEX-Zeit als Student an der DOAG K+A teilgenommen. Neben den Erfahrungen persönlich mit den Referenten sprechen zu können (ich habe den "APEX-Raum" betreut) und direkt am Puls der Technologie sein zu können, hat mir gerade diese Zeit auch sehr bei dem Einstieg in die Community geholfen.
Und neben diesen doch eher weichen Faktoren gibt es durchaus auch andere Gründe an den Veranstaltungen teilzunehmen: Ist man erst einmal in dem Sog der APEX-Community gefangen, weiß man von den neusten Releases nur wenige Stunden nach der Veröffentlichung. Man profitiert von den Kontakten und bekommt auch schon einmal 20 Minuten nachdem man einem der APEX-Entwickler eine Frage gestellt hat, eine sehr ausgiebige Antwort ;) Das kann in vielen Situationen ein entscheidender Vorteil sein."

Jetzt seid ihr gefragt und gebt euren Azubis und Studenten die Möglichkeit teilzunehmen oder sponsert eine Wildcard, um ein neues Gesicht in unsere Community zu integrieren. Jeder der sich einmal mit dem APEX Virus infiziert hat, weiß wie schwierig es ist hiervon wieder los zu kommen. Also lasst uns gemeinsam möglichst viele neue Leute infizieren!


---
Danke an Carolin Hagemann und Matthias Nöll für deren wertvollen Input in den Artikel.

---
Links:
Werde Teil der NextGEN Community, dann schreibe einfach eine Mail an nextgen@doag.org

Dynamic LOV with Pipeline function

Von Tobias Arnhold → 1.18.2018
A new year brought me some new tasks. I had to take over a generic Excel import and the customer wanted some extension by checking if the join on the master tables were successful.

Unfortunate we were talking about a generic solution which meant that all the configuration was saved inside tables including the LOV-tables which were saved as simple select statements.

Goal:
Show all import rows/values which were not fitting towards the master data.

How did I fix it?


Source of LOV data:

Source of import data:


I made a little abstract data model so that you understand what I mean:
I have two tables "I_DATA" including the values from the import and "I_DYNAMIC_SQL" including the LOV statements.

-- ddl
  CREATE TABLE "I_DYNAMIC_SQL" 
   ( "ID" NUMBER NOT NULL ENABLE, 
 "SQL_STATEMENT" VARCHAR2(4000), 
  CONSTRAINT "I_DYNAMIC_SQL_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE
   ) ;

  CREATE TABLE "I_DATA" 
   ( "ID" NUMBER NOT NULL ENABLE, 
 "DATA_VALUE" VARCHAR2(1000), 
 "DYNAMIC_SQL_ID" NUMBER, 
 "DATA_GROUP" VARCHAR2(20), 
  CONSTRAINT "I_DATA_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE
   ) ;

-- data
REM INSERTING into I_DATA
SET DEFINE OFF;
Insert into I_DATA (ID,DATA_VALUE,DYNAMIC_SQL_ID,DATA_GROUP) values (1,'Jonas',1,'G1');
Insert into I_DATA (ID,DATA_VALUE,DYNAMIC_SQL_ID,DATA_GROUP) values (2,'Sven',1,'G2');
Insert into I_DATA (ID,DATA_VALUE,DYNAMIC_SQL_ID,DATA_GROUP) values (3,'Annika',1,'G3');
Insert into I_DATA (ID,DATA_VALUE,DYNAMIC_SQL_ID,DATA_GROUP) values (4,'Jens',1,'G4');
Insert into I_DATA (ID,DATA_VALUE,DYNAMIC_SQL_ID,DATA_GROUP) values (5,'FH Trier',2,'G1');
Insert into I_DATA (ID,DATA_VALUE,DYNAMIC_SQL_ID,DATA_GROUP) values (6,'TH Bingen',2,'G1');
Insert into I_DATA (ID,DATA_VALUE,DYNAMIC_SQL_ID,DATA_GROUP) values (7,'FH Trier',2,'G2');
Insert into I_DATA (ID,DATA_VALUE,DYNAMIC_SQL_ID,DATA_GROUP) values (8,'TH Bingen',2,'G2');
Insert into I_DATA (ID,DATA_VALUE,DYNAMIC_SQL_ID,DATA_GROUP) values (9,'Frankfurt UAS',2,'G3');
Insert into I_DATA (ID,DATA_VALUE,DYNAMIC_SQL_ID,DATA_GROUP) values (10,'TH Bingen',2,'G4');

REM INSERTING into I_DYNAMIC_SQL
SET DEFINE OFF;
Insert into I_DYNAMIC_SQL (ID,SQL_STATEMENT) values (1,'select d,r from (
   select ''Jonas'' as d, 1 as r from dual union all 
   select ''Sven'' as d, 2 as r from dual union all 
   select ''Jens'' as d, 3 as r from dual union all 
   select ''Annika'' as d, 4 as r from dual
)');
Insert into I_DYNAMIC_SQL (ID,SQL_STATEMENT) values (2,'select d, r
from (
   select ''FH Trier'' as d, 1 as r from dual
   union all
   select ''TH Bingen'' as d, 2 as r from dual
)');

It actually took some time to find a solution fitting my needs.
1. Fast
2. Easy to understand
3. Not tons of code

What I needed was some kind of EXECUTE IMMEDIATE returning table rows instead of single values. With pipeline functions I was able to do it:
create or replace package i_dynamic_sql_pkg as

  /* LOV type */
  type rt_dynamic_lov is record ( display_value varchar2(4000), return_value number );

  type type_dynamic_lov is table of rt_dynamic_lov;

  function get_dynamic_lov (
    p_lov_id number
  ) return type_dynamic_lov pipelined;
end;

create or replace package body i_dynamic_sql_pkg as
  /* global variable */
  gv_custom_err_message varchar2(4000);

  /* Function to return dynamic lov as table */
  function get_dynamic_lov (
    p_lov_id number
  ) return type_dynamic_lov pipelined is

    row_data rt_dynamic_lov;

    type cur_lov is ref cursor;
    c_lov cur_lov;

    e_statement_exist exception;

    v_sql varchar2(4000);
  begin

    -- 'Exception check - read select statement';
    select 
       max(sql_statement)
    into
       v_sql
    from i_dynamic_sql
    where id = p_lov_id;

    -- 'Exception check - result';
    if v_sql is null
    then
      gv_custom_err_message := 'Error occured. No list of value found.';
      raise e_statement_exist;
    end if;

    -- 'Loop dynamic SQL statement';
    open c_lov for v_sql;
    loop
       fetch c_lov 
       into 
         row_data.display_value,
         row_data.return_value;
       exit when c_lov%notfound;

       pipe row(row_data);
    end loop;
    close c_lov;

  exception
  when e_statement_exist then
      rollback;
      /*
      apex_error.add_error(
        p_message => gv_custom_err_message
      , p_display_location => apex_error.c_inline_in_notification
      );
      */
      raise_application_error(-20001, gv_custom_err_message); 
  when others then
      raise;
  end;
end;

Now I just had to create a SQL statement doing the job for me:
-- ddl
select
  da.data_group,
  da.data_value,
  /* check if a return value exist */
  case
    when lov.display_value is not null
    then 'OK'
    else 'ERROR'
  end as chk_lov_data_row,
  /* apply error check for the whole group */
  case
    when min(case
                when lov.display_value is not null
                then 1
                else 0
              end) over (partition by da.data_group)
        = 0
    then 'ERROR'
    else 'OK'
  end as chk_lov_data_group
from i_data da
/* Join on my pipeline function including the dynamic sql id */
left join table(i_dynamic_sql_pkg.get_dynamic_lov(da.dynamic_sql_id)) lov
on (da.data_value = lov.display_value)
order by da.data_group, da.dynamic_sql_id, da.data_value;

Result:

After the party is before the party

Von Tobias Arnhold → 10.20.2017
Now two months after the #pougtrip everything went back to normal. Except the planning for the next events.

The DOAG #NextGEN is currently starting a "roadshow" at German universities.

Start is the 07.11.2017 at the Trier University of Applied Sciences. We will have 3 presentations covering different technologies: SQL, JS, JSON, SVG, PL/SQL, REST and APEX.
But see for yourself: Oracle Vorträge an der Hochschule Trier


As you can see the agenda is made with APEX :). Inspired by the #pougtrip we plan free beer for all students!

This is our first test run to find out what it takes to get as much students as possible to listen to Oracle related technologies.

From my perspective the question is: What can an Oracle expert contribute to young professionals?

For example: handling real life problems and sharing the experience while using modern technologies. But I think most important is devotion.
You get inspired by an expert mostly depending on how dedicated this person is delivering the information. This event gives us the chance to inspire young professionals to see Oracle in a new light. See the possibilities and feel the devotion by the people who are working with the technology.

BTW: This event wouldn't be possible without two dedicated students. Thanks Rebecca and Abby!

The best Oracle technology week ever - Part 1: Helsinki and Stockholm

Von Tobias Arnhold → 9.07.2017
You remember my last blogpost describing how my #europeTour would be like:
#orclapex Europe tour 

It was a week with as little sleep as possible. Reasons:
 - traveling
 - just was to excited
 - to much party

It all started in Helsinki with the first Oracle APEX day in Finland.
I reached Helsinki with Richard Rieb around 10 o'clock in the evening. It was dark and cold, our taxi driver spoke 5 words to us but good hard rock music was played on the radio. We knew we have come to the right place.
At the next day we met the organizers (Heli and Marko), the other speakers (Carsten Czarski, Shakeeb Rahman, Matthias Nöll and Mathias Magnussen) and 30 other APEX interested people.
I was happily surprised to meet Matt Nolan from the FOEX crew.
Background story: This guy gave me, in hard times maybe 8 years ago, some good tips and kept my APEX passion alive.
I was even more surprised to meet Jari Lainen which created a really nice APEX example application and has an awesome blog as well. He mentioned to join a new APEX project soon. He will hopefully start writing then again.

The presentations were really good but see for yourselves:











Btw: If you think I do crazy stuff then I always find someone even more devoted like Heli who flew to Soul for the Oracle code conference in between the Finnish APEX event and the POUG in Krakow.

We the other presenters, except Mathias who had to fill the time gaps in between the conferences, took the cruiser ship to Stockholm.


Funny fact: beer at the bar was 6 €, one beer at the tax free shop costs 3,50 € and 24 for of them in a pack costs 20 €. You know what we did.

We enjoyed beers in a stormy night at the railing watching the ocean.
Tip: On a shaky boat just drink 5 beers and feels like you have drunken 10.

In Stockholm we got the perfect location including really tasty food during the first Oracle APEX day in Sweden. 60 participants were listening to our presentation. See for yourselves:










We got great feedback by the customers and enjoyed several nice talks. I even talked to a guy from Denmark who just came to participate at this particular conference. Thanks to the Swedish Oracle Usergroup (SWEOUG) making it possible. My special thanks goes to Daniel and Mathias.

We spent the night by my relatives as Richard discoverd that one of the beers got broken and drenched his backpack. :D Luckily he flew home the day after.

Next Morning at 5 o'clock we (Matthias and me) headed towards the Arlanda airport and took the flight to Berlin.
In Berlin we met the students for the "NextGEN goes POUG trip" (#pougtrip)

In case you want to look at all the tweets from the conference then search for #europeTour, #sweougApex17 or #orclapex.

Next time I will tell you all about the trip to the "POUG 2017". :) Look closely at the DOAG website.