Recent Posts

APEX CONNECT 2020 [ONLINE]

Von Tobias Arnhold → 4.28.2020
You may already noticed..
Anyway I'm glad to remind you that next week a full two day APEX online conference will be held:

APEX CONNECT 2020 [ONLINE]


We will have three parallel tracks divided in different topics and languages:
  • Track 1: APEX [🇩🇪]
  • Track 2: PL/SQL [🇩🇪]
  • Track 3: APEX and PL/SQL [🇬🇧]
Register now: https://apex.doag.org/en/home#c40443

We also plan a beer session on Tuesday evening (CET) so stay tuned on Twitter: #apexconn20

This is not all..
On Monday there will be an ACEs at Home APEX day including a nice presentation from my side:
Fighting climate change with Oracle - a worldwide initiative


utPLSQL - Example Package

Von Tobias Arnhold → 4.22.2020
A few months ago I got an awesome task to do inside the fabe project:
"Create an utPLSQL test package for the app authentication package"

Well, I never used utPLSQL before and (due to some hangovers at conferences 🥴) I could never really get a heads up towards that topic. Luckily I know one of the main developers Samuel Nitsche. He claims to be a Sith Lord, but for me, he is a true Jedi.
Anyway, I used the force to get him to help me getting a foot into that topic.

First, he gave me some resources.

Documentation:
http://utplsql.org/utPLSQL/latest/

