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.

Submit from jQuery modal causing session state protection violation

Don’t you hate those nagging issues where you attempt a few fixes in vain, waste hours of your life, and then suddenly the issue just resolves itself? What’s worse than an issue that won’t go away is one that just resolves itself and you don’t know why. You don’t know if it is just hiding, waiting to reappear at some inconvenient time later (e.g. when the app goes live in Production).

I added the first modal popup on a page in my application running on APEX 4.2.4 and immediately hit the problem described here: Jquery modal causing page protection violation error.

I’d used the simple popup modal using the builtin jQuery dialog widget as described here: Oracle APEX 4.2 – Creating a modal window [link no longer valid – http://rowantraining.blogspot.com.au/2013/08/oracle-apex-42-creating-modal-window.html] – Helen’s example works but doesn’t include submitting the page. Side note: I found it only worked if I put it in one of the page body regions, not in the After Header region.

The purpose of my popup is to accept additional input from the user, then submit the page. If I removed the page submit, the popup worked fine. On submit, it raises this error for the first item on the page:

“Session state protection violation: This may be caused by manual alteration of protected page item P1_ID. …”

If I turn off session state protection on the item, the same error is then raised on the next item on the page. Plus, I require session state protection to be enabled so disabling it is not an acceptable solution.

I didn’t want to import another plugin to the application because I wanted to keep it dead simple. The default jQuery dialog should just work, dammit! (I don’t mind importing plugins if the plain vanilla features provided by APEX are just not sufficient for the need; but the basic jQuery dialog is perfectly fine.)

In the end on a hunch I tried doing the submit after a timeout, i.e. instead of:

function popupSubmit() {
  $("#myPopup").dialog('close');
  doSubmit('SAVE');
}

I changed it to:

function popupSubmit() {
  $("#myPopup").dialog('close');
  setTimeout( function() { doSubmit('SAVE'); }, 1);
}

This workaround seemed to have done the trick, but I wasn’t happy – it just felt “hacky”.

I tried adding a similar popup to another page that was far simpler and it didn’t experience the problem. So submitting from a popup dialog should work.

I reverted the code to remove the timout and tried disabling all the Dynamic Actions on the original page, and the problem disappeared. So I figured the problem was caused by some interaction with a Dynamic Action. I gradually re-enabled the DAs one by one, retesting the page between each one. Finally I re-enabled the last Dynamic Action – and the problem still didn’t reoccur. So the problem has resolved itself, but apparently not because of any particular thing I’ve fixed, and I can no longer reproduce the problem. Aarrgh.

UPDATE 4/9/2015:

After a number of unrelated changes to the page, I started getting this error again, consistently. As the comments (see below) suggested, this is due to the jQuery dialog.close() method moving its contents outside of the <form> tag, which meant that the items submitted to APEX differed from those in the original page, causing APEX to raise the error.

I could find no way of avoiding the error, so instead I’ve fixed it by a little hack: instead of opening the dialog based on the original region that was generated by APEX, I use jQuery to create a copy of the region, and open and close that copy. That way, the original region stays unmodified (forever hidden) and the page submit works. It’s a bit fiddly but it seems to work so far.

(in this example, P1_REAL_NOTE is the database item; P1_POPUP_NOTE is a non-database textarea that’s rendered in the popup region)

function showPopup() {
  //copy the current note text into the popup window
  $s("P1_POPUP_NOTE",$v("P1_REAL_NOTE"));

  //make a temporary copy of the region
  //(because dialog.close moves it leading to
  //APEX.SESSION_STATE.ITEM_VALUE_PROTECTION)
  var x = $("#myPopup").clone();
  x.attr("id","myPopupCopy");
  x.insertAfter($("#myPopup"));
  //change the ID of the copied popup note item
  $("#myPopupCopy textarea#P1_POPUP_NOTE")
    .attr("id","P1_POPUP_NOTE_COPY");

  //popup the dialog
  $("#myPopupCopy").dialog(
    {modal:true
    ,autoOpen:true
    ,title:'Please enter your Notes'
    ,width:'500px'
    }
  );
}

function popupSubmit() {
  //copy the note text back into the real form item
  $s("P1_REAL_NOTE",$v('P1_POPUP_NOTE_COPY'));

  //close the popup (this moves the contents out of the
  //form tag which is what was leading to the apex error)
  $("#myPopupCopy").dialog('close');

  apex.submit({request:'SAVE_REVIEW',showWait:true});
}