List all installed plugins

After upgrading APEX I found this query useful to review all the plugins I had installed across multiple workspaces and in multiple applications to find ones that needed to be upgraded.

select name
      ,plugin_type
      ,version_identifier
      ,workspace
      ,application_id
      ,application_name
from APEX_APPL_PLUGINS
where workspace not in ('INTERNAL','COM.ORACLE.CUST.REPOSITORY')
order by name, workspace, application_id;

Load spreadsheet data into APEX

Face it: your users are in love with Microsoft Excel, and you can’t do anything about it.

You can show them the excellent Interactive Report and Interactive Grid features of APEX, and train some of your users to use some of their capabilities, but at the end of the day, your users will still download the data into their spreadsheet software to do their own stuff with it.

Once they’ve finished with the data, odds are they’ll come back to you and ask “how do I upload this data back into APEX?” and expect that the answer is merely a flip of a switch to enable a built-in APEX feature. Of course, you know and I know that this is not necessarily a simple thing; it is certainly not just an option to be enabled. Depending on what exactly they mean by “upload this data” it may be reasonably easy to build or it could get quite complex.

File Formats

Typically the data will be provided in some kind of text format (CSV, tab delimited, fixed width) or binary file (XLS or XLSX). If they have copied the data from a table in Excel, it will be in tab-delimited format in the clipboard. Perhaps in some odd instances the user will have received the data from some system in fixed width, XML or JSON format – but this is rare as this is typically part of the build of a system integration solution and users expect these to be “harder”.

Actual Requirements

When your user wants you to provide a facility for uploading data, there are some basic questions you’ll need to ask. Knowing these will help you choose the right approach and solution.

  1. Where are the files – i.e. are they stored on the database server, or is the user going to upload them via an online APEX application
  2. How much automation is required, how often – i.e. is this a ad-hoc, rare situation; something they need to do a few times per month; or something that is frequent and needs to be painless and automatic?
  3. What are the files named – i.e. if they are stored on the database server, do we know what the files will be called?
  4. How consistent is the data structure?

Data Structure

That last one is important. Will the columns in the file remain the same, or might they change all the time? If the structure is not amenable to automated data matching, can the file structure be changed to accommodate our program? Is the structure even in tabular form (e.g. is it a simple “header line, data line, data line” structure or are there bits and pieces dotted around the spreadsheet)? If it’s an Excel file, is all the data in one sheet, or is it spread across multiple sheets? Should all the sheets be processed, or should some of them be ignored? Can the columns vary depending on requirement – might there be some columns in one file that don’t exist in other files, and vice versa?

Finally, is all the data to be loaded actually encoded in text form? This is an issue where a spreadsheet is provided where the user has, trying to be helpful, highlighted rows with different colours to indicate different statuses or other categorising information. I’ve received spreadsheets where some data rows were not “real” data rows, but merely explanatory text or notes entered by the users – since they coloured the background on those rows in grey, they expected my program to automatically filter those rows out.

Solution Components

Any solution for processing uploaded files must incorporate each of the following components:

  • Load – read the raw file data
  • Parse – extract the text data from the file
  • Map – identify how the text data relates to your schema
  • Validate – check that the data satisfies all schema and business rule constraints
  • Process – make the relevant changes in the database based on the data

Each of these components have multiple solution options, some are listed here:

  • Load – External tables / SQL*Loader; APEX file select
  • Parse – External tables / SQL*Loader; APEX; 3rd-party code
  • Map – Fixed (we already know which columns appear where) (e.g. SQL*Loader Express Mode); Manual (allow the user to choose which column maps to which target); or A.I. (use some sort of heuristic algorithm to guess which column maps to what, e.g. based on the header labels)
  • Validate – Database constraints; and/or PL/SQL API
  • Process – INSERT; MERGE; or call a PL/SQL API

The rest of this post is focussed primarily on Parsing and Mapping Text and Excel files.

Solutions for Text files

These are some solutions for parsing text files (CSV, tab-delimited, fixed-width, XML, etc.) that I’ve used or heard of. “Your Mileage May Vary” – so test and evaluate them to determine if they will suit your needs.

  • External Table / SQL*Loader / SQL*Loader Express Mode (CSV, delimited, and fixed width files)
  • XML – native SQL using XMLType
  • APEX Data Workshop (CSV, delimited, and XML files)
  • APEX Data Loader Wizard (CSV and delimited files only)
  • Alexandria PL/SQL Library CSV_UTIL_PKG – Morten Braten, 2010
  • LOB2Table PL/SQL (CSV, delimited and fixed width files) – Michael Schmid, 2015
  • Excel2Collection APEX process plugin (CSV, delimited, XLS and XLSX files) – Anton Scheffer, 2013

It could be noted here that the Interactive Grid in APEX 5.1 and later does support Paste; if the user selects some data from Excel, then selects the corresponding columns and rows in the grid, they can Paste the tab-delimited data right in. Of course, this requires that the columns be in exactly the right order.

External Table

