CODEWITHSUNDEEP
oracle-databaseoracle11g
kenpeter
kenpeter

Reputation: 8274

How to disable a constraint in Oracle with select statement as constraint name

I try to disable a constraint attached to a column in a table

ALTER TABLE
   ADV_TEST_COURSE_CREDIT
DISABLE constraint

name = (SELECT 
    constraint_name
from
    user_constraints natural join 
    user_cons_columns
where 
    table_name = 'ADV_TEST_COURSE_CREDIT' AND 
    column_name = 'SEQUENCE_NUMBER' AND
    constraint_type = 'C');

I try the statement above. It is not working. Any idea on how to make it work.

Table ADV_TEST_COURSE_CREDIT exists in dev, UAT, Production, so the constraint name is different, but it is all attached a column called SEQUENCE_NUMBER

Upvotes: 0

Views: 3527

Answers (1)

Renato Afonso
Renato Afonso

Reputation: 654

You need dynamic sql and some plsql:

declare name varchar2(200);
begin
SELECT 
    constraint_name
into name
from
    user_constraints natural join 
    user_cons_columns
where 
    table_name = 'ADV_TEST_COURSE_CREDIT' AND 
    column_name = 'SEQUENCE_NUMBER' AND
    constraint_type = 'C');
execute immediate 'ALTER TABLE  ADV_TEST_COURSE_CREDIT DISABLE CONSTRAINT :1' using name;
end;

Upvotes: 3

Related Questions