Browsing "Older Posts"


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:


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:

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.


My personal favorite was this article he wrote for the DOAG magazine:
(German version:

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:

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('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) := '';
  gc_salt constant varchar2(100) := 'XYZ1235';
  gc_username constant varchar2(100) := 'uttest';
  gc_password constant varchar2(100) := 'ThisIsAValidTest';
  gc_creation_date constant date := to_date(to_char(localtimestamp,'') || ' 00:01', ' hh24:mi');

  -- startup once before all procedures
  procedure generate_a_valid_apex_session as
    l_user_id_in_session_state number;
      p_app_id => 100,
      p_page_id => 1,
      p_username => gc_app_user
        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'


  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;


    INSERT INTO user_table(
      'Internal test account which should not exist regularly.',
      168, -- Berlin
      86, -- Germany
      gc_password_hash --'ThisIsAValidTest'

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


  end create_a_test_user_in_user_table;
  -- cleanup from user_table
  procedure remove_test_user as

    -- populate expected
    l_user_id_entries number;


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

    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

  PROCEDURE check_email_format_the_test_user IS
      ut.expect(authentication_pkg.email_format(p_email => gc_app_user))

  -- Login tests

  PROCEDURE verify_a_positive_authentication IS

  PROCEDURE verify_an_authentication_failure IS

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

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

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;

    l_actual := authentication_pkg.check_something(gc_app_user);
end test_action;
I would recommend to do it like this:
procedure test_action as
end test_action;

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


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:
  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)
  v_cnt number;
-- 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;

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