Oracle - Dynamically Drop constraints from a table

I was faced with a task of dropping a constraint from a column in a table dynamically. i.e. the name of the constraint would vary between different environments and the challenge would be to know the name of the constraint by querying the dictionary tables in Oracle and drop them on the fly. As an example, consider the following create table statement;

CREATE TABLE TEST1(
COL1 VARCHAR2(20) NOT NULL,
COL2 NUMBER
)

Here, we are enforcing a NOT NULL constraint on the column COL1 of the table TEST1. If the requirement is to find this constraint dynamically and drop it, then the SQL to find the constraint is straightforward.

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='TEST1' AND CONSTRAINT_NAME IN (
SELECT CONSTRAINT_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME='TEST1' AND COLUMN_NAME='COL1')

This gives me an output as SYS_C009648. From here on, it is as simple as

Drop constraint SYS_C009648;

Let us now complicate the problem a little. Say, I have two constraints on a table. As an example, consider the same create table statement, but slightly modified as

CREATE TABLE TEST1(
COL1 VARCHAR2(20) NOT NULL CHECK (LENGTH(COL1)<=5),
COL2 NUMBER
)

Now, I have two constraints on the table TEST1. A NOT NULL constraint and a CHECK constraint. The SQL

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='TEST1' AND CONSTRAINT_NAME IN (
SELECT CONSTRAINT_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME='TEST1' AND COLUMN_NAME='COL1')

would now return two rows for me. One row for the NOT NULL constraint and another row for the check constraint.

Unfortunately, the column CONSTRAINT_TYPE in USER_CONSTRAINTS has a value of 'C' for both the rows and there is no straightforward way to find the NOT NULL constraint.There is only one field in the view USER_CONSTRAINTS that can help us in this case. It is SEARCH_CONDITION. This column has values as given below

NOT NULL -> "COL1" IS NOT NULL
Check Constraint -> LENGTH(COL1)<=5

As this column is of LONG datatype, we will have to go for a "round about" way to find the exact constraint that we are looking for. The solution provided by Tom Kyte for this is to create a function and then use it for our requirement; The function is provided below

CREATE OR REPLACE FUNCTION GET_SEARCH_CONDITION(
P_CONS_NAME IN VARCHAR2 ) RETURN VARCHAR2
AUTHID CURRENT_USER
IS
L_SEARCH_CONDITION USER_CONSTRAINTS.SEARCH_CONDITION%TYPE;
BEGIN
SELECT SEARCH_CONDITION INTO L_SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME = P_CONS_NAME;

RETURN L_SEARCH_CONDITION;
END

The SQL needs to be modified as;

SELECT * FROM(
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='TEST1' AND CONSTRAINT_NAME IN (
SELECT CONSTRAINT_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME='TEST1' AND COLUMN_NAME='COL1')
) WHERE
GET_SEARCH_CONDITION(CONSTRAINT_NAME) LIKE '%NOT NULL%'

and this would do the trick for us;) We can now find the constraint with the help of the SQL above and drop it.