My personal favorite was this article he wrote for the DOAG magazine:
https://cleandatabase.wordpress.com/2019/12/18/but-that-worked-yesterday-or-why-you-should-test-with-utplsql/
(German version: https://www.doag.org/formes/pubfiles/11888853/06_2019-Red_Stack-Samuel_Nitsche-Aber_das_hat_gestern_noch_funktioniert_Testing_mit_utPLSQL.pdf)

A slide that explains the basic usage:


After a little private introduction I made my first attempt and had a discussion with the other Fabe team members about the code usage and naming rules. Well naming rules sounds harsh and we had a long discussion on the topic. Why? Because it is a damn important thing with several long-term consequences. Finally we as a team decided to do it like this:

Test package name should be the same as the original package with an additional "test_" prefix.
For the AUTHENTICATION_PKG the corresponding utPLSQL package was called: TEST_AUTHENTICATION_PKG.

More important were the names for the procedures:
They should be as descriptive as possible. Luckily, fabe runs on  Oracle 19c and we don't have  a 30 character limitation.
For example:
  try_to_hijack_another_users_automatic_login
  send_a_mail_with_the_request_to_reset_the_password

The big advantage - compared to having test methods named after the test methods - is that you focus on the task / behavior and you can have as many test cases for one single procedure as necessary.

So lets get to the code..

The package header looked like this:
Info: This code piece does not include the complete test case for my package.
create or replace package test_authentication_pkg is
  -- %suite(Testing authentication logic)
  -- %suitepath(Authentication)
  -- %rollback(manual)
 
  -- run code:
  -- select * from table(ut.run('test_authentication_pkg'));

  -- %beforeall
  procedure generate_a_valid_apex_session;

  -- %beforeeach
  procedure create_a_test_user_in_user_profile;

  -- %context(Running side programms)
     -- %name(1_simple_procedures)
   
    -- %test(generate a hash value and check the output)
    procedure generate_a_hash_value_and_check_the_output;
   
    -- %test(check the email format of the test_user)
    procedure check_email_format_of_the_test_user;

  -- %endcontext

  -- %context(Authentication checks)
     -- %name(2_login_procedures)
   
    -- %test(verify a positive authentication)
    procedure verify_a_positive_authentication;
   
    -- %test(verify an authentication failure)
    procedure verify_an_authentication_failure;
   
    -- %test(generate a user login token)
    procedure generate_a_user_login_token;

  -- %endcontext
 
  -- %aftereach
  procedure remove_test_user;

end test_authentication_pkg;

I used a beforeall as well as a beforeeach process to setup my test case.
By using those annotations the procedures run automatically before the test cases (once / always).

The beforeall process created a valid APEX session which required a commit inside my test procedure. For that fact I had to manually clean up all test content and I had to use %rollback(manual)to get the package running.

I also used %context to be able to better organize my procedures.

Be aware that utPLSQL runs procedures not in a certain way as you defined it. That is the reason why I always recreate the test user and cleanup all data after each test procedure.
utPLSQL is intended to work like that. To check each test case independent from another one.

My package body looked like this.
create or replace package body test_authentication_pkg is
  -- global variables
  gc_user_id constant number := -1;
  gc_app_user constant varchar2(100) := 'uttest.authentication_pkg@fab.earth';
  gc_salt constant varchar2(100) := 'XYZ1235';
  gc_username constant varchar2(100) := 'uttest';
  gc_password constant varchar2(100) := 'ThisIsAValidTest';
  gc_password_hash constant varchar2(100) := 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'; 
   
  gc_creation_date constant date := to_date(to_char(localtimestamp,'dd.mm.yyyy') || ' 00:01', 'dd.mm.yyyy hh24:mi');


  --
  -- startup once before all procedures
  --
  procedure generate_a_valid_apex_session as
    l_user_id_in_session_state number;
  begin
    remove_test_user;
   
    apex_session.create_session(
      p_app_id => 100,
      p_page_id => 1,
      p_username => gc_app_user
    );
   
    APEX_UTIL.SET_SESSION_STATE (
        P_NAME => 'G_USER_ID',
        P_VALUE => gc_user_id
    );

    l_user_id_in_session_state := APEX_UTIL.GET_SESSION_STATE (
                p_item => 'G_USER_ID'
              );

    --check
    ut.expect(l_user_id_in_session_state)
      .to_equal(gc_user_id);

  end generate_a_valid_apex_session;
 

  --
  -- preparation: insert into user_table
  --
  procedure create_a_test_user_in_user_table as

    -- populate expected
    l_user_id_entries number;

  begin

    INSERT INTO user_table(
      user_id,
      app_user,
      active,
      username,
      first_name,
      last_name,
      bio,
      timezone_id,
      country_id,
      salt,
      password
    )VALUES(
      gc_user_id,
      gc_app_user,
      'Y',
      gc_username,
      'UT',
      'Test',
      'Internal test account which should not exist regularly.',
      168, -- Berlin
      86, -- Germany
      gc_salt,
      gc_password_hash --'ThisIsAValidTest'
    );

    select count(*)
    into l_user_id_entries
    from user_table
    where user_id = gc_user_id;

    --check
    ut.expect(l_user_id_entries).to_equal(1);

  end create_a_test_user_in_user_table;
 
  --
  -- cleanup from user_table
  --
  procedure remove_test_user as

    -- populate expected
    l_user_id_entries number;

  begin

    delete from user_pw_table
    where email_address = gc_app_user;
   
    delete
    from user_log_table
    where user_id = gc_user_id;

    delete
    from user_table
    where user_id = gc_user_id;

  end remove_test_user;


  --
  --
  -- Simple procedure checks
  --
  --

  PROCEDURE generate_a_hash_value_and_check_the_output IS
  BEGIN
      ut.expect(authentication_pkg.cust_hash(gc_app_user,gc_salt,gc_password))
        .to_equal(gc_password_hash);
  END;


  PROCEDURE check_email_format_the_test_user IS
  BEGIN
      ut.expect(authentication_pkg.email_format(p_email => gc_app_user))
        .to_be_true();
  END;


  --
  --
  -- Login tests
  --
  --

  PROCEDURE verify_a_positive_authentication IS
  BEGIN
      ut.expect(authentication_pkg.custom_authenticate(gc_app_user,gc_password))
        .to_be_true();
  END;

  PROCEDURE verify_an_authentication_failure IS
  BEGIN
      ut.expect(authentication_pkg.custom_authenticate(gc_app_user,gc_password||'X'))
        .to_be_false();
  END;


  -- Create an user login entry / token and create JWT SSO Token as cookie
  PROCEDURE generate_a_user_login_token IS
      l_actual   number;
     
      l_owautil_var owa.vc_arr;
      l_owautil_val owa.vc_arr;
  BEGIN
     
      -- Pre Test configuration
      -- intialize owautil for the generate_fingerprint procedure
      -- to prevent ORA-06502, ORA-06512: at "SYS.OWA_UTIL", line 354
      l_owautil_var(1) := 'HTTP_USER_AGENT';
      l_owautil_val(1) := 'Windows 10 Client with Firefox xxx.x';     
      owa.init_cgi_env( l_owautil_var.count, l_owautil_var, l_owautil_val );

      -- start test case
      authentication_pkg.generate_user_log (
        p_user_id => gc_user_id
      );
             
      -- verifiy user_log_table entry   
      select count(*)
      into l_actual
      from user_log_table
      where user_id = gc_user_id;

      ut.expect(l_actual).to_equal(1);
     
      -- remove test data
      delete from user_log_table
      where user_id = gc_user_id;
     
  END generate_a_user_login_token;

end test_authentication_pkg;

Now there is a lot to say about that package body let me start with the obvious.

1. I used global variables for all of the values my  test case procedures rely on.
Advantage: Saves time when I need to adjust the parameters. It is also more readable by using understandable variable names.

2. Use the API and keep the test case simple
Use the predefined API procedures.
ut.expect(...).to_be_true();
ut.expect(...).to_equal(1);
ut.expect(...).to_equal(gc_user_id);

A common way writing the test cases are looking similar to this example:
procedure test_action as
  l_actual number;
  -- populate expected
  l_expected number := 1;

begin
    l_actual := authentication_pkg.check_something(gc_app_user);
    ut.expect(l_actual).to_equal(l_expected);
end test_action;
I would recommend to do it like this:
procedure test_action as
begin
    ut.expect(authentication_pkg.check_something(gc_app_user))
      .to_equal(1);
end test_action;

Advantage:
Use the proper variable names instead of generic ones.
By splitting the procedure call and the result check in two lines. We can read it much better.
By adding the target procedure inside ut_expect, we spare an additional variable, create almost zero code and still keep the test readable.
Thanks to Samuel for showing me to code like this.

4. Use a custom APEX session procedure
Advantage: Independent code pieces like this should be extracted into separate procedures (separation of concerns). In that case you can focus on the real test case errors.

5.  Define your cleanup process
In my example remove_test_user I manually removed all data because in the original package I had some commits inside my called procedures. If you don't have that situation, instead you can  define a simple rollback process.
procedure remove_test_user as

begin
  rollback;

end remove_test_user;

6. Error ORA-06502, ORA-06512: at "SYS.OWA_UTIL"
      l_owautil_var(1) := 'HTTP_USER_AGENT';
      l_owautil_val(1) := 'Windows 10 Client with Firefox xxx.x';     
      owa.init_cgi_env( l_owautil_var.count, l_owautil_var, l_owautil_val );

Why? You need to pretend to be a client. :) Otherwise it will be null.

