Improve Dropzone UX Design with CSS in APEX 20.2

With the release of APEX 20.2 in October 2020, a new Dropzone component for uploading files was added as a native component in the Page Designer. Until then, it was necessary to rely on existing external plug-ins for such an upload functionality. In this post, I would like to show how to optimise the design of the Dropzone component by adding a little piece of CSS code to the page.

Dropzone without customization

The text informs the website visitor that he or she can select a file using the file browser. Nevertheless, the new component has a simple design, which is why it’s not really visible that an upload area is available here and that the file can also be directly dragged into the area by “drag and drop”.

I have added a few lines of CSS code for the dropzone component to improve the User experience. The dropzone element should have a dotted border and a slightly darker background colour. Additionally, when holding a file and hover over the area, the border should be highlighted in blue.

Improved layout of the Dropzone element with border
Selecting a file and hovering over the Dropzone will show a blue border

Below is the CSS code I used that can be added to an APEX page as inline code or within a static file for the new appearance of the Dropzone component.

PL/SQL: Identify dates of weekdays within time period using NEXT_DAY() function

Today I would like to show an example to demonstrate how you can identify all dates of occurrences of a single weekday within a period of time in PL/SQL by specifying a weekday.

For a project, a form mask in APEX was needed on which the operator should be able to select one or more weekdays (Monday, Tuesday, etc.) within a period of time and specify a time of day and a coach. The aim was to calculate the dates of all selected weekdays between the start and end date with PL/SQL and to store the data in the database table “appointments”. End users can book a support slot on these days via a separate order mask.

Form mask to select the weekdays

First, we need to create the specification of the PL/SQL package with its contents as shown below:

Next, the corresponding package body can be generated:

Via the database function NEXT_DAY() the date of the weekday name passed in the second parameter will be returned which follows the specified date. As an example, NEXT_DAY(’23-AUG-2020′, ‘SUNDAY’) would return the following Sunday (August 30, 2020), even though 23 August is also a Sunday.

The parameter for determining the returning day of the week depends on the NLS_DATE_LANGUAGE of the calling session, at the same time the function does not allow the free specification of NLS values. For this reason, calling NEXT_DAY(’23-AUG-2020′, ‘SUNDAY’) may result in the ORA error ORA-01846: not a valid day of the week if a different language (English, in the example above) is stored for NLS_DATE_LANGUAGE within the session. To use the function independently and get the weekday name in the language of the session, we use trunc(sysdate, ‘IW’) as a workaround to get the first day of the week according to ISO standard (Monday). To identify which weekdays have been selected on the website, colon-separated return values (from 0 (= Monday) to 4 (= Friday)) are passed as varchar from the APEX page to the procedure all_weekdays_in_period (e.g. ‘0:1:3’ for Monday, Tuesday and Thursday) that are added on top of Monday. Via to_char(trunc(sysdate, ‘IW’) + pi_weekday, ‘Day’) we get the selected weekday in the correct language of the session. After determining the specific date of the weekday, all following occurrences of this weekday can be identified by simple mathematical multiplications using a FOR loop. In order for the start day to be taken into account, we use NEXT_DAY(〈start date〉-1, 〈weekday name〉) to obtain the date of the day preceding the start date and use this for the calculations. Via the calculation NEXT_DAY( pi_period_from – 1, l_weekday_name ) + 7 * rec_days we get all further occurrences of this weekday with each run of the loop.

Holidays are not to be stored when querying the daily data. To check whether a date represents a public holiday, the function german_holidays described in the previous blog post is used at this point.

Related posts:
How to Find the Next Business Day and Add or Subtract N Working Days with SQL (blogs.oracle.com)
Make NEXT_DAY() independent of the invoking session’s NLS_DATE_LANGUAGE (community.oracle.com)
Add optional NLS_DATE_LANGUAGE parameter to NEXT_DAY() (community.oracle.com)

Calculation of public holidays via table function in PL/SQL

In this blog post I would like to show how to calculate public holidays in Germany within a period of time using a table function in the Oracle database.

The calculation of the dates for the public holidays is made on the basis of the date of Easter (Easter Sunday). I have taken the following PL/SQL code from a post written by Thomas Uhren on his blog Oracle SQL und PL/SQL: Rund um Oracle und die Verwendung von SQL und PL/SQL von Thomas Uhren and added and adapted dates to include further public holidays in the federal state of North Rhine-Westphalia in Germany.

Afterwards, the object types are created for the table function:

After that, the table function can be created:

This script is also used in my next blog post PL/SQL: Identify dates of weekdays within time period using NEXT_DAY() function. There I explain how to use PL/SQL to dynamically determine days of the week.

Hello World!

Welcome to my blog!

Here I write about topics in programming that interest me, mostly around Web design with HTML, CSS and JavaScript as well as Web development with Oracle APEX.

Have fun reading. 🙂