I’ve been struggling with a long form which has tens of text areas on my APEX env.

At some point I needed to apply some restrictions on input fields. Basically to refrain any unwanted non-unicode characters to be inserted into table.

APEX has great Built-in restrictions and they work like a charm. However, it has only a few options. It didn’t help my case when I had to set a restriction to reject all characters but a-Z letters, all the numbers, spaces AND punctuations.

Perhaps there are easier ways to do that. Actually I’m sure there are easier ways to do that. But here is what I ‘ve done and feel free to use & feedback.

DECLARE
l_query VARCHAR2(100);
l_res clob;
v_out clob;
v_special NUMBER;
CURSOR cur_main IS
SELECT item
FROM (
SELECT item_name item 
FROM APEX_APPLICATION_PAGE_ITEMS 
WHERE application_id = :app_id AND page_id = :app_page_id 
AND LOWER(display_as) LIKE '%text%'
);
BEGIN
v_out:= '';
   FOR rec_main IN cur_main LOOP
   l_query := 'select v('''||NVL(rec_main.item, 'null')||''') from dual';
   EXECUTE IMMEDIATE l_query INTO l_res;
   v_out := v_out||l_res;
   END LOOP;
SELECT COUNT(1) 
INTO v_special 
FROM dual 
WHERE regexp_like(REPLACE(v_out, CHR(13)||CHR(10), '_carriegeReturn_'), 
                 '[^a-zA-Z0-9 .,:;?%&+-_]');
   IF v_special >0 THEN
   RETURN FALSE;
   ELSE
   RETURN TRUE;
  END IF;
END;

let’s unwrap it a bit:

1. cur_main is the cursor where I get all my text-based input fields on the page.
2. l_query holds my dynamic sql string.
3. v_out is concatenated string which consists of each item’s value on the screen for current page. note that it won’t work based on the data from session state.
4. I simply hate being have to use regular expressions. So I copied a simple syntax and modified for my scenario.

Rest are just true/false conditions.

In my case it’s a Page Validation Process. To pass this validation no rows should be returned. Otherwise it will raise the error message.

Cheers!