How to restrict an Oracle apex item to numeric/character values

Question

I have a TEXT item field - order number, where user can enter his details.

When a user enters order number starting with 1, it should allow only numeric values.
When a user enters order number starting with L, it should accept both numeric and alphabets.

How can I do it in Oracle Apex?

score 2 · Accepted Answer · answered Apr 21 '15 at 06:39

You could use a CHECK constraint on the column to allow:

values with only digits when starting with '1'
values as it is when starting with 'L'

Check constraint would be:

CHECK
(
  CASE
  WHEN SUBSTR(text,1,1) ='1' THEN
    REGEXP_substr(text, '^[[:digit:]]+$')
  WHEN SUBSTR(text,1,1) ='L' THEN
    text
  END IS NOT NULL)

Let's look at a test case:

Setup

SQL> CREATE TABLE t(text VARCHAR2(30));

Table created.

SQL>
SQL> ALTER TABLE t ADD CONSTRAINT t_chk CHECK
  2  (
  3    CASE
  4    WHEN SUBSTR(text,1,1) ='1' THEN
  5      REGEXP_substr(text, '^[[:digit:]]+$')
  6    WHEN SUBSTR(text,1,1) ='L' THEN
  7      text
  8    END IS NOT NULL);

Table altered.

SQL>

Test

SQL> INSERT INTO t VALUES('123');

1 row created.

SQL> INSERT INTO t VALUES('1a');
INSERT INTO t VALUES('1a')
*
ERROR at line 1:
ORA-02290: check constraint (LALIT.T_CHK) violated


SQL> INSERT INTO t VALUES('L12A');

1 row created.

SQL> INSERT INTO t VALUES('A12A');
INSERT INTO t VALUES('A12A')
*
ERROR at line 1:
ORA-02290: check constraint (LALIT.T_CHK) violated


SQL> SELECT * FROM t;

TEXT
------------------------------
123
L12A

SQL>

So, it only allowed values '123' and 'L12A'. And it didn't allow values '1a' and 'A12A'. Works perfectly!

I saw that question, however, I don't work with reports. So, I am afraid I can't help you with that. — Lalit Kumar B, Apr 21 '15 at 10:35

score 0 · Answer 2 · answered Jul 19 '19 at 21:45

For a solution that checks when the APEX page is submitted, and gives a nice error message on the field in error, you can create a "Validation" for the field, example here validates an email address (with an incorrect REGEX - but fairly correct). It uses a PL/SQL function returning a Boolean.

Function:

begin
  return regexp_count(:P811_EMAIL_ADDRESS, '^[-_A-Z0-9.]+@([A-Z]+\.)?[A-Z]+\.COM$') >= 1;
end;

APEX Application:

How to restrict an Oracle apex item to numeric/character values

2 Answers2