The result for the real procedure looks like this:
authentication
  Testing authentication logic
    Password checks
      reset the test user password [,041 sec]
      send a mail with the request to reset the password [,107 sec]
      verify the requested password activity [,025 sec]
      verify the signup password [,019 sec]
    Authentication checks
      verify a positive authentication [,055 sec]
      verify an authentication failure [,069 sec]
      generate a user login token [,084 sec]
      automatically recreate an apex session for the test user [,26 sec]
      try to hijack another users automatic login [,238 sec]
    Running side programms
      generate a hash value and check the output [,053 sec]
      check the email format from the test_user [,036 sec]

Finished in 1,076257 seconds
11 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)



Thanks again Samuel, Hayden and the whole Fabe team making this blog post possible.
It showed me once more what community spirit really meant.


Interactive Grid: Validation - Check for duplicated column entries over all rows

Von Tobias Arnhold → 4.01.2020
I had this situation now a few times and was always to lazy to write it down. :/

During my last task within the fabe project I hat to create a validation to check for duplicated entries inside an Interactive Grid.
Whenever I add "None of the above" twice, an error should occur:
This blog post from Lino Schilde was a good start for my final solution:

Interactive Grid Validation
Validation of Type: PL/SQL Function (returning Error Text)
Code:
declare
  v_cnt number;
begin
 
-- check only if insert or update (not delete "D")
if :APEX$ROW_STATUS in ('C','U') then

  -- select only if the current row is set to Y
  -- positive result if one answer was set to Y
  select max(case when none_yn = 'Y' then 1 else 0 end)
  into v_cnt
  from answer
  where question_id = :P301_QUESTION_ID
  and answer_id != nvl(:ANSWER_ID,0)
  and :NONE_YN = 'Y';
 
  if v_cnt = 1 then
    return 'Another answer was already set up with "None of the above". You need to change and save it first.';
  end if;