This uses the SQL*Loader access driver and you can look up the syntax in the Oracle docs by searching for “ORACLE_LOADER”. Since Oracle 12.1 you can use the simple “FIELDS CSV” syntax to parse CSV files, e.g.:

create table emp_staging
  ( emp_no number(4)
  , name   varchar2(10)
  , ...
  )
organization external
  ( default directory ext_dir
    access parameters
      ( records delimited by newline
        FIELDS CSV
        reject rows with all null fields
      )
    location ( 'emp.dat' )
  )
reject limit unlimited

Note that the filename (location) is hard-coded in the external table definition. Since Oracle 12.2 you can modify some of the attributes of an external table such as file name directly in the SELECT statement, without requiring any ALTER TABLE – Override External Table Parameters From a Query in Oracle Database 12c Release 2 (12.2).

SQL*Loader Express Mode

Since Oracle 12.1 the sqlldr command-line utility supports “Express Mode” which by default reads a CSV file, and loads the data into a given table. Read this quick intro here. This can come in handy for scripting the load of a number of CSV files on an ad-hoc basis into tables that already have exactly the same structure as those CSV files.

> sqlldr userid=scott/tiger table=emp 

This expects to find a file named “emp.dat” which contains CSV data to be loaded into the nominated table. Internally, it creates a temporary external table to load the data. Additional parameters can be added to change the filename, delimiters, field names, and other options.

APEX Data Workshop

The APEX Data Workshop is found under SQL Workshop > Utilities > Data Workshop and allows the developer to quickly load data from a CSV, tab-delimited, XML, or copy-and-paste from Excel into an existing or new table. This can be very handy when your client sends you some spreadsheets and you need to quickly load the data as one or more tables.

A related utility is the Create Application from Spreadsheet which does the same thing, plus creates a basic application to report and maintain the data in the new table.

APEX Data Loader Wizard

If your users need to load data on an ad-hoc, on-demand basis, and you don’t know necessarily what the structure of the files will be (e.g. the headings might change, or the order of the columns might change), you can use the APEX Data Loader Wizard to build an APEX application that guides your users in loading, mapping, and validating the data for themselves. If required you can customise the generated pages to add your own processing.

To create the wizard, click Create Page, choose Data Loading, and follow the steps to define the Data Load Definition and its associated pages. The definition determines the target table, the unique column(s) for data matching, transformation rules, and lookups (e.g. to get foreign keys for reference tables). Once this is done, the following four pages will be created for you:

You can customise these pages to modify the look and feel (e.g. moving some of the more complicated options in a collapsible region), or to add your own custom processing for the data.

For example, I will often add an additional process on either the 3rd page (to be run after the “Prepare Uploaded Data” process) that will do further processing of the loaded data. The process would be set up to only run When Button Pressed = “NEXT” and would have a Condition “Item is NULL or Zero” = “P111_ERROR_COUNT”. The result is that on the Data Validation (3rd) page, after reviewing the validation summary, the user clicks “Next” and the data is loaded and processed.

Alternatively, I sometimes want the user to view additional validation or summary information on the 4th page before doing further processing. In this case, I would add the process to the Data Load Results (4th) page, When Button Pressed = “FINISH”. For this to work, you need to modify the FINISH button to Submit the page (instead of redirecting). I also would add a CANCEL button to the page so the user can choose to not run the final processing if they wish.

Updating a Data Load Definition

The Data Load Definitions (one for each target table) may be found under Shared Components > Data Load Definitions. Here, you can modify the transformations and lookup tables for the definition. However, if the table structure has changed (e.g. a new column has been added), it will not automatically pick up the change. To reflect the change in the definition, you need to follow the following steps:

  1. Edit the Data Load Definition
  2. Click Re-create Data Load Pages
  3. Delete the new generated pages

This refreshes the Data Load Definition and creates 4 new pages for the wizard. Since you already had the pages you need (possibly with some customisations you’ve made) you don’t need the new pages so you can just delete them.

CSV_UTIL_PKG

The Alexandria PL/SQL Library includes CSV_UTIL_PKG which I’ve used in a number of projects. It’s simple to use and effective – it requires no APEX session, nothing but PL/SQL, and can be called from SQL for any CLOB data. It’s handy when you know ahead of time what the columns will be. You could read and interpret the headings in the first line from the file if you want to write some code to automatically determine which column is which – but personally in this case I’d lean towards using the APEX Data Loader Wizard instead and make the user do the mapping.

If you don’t already have the full Alexandria library installed, to use this package you must first create the schema types t_str_array and t_csv_tab. You will find the definition for these types in setup/types.sql. After that, simply install ora/csv_util_pkg.pks and ora/csv_util_pkg.pkb and you’re good to go.

In the example below I get a CSV file that a user has uploaded via my APEX application, convert it to a CLOB, then parse it using CSV_UTIL_PKG.clob_to_csv:

procedure parse_csv (filename in varchar2) is
    bl blob; cl clob;
