Paste from clipboard in Oracle APEX

Since APEX makes it so nifty, I've got a few pages simply used as query tools - handy to verify data during development.

I also use these pages to experiment with UX, and recently I thought I could save myself pressing ctrl+v to paste my ID into a field for lookup.

I knew we had done something recently for adding content to the clipboard, so I figured there would be a way to paste clipboard content into a field.

Sure enough, Dr Google had the answers, though it's bleeding edge. Good enough to experiment with.

For me, adapting this response to APEX meant setting the provided field name.

I added this to my JS global declaration.

async function paste(input) {
  const text = await navigator.clipboard.readText();

  $s(input, text);
}

And this when I clicked the button to action the paste, I nominate the item I want the clipboard data to be copied into.
paste('P105_EVENT_ID');

An on-change dynamic action on this field would add a member to the collection, and refresh a number of regions that query the collection.

Don't forget to add Page Items to Submit

I have an example here.
https://apex.oracle.com/pls/apex/f?p=32532:22

I thought I noticed a potential odd behaviour with this, possibly due to the async action.
Therefore I've created an example with a few regions, and I'll try throttling the connection to see if that highlights the behaviour.

And so now I have this as a handy reference, I'll demonstrate some APEX collections and regular expression behaviour while I'm at it.

Regular Expressions 101 - REGEXP_COUNT

Not all regular expressions are scary.

As the documentation states, REGEXP_COUNT returns the number of times a pattern occurs in a string.

We can do this to simply count how many times the letter S appears in a string

select job, regexp_count(job,'S') 
from scott.emp;
JOB       REGEXP_COUNT(JOB,'S')  
--------- ---------------------- 
CLERK     0                      
SALESMAN  2                      
SALESMAN  2                      
MANAGER   0                      
SALESMAN  2                      
MANAGER   0                      
MANAGER   0                      
ANALYST   1                      
PRESIDENT 1                      
SALESMAN  2                      
CLERK     0                      
CLERK     0                      
ANALYST   1                      
CLERK     0                      

 14 rows selected 

Today I used this function to detect/count how many carriage returns in a string using CHR(13) instead of 'S', but no doubt you could use the power of regular expressions to do all sorts of things. One such example is validating email address format.

I've also used it in the past to identify dirty data - those with numeric digits when it should be all alphabetical characters (names).

Validating e-mail in PL/SQL

This seems like a frequent request across all languages. I google search can give you all sorts of options for validating a singular e-mail address.

For instance, here's one option using a regular expression:

FUNCTION val_email
  (p_email  IN  VARCHAR2)
  RETURN BOOLEAN IS
BEGIN
  RETURN REGEXP_SUBSTR (p_email, '[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}') IS NOT NULL OR p_email IS NULL;
END val_email;

Couple this with the common need/want to enter multiple addresses in the one field, I wondered how best to action that requirement - I came up with this:

FUNCTION val_email_string
  (p_email      IN  VARCHAR2
  ,p_separator  IN  VARCHAR2 DEFAULT ',')
  RETURN VARCHAR2 IS
  lt_emails  apex_application_global.vc_arr2;
  lb_valid   BOOLEAN DEFAULT TRUE;
  ln_error   PLS_INTEGER;
BEGIN
  -- split string into separate addresses
  lt_emails := apex_util.string_to_table
                (p_string    => REPLACE(p_email,' ')
                ,p_separator => p_separator);

  << email_val >>
  FOR i IN 1..lt_emails.COUNT LOOP
    lb_valid := val_email(lt_emails(i));
    -- If error occurs, record the problem number and don't bother continuing
    ln_error := i;
    EXIT WHEN NOT lb_valid;
  END LOOP email_val;
  IF NOT lb_valid THEN
    -- return the message with the first problem address
    RETURN 'At least one e-mail not valid: '||lt_emails(ln_error);
  ELSE
    -- no errors occurred
    RETURN NULL;
  END IF;
END val_email_string;

Any suggested improvements?

It was designed for Apex validation with type "Function Returning Error Text"