end if;
end;

My solution used a "max" aggregation within a "case when" trick to get the right result.

Maxime Tremblay gave me a really important tip:
If I add more then one row through the IG and press save. The validation is not gone be triggered.

To fix that you to add a unique index:
create unique index ANSWER_UK1 on ANSWER (
  case when NONE_YN = 'Y' then QUESTION_ID end
);



--
--

Tip of the day
All those meeting clients (Skype, Zoom, Hangouts ...) can also be used for APEX friends and beer meetups. #beer #orclapex

Interactive Grid: After Update Trigger

Von Tobias Arnhold → 3.29.2020
If you want to run a process after the "Interactive Grid" successfully updated all rows you can achieve this with a dynamic action. This can be necessary if you need to update certain columns calculated over several row in the same table you updated within the grid. Problem is to refresh the related data inside the grid as well.

Example:
You edit 3 rows for column A within your grid.
The grid updates column A row by row.
After that an update process should calculate a new result for column B which includes the data from all those updated 3 rows in column A.

Column A
row 1: 150 
row 2: 200
row 3: 100

Column B includes the sum of  column A
row 1: 450 
row 2: 450 
row 3: 450  

All you need to do is to define your Grid like this:
Interactive Grid > Advanced > Static ID: igYourGrid

Dynamic Action Event: Save [Interactive Grid]
Selection Type: Region
Region: Your Grid
Event Scope: Static

Action: Execute PL/SQL Code
custom_pkg.after_update_process;

Action: Execute Javascript Code
var model = apex.region("igYourGrid").widget().interactiveGrid("getViews").grid.model;
model.fetchRecords(model._data);




Be aware that the custom_pkg.after_update_process; process may not be executed when JavaScript errors occur.

This solution is completely dynamic and does not require a page submit.

--
--

Tip of the day
Since all conferences get cancelled worldwide join those online conferences instead:
ACE's at Home: https://itoug.it/aces-at-home/ (March 31th '20)
APEX@Home: https://asktom.oracle.com/pls/apex/f?p=100:551:::NO:551:P551_CLASS_ID:744 (April 16th '20)

Die inoffizielle APEX Connect Agenda

Von Tobias Arnhold → 4.01.2019
Noch etwas mehr als einen Monat bis zur APEX Connect 2019 und so langsam steigt bei allen die Vorfreude. Dieses Jahr wird es neben dem voll gepackten offiziellen Programmplan auch die ein oder andere Community Aktivität zu erleben geben.

Aber eins nach dem Anderen. Zunächst mal der Verweis auf das aktuelle Vortragsprogramm:


Am Dienstag den 07.05. ist um 9.00 Uhr der offizielle Startschuss und am 09.05. um 17:00 Uhr beenden wir die Connect mit einer Keynote von Jonathan Lewis.

Davor und dazwischen gibt es aber neben dem offiziellen Programm noch allerhand MEHR zu erleben.

Montag 17-21 Uhr: Meetup in der Uni Bonn (kostenlos)
Montag ab 21 Uhr: Gemeinsames Abendessen + 🍺; Ort - offen - (Selbstzahler)

Dienstag 18-20 Uhr: individuelles Abendbrot in der Nähe des Hotels
Dienstag ab 20:30 Uhr: Offene Fragerunde mit dem Oracle Development Team und das Beste aus den letzten Monaten.
Dienstag ab 22 Uhr: Ausklingen an der Bar (Selbstzahler)

Mittwoch 7:30 Uhr: 5k Fun Run
Mittwoch 18:00 Uhr: Dinner Cruise
Mittwoch ab 22 Uhr: Party Open End im Club "N8schicht" (Selbstzahler)


Damit ihr alles Wichtige auch während der Konferenz mit bekommt, meldet euch an Twitter an und folgt diesen beiden Hashtags:
#orclapex #apexconn19
 

And the APEX Coin goes to...

Von Tobias Arnhold → 3.05.2019
And the APEX Coin goes to...



Before I tell you.. Here is the story behind it!

Adrian Png is a Senior APEX developer and he is doing a lot of things for the APEX Community. For that reason he earned the APEX.WORLD Member of the year award.