begin

    select x.blob_content into bl
    from apex_application_temp_files x
    where x.name = parse_csv.filename;

    cl := blob_to_clob(bl);

    insert into csv_staging_lines
          (session_id, line_no, school_code, school_name
          ,line_type, amount, line_description)
    select sys_context('APEX$SESSION','APP_SESSION')
          ,line_number - 1
          ,c001 as school_code
          ,c002 as school_name
          ,c003 as line_type
          ,replace(replace(c004,'$',''),',','') as amount
          ,c005 as line_description
    from   table(csv_util_pkg.clob_to_csv(cl, p_skip_rows => 1))
    where  trim(line_raw) is not null;

end parse_csv;

Read more details on CSV_UTIL_PKG here.

LOB2Table

This PL/SQL package written and maintained by Michael Schmid parses CSV, delimited or Fixed-width data embedded in any LOB or VARCHAR2, including in a table with many records. The data can be read from any CLOB, BLOB, BFILE, or VARCHAR2. This makes it quite versatile, it reportedly provides excellent performance, and it includes a pipelined option. It will read up to 200 columns, with a maximum of 32K per record.

It requires execute privileges on SYS.DBMS_LOB and SYS.UTL_I18N and creates some object types and a database package. You can download the source from sourceforge. It appears to be well supported and was most recently updated in June 2018. I recommend checking it out.

select d.deptno, d.dname,
       t.row_no,
       t.column1, t.column2,
       t.column3, t.column4from dept d
cross join table( 
  lob2table.separatedcolumns(
    d.myclob, /* the data LOB */
    chr(10),  /* row separator */
    ',',      /* column separator */
    '"'       /* delimiter (optional) */
) ) t;

Excel2Collection

This is a process type APEX plugin written by Anton Scheffer (AMIS) in 2013, and has been actively maintained since then. You can download it from here or find it on apex.world.

The plugin detects and parses CSV, XLS, XML 2003 and XLSX files which makes it very versatile. It will load 50 columns from the spreadsheet into an APEX collection (max 10K rows). If you need to load larger spreadsheets you can send Anton a donation and your email address and he’ll send you a database package that can handle larger files.

Solutions for Excel files

The first thing to know about XLSX files is that they are basically a ZIP file containing a folder structure filled with a number of XML documents. Parsing an XLSX file, therefore, involves first unzipping the file, reading the relevant XML documents and parsing their contents. As usual with any Microsoft file format, the internal structure of these XML documents can be quite complex so I’d much rather leave the work to someone who has already worked out the details. Thankfully, this has largely been done.

These are some solutions for loading data from Microsoft Excel files (XLS, XLSX) that I’ve come across. Again, “YMMV” – so test and evaluate them to determine if they will suit your needs.

OOXML_UTIL_PKG

The Alexandria PL/SQL Library includes OOXML_UTIL_PKG which provides a number of utilities for parsing (and creating) XLSX, DOCX and PPTX files. It provides functions to get file attributes (including title, creator, last modified) and a list of worksheets in an XLSX. It provides functions to get the text data from any cell in any sheet, although for reasonable performance if you need more than one cell you should use the functions that returns a range of cells in one go.

Installation requires zip_util_pkg, sql_util_pkg, string_util_pkg, xml_util_pkg, xml_stylesheet_pkg, and ooxml_util_pkg, all of which are in the Alexandria library. Given this list of dependencies (and probably others I didn’t notice) I recommend installing the whole library – after all, there’s a lot of useful stuff in there so it’s worth it.

declare
  l_blob   blob;
  l_names  t_str_array := t_str_array('B3','C3','B4','C4','B5','C5');
  l_values t_str_array;
begin
  l_blob := file_util_pkg.get_blob_from_file('EXT_DIR','sample.xlsx');
  l_values := ooxml_util_pkg.get_xlsx_cell_values(l_blob, 'Sheet1', l_names);
  for i in 1..l_values.count loop
    dbms_output.put_line(l_names(i) || ' = ' || l_values(i));
  end loop;
end;

More details on OOXML_UTIL_PKG here.

Excel2Collection

As noted above, the Excel2Collection APEX plugin can detect and parse XLS, XML 2003 and XLSX files (as well as CSV files). The fact that it detects the file type automatically is a big plus for usability.

Apache POI

This solution involves installing Apache POI (“Poor Obfuscation Implementation”), a Java API for Microsoft products, into the database. The solution described by Christian Neumueller here parses XLS and XLSX files although it is admittedly not very efficient.

XLSX_PARSER

In 2018 Carsten Czarski posted a blog article “Easy XLSX Parser just with SQL and PL/SQL” listing a simple database package that parses XLSX files. This uses APEX_ZIP which comes with APEX, although using it does not require an APEX session or collections. It can load the file from a BLOB, or from APEX_APPLICATION_TEMP_FILES. It uses XMLTable to parse the XML content and return the text content of up to 50 columns for any worksheet in the file. It may be modified to support up to 1,000 columns.

To get a list of worksheets from a file:

select * from table( 
  xlsx_parser.get_worksheets( 
    p_xlsx_name => :P1_XLSX_FILE
  ));

To get the cells from a worksheet:

