OracleBlog

You can't use variables in constraints.

CREATE OR REPLACE PACKAGE test_constants IS
test_val CONSTANT NUMBER := 20;
END test_constants;

CREATE TABLE atable (
a_id NUMBER,
CONSTRAINT atable_test CHECK (a_id > test_constants.test_val)
);

ORA-00904: "TEST_CONSTANTS"."TEST_VAL": invalid identifier

Check Constraints can NOT contain subqueries, so forget about doing it that way.

It is basically only for comparisons and simple operations.

How about this convoluted way of achieving the goal. Create a trigger that will check the value upon insert or update, and then trigger a special constraint.

SQL> CREATE TABLE atable (
2 a_id NUMBER,
3 a_error NUMBER,
4 CONSTRAINT id_too_high CHECK (a_error <> 1)
5 );

Table created.

SQL> CREATE OR REPLACE TRIGGER atable_trig
2 BEFORE INSERT OR UPDATE OF a_id ON atable FOR EACH ROW
3 BEGIN
4 IF :new.a_id > test_constants.test_val THEN :new.a_error := 1;
5 END IF;
6 END;
7 /

Trigger created.

SQL> INSERT INTO atable (a_id) VALUES (1);

1 row created.

SQL> INSERT INTO atable (a_id) VALUES (21);
INSERT INTO atable (a_id) VALUES (21)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.ID_TOO_HIGH) violated

Check the comments to see if someone has a better way.

Related links:
http://thinkoracle.blogspot.com/2005/05/enum-in-oracle.html