His last outstanding idea was the APEX Challenge Coin!
In short: You can announce one or more great APEX developers who you think earn an APEX Coin.
But please read the full story here: Announcing the APEX Challenge Coin 


Of course I had to ask Adrian to send me 2 coins.


Unfortunately I know to many great APEX developers only in Germany. How in hell will I ever be able to choose one???

So my choice fell on 2 passionate people doing not only APEX. They lead and support our German APEX Community since years. Here are some examples:
 - APEX Meetup organizers
 - Member of the DOAG development community
 - Supporting the APEX Connect conference

But this is not the main reason!

The main reason is the passionate work in supporting and helping "Woman in Tech" (WIT) to become part of our wonderful APEX community.


And the APEX Coin goes to... Carolin Hagemann and Sabine Heimsath !






P.S.: As you can see on the picture I got 3 coins. So thanks Adrian for giving me one as well. :)

Der Fahrplan bis zur APEX Connect 2019

Von Tobias Arnhold → 2.22.2019
Im Mai 2017 hatte ich euch gefragt worauf ihr euch am meisten freut was die damals anstehende APEX Connect betraf.
Nun schreiben wir das Jahr 2019 und bis zur Connect stehen noch einige Events vor der Tür, bei denen es neben dem technischen Wissensaustausch vor allem auch um die Vernetzung und das teilen des gemeinsamen Spirits geht.

Und falls Ihr euch fragt weshalb ich immer und immer wieder von "Spirit" rede, dann schaut euch mal den Artikel "#NextGenTrip18 — Community at Its Best" im ORAWORLD Magazin auf Seite 39 an. Dieser repräsentiert das Wort "Spirit" ganz gut. :)



Hier nun der Fahrplan bis zur APEX Connect 2019

Meetups
Das nächste Meetup findet am 11.03. in München zum Thema "APEX 19.1 New Features" statt.
Die anderen Meetupgruppen befinden sich noch im Winterschlaf. News gibt es aus Dresden, dort wurde eine neue Meetup Gruppe gegründet. Das erste Treffen ist bereits in Planung.
Eine Übersicht aller Meetups findet ihr auf apex.world.
Eure Stadt fehlt? Ihr wollt aber trotzdem eine Gruppe gründen und wisst nicht wie? Schreibt mich einfach an (Twitter, Mail) und ich versuche euch dabei zu unterstützen.


Workshops
Am 18.03. findet ein kostenloser APEX Workshop in Trier statt inklusive Hands On. Beeilt euch die Plätze sind begrenzt.

In Frankfurt gibt es am 13.03 einen kostenlosen Oracle Datenbank-Technologie Tag.

Zwischen dem 09.-10.04 bietet die MT AG einen APEX Migrations-Workshop an. Geeignet für alle die endlich auf APEX 18.2 migrieren möchten.


APEX Competition
Bis zur Connect läuft parallel auch die APEX Competition 2019 bei der es darum geht ein APEX Plugin zu entwickeln. Der Gewinner erhält 850 €.
Die Competition läuft noch bis zum 07. April. Genug Zeit für eurer erstes Plugin, den passenden Einstieg findet ihr übrigens in diesem aufgezeichneten Webinar:
Entwicklung von Plug-ins für Oracle Apex – Kurzer Einstieg


Im Mai findet dann die heiligste aller APEX Veranstaltungen im deutschsprachigen Raum statt:

Die APEX Connect

Am Vortag der Connect dem 06.05. (Montag) wird es ein kostenloses Meetup geben, bei dem bekannte Oracle Core Entwickler die letzten News rund um APEX, PL/SQL und Javascript teilen werden.
Zwischen dem 07.05. und dem 09.05. dreht sich dann alles nur noch um die bekannten 4 Buchstaben 

A..P..E..X..

Neben APEX steht natürlich auch wieder SQL, PL/SQL, Datenmodellierung und Javascript im Fokus der Besucher. Highlights rund um das Event werden in den kommenden Wochen über unterschiedliche Blogposts detaillierter kommuniziert, bis dahin schaut euch doch mal die Vortragshighlights und den Bewertungsprozess näher an. Das komplette Programm findet ihr hier:

Mein Highlight dieses Jahr sind definitiv die Beginner Sessions.
Geeignet für Newcomer, Studenten, ehemalige Forms-Entwickler, DBA's und alle die APEX näher kennenlernen möchten.