When exporting an APEX app by a tool like SQLcl splitted in separated files, each SQL file will include arguments inside the Metadata export which can differ when exporting from different APEX instances. Depending on the amount of generated files, this may cause a huge number of merge conflicts when versioning the exported app in a tool like Git or Apache SVN, which have to be resolved manually. In this blog post, I would like to show an example how to reduce the amount of conflicts.
If you are using a tool for exporting the Apps, you may include a script which removes information that is not needed (or could be replaced) during an import. Most of the merge conflicts are caused by arguments like “p_default_id_offset” as well as “Manifest” information. While the Offset depends on the APEX instance from which you have exported, the Manifest gives various information about a single application component, and therefore usually remains identical. However, if different Application IDs are used on multiple APEX instances for the same application, some of this information (which relates to these IDs) may also differ between exports.
Based on the components your APEX app consists of, the folder structure can look different. While the application folder will always exist and which stores all application resources, including pages, build options, files and other related logics, your export could also include other folders like workspace. This is the case if you are using workspace related resources inside your application logics (e.g. Web credentials).
The Bash script below will do the following actions for all SQL files inside the given subfolders:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
#!/bin/bash # Go into the apex folder of application # this is where the install.sql file is located # Then run below commands for all sql files in specified subfolders # Adjustments: # Manifest: Delete every Manifest information # Offset Id: Replace any value of p_default_id_offset with 0 APEX_APP_BASE_FOLDER="apex" APEX_APP_SEARCH_SUBFOLDERS=("application" "workspace") for subfolder in "${APEX_APP_SEARCH_SUBFOLDERS[@]}" do if [ -d ./"$APEX_APP_BASE_FOLDER"/"$subfolder" ]; then find ./"$APEX_APP_BASE_FOLDER"/"$subfolder" -type f -name "*.sql" -exec sed -i -e '/-- Manifest/,/-- Manifest End/d' -i -e 's/p_default_id_offset.*/p_default_id_offset⟹0/g' {} + fi done |
The Manifest contains information about to which resource the component belongs to (e.g. App Id). This information is not needed for the import. The OffsetId is always linked to an APEX instance and ensures that the metadata for the Oracle APEX application definition does not collide with other metadata on the instance. This means, that the Offset value should always differ, especially if you are importing the same application (or components) to several workspaces on the same APEX instance. If you set the OffsetId to 0, you need to make sure that you are setting this Offset value – or even better, that APEX generates an appropriate one for you – before the APEX app gets imported. The Offset needs to be a positive integer. You have two procedures and one function in the package wwv_flow_application_install for generating, setting and getting the OffsetId value. They are accessable over the synonym APEX_APPLICATION_INSTALL.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- Generate a new Offset value before application import begin APEX_APPLICATION_INSTALL.GENERATE_OFFSET; end; -- Set the Offset value manually declare v_offset_id NUMBER; begin v_offset_id := DBMS_RANDOM.VALUE(100000000000, 999999999999); -- may cause collisions APEX_APPLICATION_INSTALL.SET_OFFSET(p_offset ⟹ v_offset_id); end; -- Get the current Offset value declare v_offset_id NUMBER; begin v_offset_id := APEX_APPLICATION_INSTALL.GET_OFFSET; end; |
When using an Interactive Grid with big data and mutiple columns, it may be helpful for the user to be able to filter the results on specific values. For this purpose, APEX offers the native filter functionality of the Interactive Grid, where the user can add filter options on single columns. This functionality may be inconvenient to some users as they would need to set each one by one.
There are some existing Oracle PL/SQL procedures like APEX_IR.ADD_FILTER which allow adding filter operations on columns on the side of the database. However, this is only available for Interactive Reports and requires the APEX page to be reloaded so that the filters can be set. Alternatively, the page items of Faceted Search and Smart Filters have been added to APEX recently, but they can be used (as of APEX 22.1) only in combination of a Classic Report, but neither with an Interactive Grid nor an Interactive Report.
In this blog post, I would like to show how the “addFilter” methods of the IG could be used to add several filters at the same time using Dynamic Actions and JavaScript without adding them manually over the “Actions” menu or reloading the page. Please note that these methods are not documented on Oracle documentation pages and are therefore not supported. They will be probably subject to change and may not work in the same way for future versions of APEX.
Available operators to use are:
The following image shows an example how to use page items for filtering on columns of an Interactive Grid:
The following JavaScript code used in a Dynamic Action can filter and refresh the columns of the Interactive Grid shown above. First, all page items are verified and checked whether values have been entered. Some of the characters, like the separator “:” of the page item P1_FILTER_JOB (Popup LOV), need to be replaced by Unicode representations. To be able to use a date value to search for, you may need to convert it to a specific date format like “YYYYMMDD000000” (e.g. 20220902000000). When using the BETWEEN operator, you can combine the values by a tilde (~).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 |
//an array with all filters we want to add when pressing Search var arrayFields = []; //counter controller to check whether it should save and refresh IG var cont = 1; //adding job filter if ($v('P1_FILTER_JOB')) { var searchStrings = $v('P1_FILTER_JOB').replace(/:/g, '\u0001'); arrayFields.push( { type: 'column', columnType: 'column', columnName: 'Job', operator: 'IN', value: searchStrings, isCaseSensitive: false }); } //adding hire date filter if ($v('P1_FILTER_HIREDATE')) { arrayFields.push( { type: 'column', columnType: 'column', columnName: 'Hire Date', operator: 'GT', value: $v('P1_FILTER_HIREDATE') + '000000', isCaseSensitive: false }); } //adding salary filter if ($v('P1_FILTER_SALARY_FROM') & & $v('P1_FILTER_SALARY_TO')) { arrayFields.push( { type: 'column', columnType: 'column', columnName: 'Salary', operator: 'BETWEEN', value: $v('P1_FILTER_SALARY_FROM') + '~' + $v('P1_FILTER_SALARY_TO'), isCaseSensitive: false }); } //adding on leave filter if ($v('P1_FILTER_ON_LEAVE')) { arrayFields.push( { type: 'column', columnType: 'column', columnName: 'On Leave', operator: 'EQ', value: $v('P1_FILTER_ON_LEAVE'), isCaseSensitive: false }); } //adding no manager filter if ($v('P1_CHECKBOX_NO_MANAGER') == 'Y') { arrayFields.push( { type: 'column', columnType: 'column', columnName: 'Manager', operator: 'N', value: 'null', isCaseSensitive: false } ); } //adding starts with filter if ($v('P1_FILTER_STARTS_WITH_COLUMN') & & $v('P1_FILTER_STARTS_WITH_EXPRESSION')) { arrayFields.push( { type: 'column', columnType: 'column', columnName: $v('P1_FILTER_STARTS_WITH_COLUMN'), operator: 'S', value: $v('P1_FILTER_STARTS_WITH_EXPRESSION'), isCaseSensitive: false } ); } //delete all filters var filters = apex.region('employees_ig').call("getFilters"); for (var i in filters) { var filderId = filters[i].id; apex.region('employees_ig').call("deleteFilter", filderId); } //iterate through the filters and refresh IG for (var filterObj of arrayFields) { if (filterObj.value) { apex.region('employees_ig').widget().interactiveGrid("addFilter", filterObj, { refreshData: (cont == arrayFields.length) ? true : false, save: (cont == arrayFields.length) ? true : false }); } cont++; } |
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.
The text informs the users that they can select a file using the file browser. Nevertheless, the new component has a simple design, which makes it for the user difficult to see 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.
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.
1 2 3 4 5 6 7 8 9 10 |
.apex-item-filedrop { border: 3px dotted #c7c7c7; background: #f7f7f7; border-radius: 5px; box-shadow: inset 0px 2px 6px 0px #d2d2d291; } .apex-item-filedrop.is-dragging { border-color: #025dad; } |
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.
First, we need to create the specification of the PL/SQL package with its contents as shown below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
create or replace package booking_dates_pkg as /* Calls procedure save_dates for each weekday * @param pi_coach: e.g. 'MWILHELM' - v('APP_USER') * @param pi_period_from: e.g. 23.04.2021 - Start date (DD.MM.YYYY) * @param pi_weekdays: e.g. '0:1:2:3:4' (Monday - Friday) * @param pi_period_to: e.g. 07.05.2021 - End date (DD.MM.YYYY) * @param pi_period_time: e.g. '12:00' - Daytime */ procedure all_weekdays_in_period ( pi_coach in varchar2 , pi_period_from in date , pi_weekdays in varchar2 , pi_period_time in varchar2 , pi_period_to in date ); /* Identifies each date of a passed weekday within the period * @param pi_coach: e.g. 'MWILHELM' - v('APP_USER') * @param pi_period_from: e.g. 23.04.2021 - Start date (DD.MM.YYYY) * @param pi_weekday: e.g. 0 (Monday) * @param pi_period_to: e.g. 07.05.2021 - End date (DD.MM.YYYY) * @param pi_period_time: e.g. '12:00' - Daytime */ procedure save_dates ( pi_coach in varchar2 , pi_period_from in date , pi_weekday in number , pi_period_time in varchar2 , pi_period_to in date ); end booking_dates_pkg; |
Next, the corresponding package body can be generated:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
create or replace package body booking_dates_pkg as procedure all_weekdays_in_period ( pi_coach in varchar2 , pi_period_from in date , pi_weekdays in varchar2 , pi_period_time in varchar2 , pi_period_to in date ) as begin for i in (select to_number(regexp_substr(pi_weekdays, '[^:]+', 1, level)) as day from dual connect by to_number(regexp_substr(pi_weekdays, '[^:]+', 1, level)) is not null) loop save_dates( pi_coach ⟹ pi_coach , pi_period_from ⟹ pi_period_from , pi_weekday ⟹ i.day , pi_period_time ⟹ pi_period_time , pi_period_to ⟹ pi_period_to ); end loop; exception when others then raise; end all_weekdays_in_period; ------------------------------------------------------------------------------------------------------------------- procedure save_dates ( pi_coach in varchar2 , pi_period_from in date , pi_weekday in number , pi_period_time in varchar2 , pi_period_to in date ) as l_day date; l_weekday_name varchar2(30); l_period_from_year number; l_period_to_year number; l_count_days number; l_count_holidays number; begin l_period_from_year := extract(year from pi_period_from); l_period_to_year := extract(year from pi_period_to); /* count all weekdays of a single weekday (e.g. Monday) within the period * function next_day() depends of the invoking session's NLS_DATE_LANGUAGE and does not allow the specification of NLS values to make it independent * workaround: to make function next_day() independent, use trunc(sysdate, 'IW') to get the first day of week (Monday) defined by ISO standard, * and add number of days pi_weekday to identify the name of the selected day of the week uniquely */ l_weekday_name := to_char(trunc(sysdate, 'IW') + pi_weekday, 'Day'); l_count_days := ( ( next_day ( pi_period_to - 7, l_weekday_name ) - next_day ( pi_period_from - 1, l_weekday_name ) ) / 7 ) + 1; for rec_days in 0..l_count_days-1 loop /* identify the weekday (subtract 1 day to include the start date as well) and all following occurrences within the period by calculating weekday+7*rec_days */ l_day := ( next_day ( pi_period_from - 1, l_weekday_name ) + 7 * rec_days ); select count(holiday_date) into l_count_holidays from table(german_holidays(l_period_from_year, l_period_to_year)) where holiday_date = l_day; if l_count_holidays = 0 then insert into appointments(date, booked, coach) values (to_date(to_char(l_day, 'DD.MM.YYYY') || ' ' || pi_period_time, 'DD.MM.YYYY HH24:MI'), 0, pi_coach); end if; end loop; exception when others then raise; end save_dates; end booking_dates_pkg; |
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.
Dates of public holidays should not be saved in the database. 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)
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
create or replace function easter_day ( pi_year in integer ) return date as v_k integer; v_m integer; v_s integer; v_a integer; v_d integer; v_r integer; v_og integer; v_sz integer; v_oe integer; v_os integer; v_day integer; v_month integer; begin v_k := floor(pi_year / 100); v_m := 15 + floor((3 * v_k + 3) / 4) - floor((8 * v_k + 13) / 25); v_s := 2 - floor((3 * v_k + 3) / 4); v_a := mod(pi_year, 19); v_d := mod((19 * v_a + v_m), 30); v_r := floor(v_d / 29) + (floor(v_d / 28) - floor(v_d / 29)) * floor(v_a / 11); v_og := 21 + v_d - v_r; v_sz := 7 - mod((pi_year + floor(pi_year / 4) + v_s), 7); v_oe := 7 - mod(v_og - v_sz, 7); v_os := v_og + v_oe; if (v_os ⟹ 31) then v_day := v_os; v_month := 3; else v_day := v_os - 31; v_month := 4; end if; return to_date(v_day || '.' || v_month || '.' || pi_year, 'DD.MM.YYYY'); end easter_day; |
Afterwards, the object types are created for the table function:
1 2 3 4 5 6 7 8 9 10 |
create type holiday_t as object ( holiday_date date, holiday_name varchar2(30), holiday_only_in varchar2(100), holiday_description varchar2(100) ); / create type holiday_tab as table of holiday_t; / |
After that, the table function can be created:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 |
create or replace function german_holidays ( pi_year_start in integer, pi_year_end in integer ) return holiday_tab pipelined as v_easter_day date; begin for y in pi_year_start .. pi_year_end loop v_easter_day := easter_day(y); pipe row ( holiday_t( to_date('01.01.' || y, 'DD.MM.YYYY'), 'Neujahrstag', '', 'Gesetzlicher Feiertag')); pipe row ( holiday_t( v_easter_day - interval '48' day, 'Rosenmontag', '', 'Frei')); pipe row ( holiday_t( v_easter_day - interval '2' day, 'Karfreitag', '', 'Gesetzlicher Feiertag')); pipe row ( holiday_t( v_easter_day + interval '1' day, 'Ostermontag', '', 'Gesetzlicher Feiertag')); pipe row ( holiday_t( to_date('01.05.' || y, 'DD.MM.YYYY'), 'Tag der Arbeit', '', 'Gesetzlicher Feiertag')); pipe row ( holiday_t( v_easter_day + interval '39' day, 'Christi Himmelfahrt', '', 'Gesetzlicher Feiertag')); pipe row ( holiday_t( v_easter_day + interval '50' day, 'Pfingstmontag', '', 'Gesetzlicher Feiertag')); pipe row ( holiday_t( v_easter_day + interval '60' day, 'Fronleichnam', 'BW, BY, HE, NW, RP', 'Feiertag in BW, BY, HE, NW, RP')); pipe row ( holiday_t( to_date('03.10.' || y, 'DD.MM.YYYY'), 'Tag der Deutschen Einheit', '', 'Gesetzlicher Feiertag')); pipe row ( holiday_t( to_date('01.11.' || y, 'DD.MM.YYYY'), 'Allerheiligen', 'BW, BY, NW, RP und SL', 'Feiertag in BW, BY, NW, RP und SL')); pipe row ( holiday_t( to_date('24.12.' || y, 'DD.MM.YYYY'), 'Heiligabend', '', 'Frei')); pipe row ( holiday_t( to_date('25.12.' || y, 'DD.MM.YYYY'), '1. Weihnachtsfeiertag', '', 'Gesetzlicher Feiertag')); pipe row ( holiday_t( to_date('26.12.' || y, 'DD.MM.YYYY'), '2. Weihnachtsfeiertag', '', 'Gesetzlicher Feiertag')); pipe row ( holiday_t( to_date('31.12.' || y, 'DD.MM.YYYY'), 'Silvester', '', 'Frei')); end loop; end german_holidays; |
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.
Welcome to my blog!
Here I write about topics in programming that interest me, mostly around web design and web development with HTML, CSS and JavaScript as well as Oracle APEX and PL/SQL.
Have fun reading. 🙂