select * from table( 
  xlsx_parser.parse( 
    p_xlsx_name      => :P1_XLSX_FILE, 
    p_worksheet_name => :P1_WORKSHEET_NAME
  ));

I used this solution in a recent APEX application but the client was still on APEX 4.2 which did not include APEX_ZIP; so I adapted it to use ZIP_UTIL_PKG from the Alexandria PL/SQL Library. If you’re interested in this implementation you can download the source code from here.

ExcelTable

ExcelTable is a powerful API for reading XLSX, XLSM, XLSB, XLS and ODF (.ods) spreadsheets. It is based on PL/SQL + Java and reportedly performs very well. It requires a grant on DBMS_CRYPTO which allows it to read encrypted files. It includes an API for extracting cell comments as well. It can return the results as a pipelined table or as a refcursor. It knows how to interpret special cell values and error codes such as booleans, #N/A, #DIV/0!, #VALUE!, #REF! etc.

It includes API calls that allow you to map the input from a spreadsheet to insert or merge into a table, defining how to map columns in the spreadsheet to your table columns, which may improve throughput and may mean it uses less memory to process large files.

The API was written by Marc Bleron in 2016 and has been in active maintenance since then (latest update 22/10/2018 as of the writing of this article). You can read more details and download it from here: https://github.com/mbleron/ExcelTable

EDIT 17/12/2018: thanks to Nicholas Ochoa who alerted me to this one.

APEX 19.1 Statement of Direction

DISCLAIMER: all comments and code samples regarding APEX 19.1 in this article are based primarily on the Statement of Direction and are subject to Oracle’s “Safe Harbour” provision and must therefore not be relied on when making business decisions.

The SOD for the next release of APEX includes the following note, which is exciting:

“New Data Loading: The data upload functionality in SQL Workshop will be modernized with a new drag & drop user interface and support for native Excel, CSV, XML and JSON documents. The same capabilities will be added to the Create App from Spreadsheet wizard and a new, public data loading PL/SQL API will be made available.”

Whether this release will include corresponding enhancements to the APEX Data Loader Wizard remains to be seen; I hope the wizard is enhanced to accept XLSX files because this is something a lot of my users would be happy about.

APEX_DATA_PARSER

The most promising part of the APEX 19.1 SOD is the PL/SQL API bit, which will mean no plugins or 3rd-party code will be needed to parse XLSX files. It appears the package will be called APEX_DATA_PARSER, providing routines to automatically detect and parse file formats including XLSX, XML, JSON and CSV/tab-delimited files, e.g.:

select * from table(
  apex_data_parser.parse(
    p_content => blob,
    p_file_name => 'test.xlsx',
    p_xlsx_sheet_name => 'sheet1.xml'));
select * from table(
  apex_data_parser.parse(
    p_content => blob,
    p_file_name => 'test.xml'));
select * from table(
  apex_data_parser.parse(
    p_content => blob,
    p_file_name => 'test.js'));
select * from table(
  apex_data_parser.parse(
    p_content => blob,
    p_file_name => 'test.csv'));

Capability Matrix

CSV Fixed width XML XLS XLSX
External Table Yes Yes
APEX Data Workshop Yes Yes 19.1?
APEX Data Loader Wizard Yes
CSV_UTIL_PKG Yes
LOB2Table Yes Yes
Excel2Collection Yes Yes Yes
OOXML_UTIL_PKG Yes
Apache POI Yes Yes
XLSX_PARSER Yes
APEX_DATA_PARSER 19.1 19.1 19.1
ExcelTable Yes Yes

Have I missed a tool or 3rd-party code that you have found useful for parsing files to load into your APEX application? If so, please comment below.

This blog post is partially based on the following presentation delivered at the AUSOUG Connect 2018 Perth conference, November 2018.

Related


Custom Syntax Highlighting in SQL Developer

A few years ago I raised an enhancement request for SQL Developer to add custom highlighting, specifically to “grey out” all the calls to logger throughout my code. I blogged about this here.

Oracle SQL Developer 18.3 adds this feature with PL/SQL Custom Syntax Rules – and the best thing is, these rules are enabled by default so you don’t have to do anything. Any calls to logger, dbms_output and apex_debug will be greyed out.

You can customise the colour for this rule in Tools -> Preferences -> Code Editor -> PL/SQL Syntax Colors – the one for logger etc. is called “PlSqlCustom2”:

You can view (and edit, if you want) the rule in PL/SQL Custom Syntax Rules:

I’m not very familiar with the syntax but you can add additional libraries by adding more lines like this to the rule:

| ?grandChild = 'MYCUSTOMLOGGER'

The only thing missing is that it doesn’t pick up the standard logger variable declarations (scope and params). I haven’t worked out how to include these in the rule yet.


ODC Appreciation Day: why I’m “on Oracle” #ThanksODC

Hamersley Gorge, Karajini National Park, Western Australia. ©2018 Jeffrey Kemp

Pictured: Hamersley Gorge, Karajini National Park, Western Australia

Why is this blog called “Jeff Kemp on Oracle”?

