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:
- cur_main is the cursor where I get all my text-based input fields on the page.
- l_query holds my dynamic sql string.
- 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.
- 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.