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

A quick browse of the Beta APEX 18.1 documentation

Noticed a few additions, one one omission.

    • APEX_EXPORT – new package with supported methods for exporting APEX applications and other scripts into a CLOB: get_application, get_workspace_files, get_feedback, get_workspace
    • APEX_JWT – new package for JSON Web Tokens – encode, decode, validate
    • APEX_SESSION – new procedures: create_session, delete_session, attach, detach
    • Missing documentation: Legacy JavaScript APIs – not sure if anyone will miss this, they could just refer to the 5.1 documentation for this
    • I should mention also the new format for the APEX Javascript API reference. Lots of TODO’s 🙂

What I’m particularly looking for is a good reference for the interactiveGrid API. Hopefully they’ll add this soon. There is a reference for a “grid” widget but that is not the API for interactive grids. I notice there are APIs for actions and model which are relevant to interactive grids.

Any other changes I’ve missed?


Interactive Grid: Custom Select List on each row

I had a column in an editable interactive grid based on a Select List, which takes valid values from a table that supports “soft delete” – i.e. records could be marked as “deleted” without affecting existing references to those deleted records.

The SQL Query for the LOV was like this (my example is a view on a reference table of school “year levels”):

select name, id from year_levels_vw
where deleted_ind is null
order by sort_order

The problem is that if a year level is marked as deleted, the select list will not include it due to the where clause; since Display Extra Values is set to “Yes”, the item on the page will instead show the internal ID which is not very useful to the user. Instead, I want to show the name but appended with a string to show it has been deleted:

select name
       || case when deleted_ind = 'Y' then ' (DELETED)' end
       as disp_name, id
from year_levels_vw
order by deleted_ind nulls first, sort_order

So now the select list shows the name, even if it has been deleted. However, once users start using this system and they delete some year levels, each select list will include all the deleted values, even if they will never be used again. We’d prefer to only include a deleted value IF it is currently used by the record being viewed; otherwise, we want to omit it from the list.

If this was an APEX item in a single-record edit form, I’d simply change the SQL Query for the LOV to:

select name
       || case when deleted_ind = 'Y' then ' (DELETED)' end
       as disp_name, id
from year_levels_vw
where deleted_ind is null or id = :P1_FROM_YEAR_LEVEL_ID
order by deleted_ind nulls first, sort_order

This way, the select list will only include the deleted year level if the underlying item was already set to that deleted ID. But we are now using an Interactive Grid – there is no page item to refer to.

The method I’ve used to solve this is to take advantage of the Cascading LOV feature in order to allow the query to refer to the value of the column. The SQL Query for the LOV on my Interactive Grid is:

select name
       || case when deleted_ind = 'Y' then ' (DELETED)' end
       as disp_name, id
from year_levels_vw
where deleted_ind is null or id = :FROM_YEAR_LEVEL_ID
order by deleted_ind nulls first, sort_order

Now, we need to make sure that “FROM_YEAR_LEVEL_ID” is available to the query, so we need to put it in the Items to Submit attribute. To make this attribute available, however, we must set Cascading LOV Parent Column(s) to something; I set it to the PK ID of the table, or some other column which doesn’t get changed by the user and isn’t actually referred to in the LOV Query.

Now, records not referring to a deleted value show only valid values:

And records that refer to a deleted value include the deleted value in the list, as desired:

It should be noted that the design of the select list means that the user is allowed to save changes to the record while preserving the reference to the deleted year level. This is desired, in this case; if it wasn’t, however, I’d consider putting a validation on the page to stop the record being saved unless the user changes it to a valid value.

P.S. Concerned parents should note that this example was just testing the ability to delete a record from a database, and I’d just like to make it totally clear that there are no plans by the department to eliminate year 7 from schools. Honest!

ADDENDUM (19/3/2018):

There are two known issues:

  1. If the item is the child of a Cascading LOV, when the parent item is changed, APEX automatically clears out any value in the child before rendering the list of values – which means the column value submitted will be NULL – which means the “deleted” items disappear from the list immediately. This means the user will not be allowed to save the record with a reference to a deleted value from the list.
  2. The column filter list of values is empty – this is due to a known bug in APEX [Doc ID 2289512.1 FILTER NOT WORKING IN INTERACTIVE GRID WITH CASCADING LOV][thanks to Dejan for alerting me to this]