Pretty much my entire career has been underpinned by the Oracle database. My first few I.T. jobs involved maintaining and building Oracle Forms and Reports. SQL and PL/SQL have been my indispensable two-pronged tool since day one and remain so today. Through fortuitous circumstance I had the opportunity to swap Forms for APEX quite a few years ago and I’ve been busy working on APEX applications since then.

The Oracle database is pretty old, and it’s huge and horrendously complex. It’s not the most popular – it certainly is among the “most dreaded” technologies in recent surveys; and (apart from XE) it is not cheap. But it unarguably has been, and still is, a major force to be reckoned with. When handled well it is still top of the line when it comes to performance and reliability.

Oracle APEX started out as a kid brother to Oracle Forms back in the mid-2000s, and has since quietly risen to be a nimble and capable development platform. It allows developers from different backgrounds to be productive very quickly.

  • If you’re a web developer skilled with HTML, CSS and Javascript, you can let APEX handle the database plumbing for you and build beautiful, functional and responsive web sites.
  • If you’re a PL/SQL nut like me, you can let APEX handle all the user interface and web browser complexities and build beautiful, functional and responsive web sites.

Either way, APEX gives you a starting point to learn the “other side”, whichever angle you came from.


Using apex_item.checkbox2 with multiple identifiers

Normally, in a report you can add a checkbox to select records like this:

select apex_item.checkbox2(1,x.id) as sel
      ,x.mycol
      ,...
from mytable x

And process the selected records using a process like this:

for i in 1..apex_application.g_f01.count loop
  l_id := apex_application.g_f01(i);
  -- ... process ...
end loop;

Since we have set the value of the checkbox to the record ID we can just get that ID from the resulting g_f01 array. What if we need multiple columns in our processing? There are a few approaches we could use:

Option 1. Re-query the table to get the corresponding data for the record ID

This is possible as long as the record ID is a unique identifier for the results in the report. A downside is that this involves running an extra query to get the corresponding data which might add a performance problem. An advantage is that the query can bring back as much data as we need – so if we need more than, say, 6 or 7 columns, this would be a reasonable approach.

Option 2. Concatenate the extra data into the checkbox value

For example:

select apex_item.checkbox2(1, x.col1 || ':' || x.col2) as sel
      ,x.mycol
      ,...
from mytable x

This requires parsing the value in the process, e.g.:

for i in 1..apex_application.g_f01.count loop
  l_buf := apex_application.g_f01(i);
  l_col1 := substr(l_buf, 1, instr(l_buf,':')-1);
  l_col2 := substr(l_buf, instr(l_buf,':')+1);
  -- ... process ...
end loop;

Option 3. Add extra hidden items to hold the data

select apex_item.checkbox2(1,rownum)
       || apex_item.hidden(2,rownum)
       || apex_item.hidden(3,col1)
       || apex_item.hidden(4,col2)
       as sel
      ,x.mycol
      ,...
from mytable x

Note: using “rownum” like this allows selecting the data from the row in the report, even if the underlying view for the report has no unique values that might be used.

