SQL> CREATE TABLE t (x VARCHAR2(15)
  2                  CHECK (REPLACE(TRANSLATE(UPPER(x)
  3                                 ,         'ABCDEFGHIJKLMNOPQRSTUVWXYZ '
  4                                 ,         'AAAAAAAAAAAAAAAAAAAAAAAAAAA')
  5                         ,       'A') IS NULL));
 
Table created.
 
SQL> INSERT INTO t VALUES ('test');
 
1 row created.
 
SQL> INSERT INTO t VALUES ('TeStInG aGaIn');
 
1 row created.
 
SQL> INSERT INTO t VALUES (RPAD(' ',15));
 
1 row created.
 
SQL> INSERT INTO t VALUES ('+');
INSERT INTO t VALUES ('+')
*
ERROR at line 1:
ORA-02290: check constraint (XXFI.SYS_C00219192) violated
 
 
SQL> INSERT INTO t VALUES ('6 six');
INSERT INTO t VALUES ('6 six')
*
ERROR at line 1:
ORA-02290: check constraint (XXFI.SYS_C00219192) violated
 
 
SQL> INSERT INTO t VALUES ('Oracle FAQ');
 
1 row created.
 
SQL> COMMIT;
 
Commit complete.
 
SQL> SELECT x
  2  FROM   t
  3  /
 
X
---------------
test
TeStInG aGaIn
               
Oracle FAQ
 
SQL>

IF (REPLACE(TRANSLATE(UPPER(TRIM(:block3.txtname))
            ,         'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
            ,         'AAAAAAAAAAAAAAAAAAAAAAAAAA')
    ,       'A') IS NOT NULL) THEN
    RAISE Form_Trigger_Failure;
    Message('You cannot enter any numbers; only letters of the alphabet.');
END IF;