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

Jeffrey Kemp 24 August 2018

APEX / apex-4.2 / javascript / jQuery /

About Jeffrey Kemp

Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.