Processing involves getting the selected rownums from the checkbox, then searching the hidden item (#2) for the corresponding rownum. This is because the g_f01 array (being based on a checkbox) will only contain elements for the selected records, whereas the g_f02, g_f03, etc. arrays will contain all elements from all records that were visible on the page.

for i in 1..apex_application.g_f01.count loop
  for j in 1..apex_application.g_f02.count loop
    -- compare the ROWNUMs
    if apex_application.g_f02(j) = apex_application.g_f01(i)
    then
      l_col1 := apex_application.g_f03(j);
      l_col2 := apex_application.g_f04(j);
      -- ... process ...
    end if;
  end loop;
end loop;

This approach helped when I had a report based on a “full outer join” view, which involved multiple record IDs which were not always present on each report row, and I had multiple processes that needed to process based on different record IDs and other data, which would have been too clumsy to concatenate and parse (as per option #2 above).


Many happy birthdays

Last year I *(not pictured) celebrated my 42nd circuit around the sun. In accordance with time-honoured tradition, it has been celebrated some time around the same day each September with variations on the following theme:

  • a get-together with friends and/or family
  • my favourite meal (usually lasagne)
  • my favourite cake (usually a sponge coffee torte, yum)
  • a gift or two
  • the taking of photographs, to document how much I’ve grown since the last one

Each year, determining the date this anniversary should fall on is a simple calculation combining the current year with the month and day-of-month. So, in the example of the special, but somewhat disadvantaged, people whose birthday falls on Christmas day (if you are among this select group, you have my sympathies), we could calculate their birthdays using a simple SQL expression like this:

with testdata as (
select date'2000-12-25' as d1
from dual)
select rownum-1 as age
      ,extract(day from d1)
       || '-' || to_char(d1,'MON')
       || '-' || (extract(year from d1)+rownum-1) as d1
from testdata connect by level <= 12;
AGE D1
0   25-DEC-2000
1   25-DEC-2001
2   25-DEC-2002
3   25-DEC-2003
4   25-DEC-2004
5   25-DEC-2005
6   25-DEC-2006
7   25-DEC-2007
8   25-DEC-2008
9   25-DEC-2009
10  25-DEC-2010
11  25-DEC-2011

Of course, as you should well know, this is wrong. It assumes that every year has every day that the anniversary might fall on. If a person is in that very special group of people who were born on the leap day of a leap year, our algorithm produces invalid dates in non-leap years:

with testdata as (
select date'2000-12-25' as d1
      ,date'2000-02-29' as d2
from dual)
select rownum-1 as age
      ,extract(day from d1)
       || '-' || to_char(d1,'MON')
       || '-' || (extract(year from d1)+rownum-1) as d1
      ,extract(day from d2)
       || '-' || to_char(d2,'MON')
       || '-' || (extract(year from d1)+rownum-1) as d2
from testdata connect by level <= 12;
AGE D1          D2
0   25-DEC-2000	29-FEB-2000
1   25-DEC-2001	29-FEB-2001 **INVALID**
2   25-DEC-2002	29-FEB-2002 **INVALID**
3   25-DEC-2003	29-FEB-2003 **INVALID**
4   25-DEC-2004	29-FEB-2004
5   25-DEC-2005	29-FEB-2005 **INVALID**
6   25-DEC-2006	29-FEB-2006 **INVALID**
7   25-DEC-2007	29-FEB-2007 **INVALID**
8   25-DEC-2008	29-FEB-2008
9   25-DEC-2009	29-FEB-2009 **INVALID**
10  25-DEC-2010	29-FEB-2010 **INVALID**
11  25-DEC-2011	29-FEB-2011 **INVALID**

This is because we are constructing a string which may or may not represent a real date in our crazy calendar system. So any self-respecting Oracle developer will know that the “correct” way of calculating this sort of thing is to use the ADD_MONTHS function, generously gifted to all of us for free:

with testdata as (
select date'2000-12-25' as d1
      ,date'2000-02-29' as d2
from dual)
select rownum-1 as age
      ,add_months(d1,12*(rownum-1)) as d1
      ,add_months(d2,12*(rownum-1)) as d2
from testdata connect by level <= 12;
AGE D1          D2
0   25-DEC-2000	29-FEB-2000
1   25-DEC-2001	28-FEB-2001
2   25-DEC-2002	28-FEB-2002
3   25-DEC-2003	28-FEB-2003
4   25-DEC-2004	29-FEB-2004
5   25-DEC-2005	28-FEB-2005
6   25-DEC-2006	28-FEB-2006
7   25-DEC-2007	28-FEB-2007
8   25-DEC-2008	29-FEB-2008
9   25-DEC-2009	28-FEB-2009
10  25-DEC-2010	28-FEB-2010
11  25-DEC-2011	28-FEB-2011

Hurrah, we now have valid dates (this is guaranteed by ADD_MONTHS), and those poor souls born on a leap day can still celebrate their birthday, albeit on the 28th of the month in non-leap years. Some of the more pedantic of these might wait until the following day (the 1st of March) to celebrate their birthday, but for the purpose of our calculation here the 28th is a little simpler to work with.

We package up our software and deliver it to the customer, it passes all their tests and it goes into Production where it works quite fine – for about a week or so.

Someone notices that for SOME people, whose anniversary did NOT fall on a leap day but were born on the 28th of February, are being assigned the 29th of February as their day of celebration in every leap year. However, not everyone has this problem: other people whose birthday is also on the 28th of February are being correctly calculated as the 28th of February whether it’s a leap year or not.

Obviously there’s a bug in Oracle’s code, somewhere. Maybe. Well, not a bug so much, this is due to the way that ADD_MONTHS chooses to solve the problem of “adding one month” when a “month” is not defined with a constant number of days. ADD_MONTHS attempts to satisfy the requirements of most applications where if you start from the last day of one month, the result of ADD_MONTHS will also be the last day of its month. So add_months(date'2000-06-30', 1) = date'2000-07-31',add_months(date'2000-06-30', 1) = date'2000-07-30', and add_months(date'2000-05-31', 1) = date'2000-06-30'.

Let’s have a look at those dates. There’s one person whose birthday was 28 Feb 2000 and our algorithm is setting their anniversary as the 28th of February regardless of year. That’s fine. There’s another person who was born a year later on 28 Feb 2001, and our algorithm is setting their “gimme gimme” day to the 29th of February in each subsequent leap year. That’s not what we want.

with testdata as (
select date'2000-12-25' as d1
      ,date'2000-02-29' as d2
      ,date'2000-02-28' as d3
      ,date'2001-02-28' as d4
from dual)
select rownum-1 as age
      ,add_months(d1,12*(rownum-1)) as d1
      ,add_months(d2,12*(rownum-1)) as d2
      ,add_months(d3,12*(rownum-1)) as d3
      ,add_months(d4,12*(rownum-1)) as d4
from testdata connect by level <= 12;
AGE D1          D2          D3          D4
0   25-DEC-2000	29-FEB-2000 28-FEB-2000 28-FEB-2001
1   25-DEC-2001	28-FEB-2001 28-FEB-2001 28-FEB-2002
2   25-DEC-2002	28-FEB-2002 28-FEB-2002 28-FEB-2003
3   25-DEC-2003	28-FEB-2003 28-FEB-2003 29-FEB-2004 **D4 is INCORRECT**
4   25-DEC-2004	29-FEB-2004 28-FEB-2004 28-FEB-2005
5   25-DEC-2005	28-FEB-2005 28-FEB-2005 28-FEB-2006
6   25-DEC-2006	28-FEB-2006 28-FEB-2006 28-FEB-2007
7   25-DEC-2007	28-FEB-2007 28-FEB-2007 29-FEB-2008 **D4 is INCORRECT**
8   25-DEC-2008	29-FEB-2008 28-FEB-2008 28-FEB-2009
9   25-DEC-2009	28-FEB-2009 28-FEB-2009 28-FEB-2010
10  25-DEC-2010	28-FEB-2010 28-FEB-2010 28-FEB-2011
11  25-DEC-2011	28-FEB-2011 28-FEB-2011 29-FEB-2012 **D4 is INCORRECT**

Edge cases. Always with the edge cases. How shall we fix this? We’ll have to pick out those especially special people who were born on the 28th of February in a non-leap year and add some special handling.

with
  function birthday (d in date, age in number) return date is
  begin
    if to_char(d,'DD/MM') = '28/02'
    and to_char(add_months(d,age*12),'DD/MM') = '29/02'
    then
      return add_months(d,age*12)-1;
    else
      return add_months(d,age*12);
    end if;
  end;
select * from (
  with testdata as (
  select date'2000-12-25' as d1
        ,date'2000-02-29' as d2
        ,date'2000-02-28' as d3
        ,date'2001-02-28' as d4
  from dual)
  select rownum-1 as age
        ,birthday(d1,rownum-1) as d1
        ,birthday(d2,rownum-1) as d2
        ,birthday(d3,rownum-1) as d3
        ,birthday(d4,rownum-1) as d4
  from testdata connect by level <= 12
);

LiveSQL

AGE D1          D2          D3          D4
0   25-DEC-2000 29-FEB-2000 28-FEB-2000 28-FEB-2001
1   25-DEC-2001 28-FEB-2001 28-FEB-2001 28-FEB-2002
2   25-DEC-2002 28-FEB-2002 28-FEB-2002 28-FEB-2003
3   25-DEC-2003 28-FEB-2003 28-FEB-2003 28-FEB-2004
4   25-DEC-2004 29-FEB-2004 28-FEB-2004 28-FEB-2005
5   25-DEC-2005 28-FEB-2005 28-FEB-2005 28-FEB-2006
6   25-DEC-2006 28-FEB-2006 28-FEB-2006 28-FEB-2007
7   25-DEC-2007 28-FEB-2007 28-FEB-2007 28-FEB-2008
8   25-DEC-2008 29-FEB-2008 28-FEB-2008 28-FEB-2009
9   25-DEC-2009 28-FEB-2009 28-FEB-2009 28-FEB-2010
10  25-DEC-2010 28-FEB-2010 28-FEB-2010 28-FEB-2011
11  25-DEC-2011 28-FEB-2011 28-FEB-2011 28-FEB-2012

Now that’s what I call a happy birthday, for everyone – no matter how special.

p.s. that’s a picture of Tony Robbins at the top of this post. He’s a leap day kid. I don’t know when he celebrates his birthdays, but as he says, “the past does not equal the future.”


Add a “Share Link” to your application

Sometimes my customers need to be able to get a direct link to a record in their APEX application which they can share with others (e.g. to publish it on a website, or send by email to a colleague).

They don’t want people to be able to easily “guess” the link (so it needs to have some degree of randomness and complexity), but it’s not so sensitive that if someone somehow gets the link by mistake it won’t be cause for alarm. They would like to be able to invalidate the link at any time, so if they send the link and then decide they don’t want people viewing the record anymore, they can mark it as “expired”.

Task 1. Generate the link

We have a table to which we want to create links for each record. It already has a surrogate key called id based on a simple sequence, so on its own this is not suitable for our link (we don’t want users just adding 1 to the id and iterating through all values to look up other records). Add a column to the table with a unique constraint:

alter table mytable add (
  link_code varchar2(50)
, constraint mytable_link_code_uk unique (link_code)
);

Update existing rows (if any) and make the column not null:

update mytable
set link_code = id || '-' || dbms_random.string('x',10)
where link_code is null;

alter mytable modify link_code not null;

Set its value using the following expression (e.g. this could be done in a page process, in your table API, or a table trigger):

create trigger bi_mytable before insert on mytable for each row
begin
  :new.link_code := :new.id || '-' || dbms_random.string('x',10);
end;

Note that the random string is more than likely to be unique for each record; but we prepend the unique ID as well which guarantees it to be unique. In addition, if a user sends us a link saying “this didn’t work”, we can visually parse it to see what the record ID was they were trying to access. This is needed because our design allows users to overwrite link_code (e.g. to expire the link).

Task 2. Build an APEX link page

Create a page (in my example, this will be page 100) to be the link resolver. It won’t have any complex regions or show any information, but will merely redirect the user to the page with the actual data (page 10, in my example).

Set it up as follows:

  • Alias: GO
  • Page Access Protection: Unrestricted
  • Region with title “Record not found”
  • Hidden item: P100_REF
    • Session State Protection: Unrestricted

Note: if the target page that shows the record is Public (i.e. the people getting the link cannot login to the application), this link resolver page will also need to be public – i.e. set Authentication to Page Is Public. On the other hand, if the target record is only available to authenticated users, the link resolver page should be restricted to authenticated users as well.

Add a PL/SQL Process that runs Before Header, with the following code:

declare
  l_id number;
begin

  select x.id into l_id
  from mytable x
  where x.link_code = :P100_REF;

  -- requires APEX 5.1 or later
  apex_util.redirect_url(
    apex_page.get_url
      (p_page        => 10
      ,p_clear_cache => 10
      ,p_items       => 'P10_ID'
      ,p_values      => l_id));

/*
  -- use this on APEX 5.0 or earlier
  apex_util.redirect_url(
    apex_util.prepare_url(
      p_url => 'f?p='
            || :APP_ID
            || ':10:'
            || :APP_SESSION
            || ':::10:P10_ID:'
            || l_id));
*/

exception
  when no_data_found then
    null;
end;

If the page gets a valid value for P100_REF, it will find the ID for the corresponding record and redirect the user to the page showing that record. If the link is invalid or expired, the page will not redirect but will show a “record not found” message.

Task 3. Show the link

We show the generated link on a page with an ordinary Text item.

Create an item on the page with the following properties:

  • Name: P10_LINK
  • Custom Attributes (or HTML Form Element Attributes): readonly
  • Session State Protection: Unrestricted

We need to generate the link using the page’s domain name, which (at least in some cases) we don’t know ahead of time. To do this, we need to get the page’s URL including host, port and path.

Create a dynamic action on page load. Set its condition so it only fires if P10_LINK_CODE is not null (if it doesn’t already exist, create P10_LINK_CODE as a hidden item based on the database column LINK_CODE).

The dynamic action needs two True Actions – firstly, Execute Javascript:

$s("P10_LINK", window.location.protocol + '//'
               + window.location.hostname
               + ':' + window.location.port
               + window.location.pathname);

Secondly, Execute PL/SQL:

:P10_LINK := :P10_LINK
          || '?p=&APP_ALIAS.:GO:::::P100_REF:'
          || :P10_LINK_CODE;

This dynamic action constructs the link using the current window’s URL including path (which includes the trailing “/apex/f” or “/ords/f“) and query string (“?p=...“).

When the user clicks the item, we want it to automatically select the entire link and copy it to their clipboard. To show this has happened, we show the word “Copied” on the screen.

  1. Create a Dynamic Action on the item – Event: Click
  2. Add a True Action on the Dynamic Action
    1. Action: Execute JavaScript Code
    2. Fire On Page Load: (unchecked)
    3. Code:
this.triggeringElement.select();
document.execCommand("copy");
$(this.triggeringElement).after("&nbsp;Copied.")

Users who are looking at the record get a “Share Link” in a convenient item that they can copy-and-paste into emails or web pages. If you need to expire a link, simple update the record with a new link_code and the old links will no longer work.


Quick tip: List all interactive reports with email features enabled

I was working on an application in an APEX instance that was not configured for email (and would not be), but a number of interactive reports were allowing users to use the “Subscription” or the “Download as Email” features. If they tried these features, those emails would just go into the APEX mail queue and never go anywhere, so I needed to turn these off.

I listed all the interactive reports that need fixing with this query:

select page_id
      ,region_name
      ,show_notify
      ,download_formats
from apex_application_page_ir
where application_id = <my app id>
and (show_notify = 'Yes' or instr(download_formats,'EMAIL') > 0);

Quick tip: List all tabular forms

I needed to get a list of all the tabular forms in my application along with which table they were based on. This query did the trick:

select page_id
      ,attribute_02 as data_source
      ,page_name
      ,region_name
      ,attribute_03 as key_column
from   apex_application_page_proc
where  application_id = <my app id>
and    process_type_code = 'MULTI_ROW_UPDATE'
order by 1, 2;


Quick tip: default only for new records

If you have an ordinary Oracle APEX form with the standard Automatic Row Fetch process, and the page has an optional item with a default value, APEX will set the default on new records automatically. However, if the user queries an existing record, Oracle APEX will also fill in a missing value with the default value. This might not be what is desired – if the user clears the value they would expect it will stay cleared.

If you only want the default to be applied for new records, change the attribute on the item so that the default is only applied to new records:

  1. Set Default Value Type to PL/SQL Expression
  2. Set Default value to something like:
    case when :P1_ID is null then 'xyzzy' end