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

Question

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

You're mixing up DDL and DML and it's not going to work that way. You need to use Dynamic SQL in a PL/SQL block — Christian Palmer
– Christian Palmer, Commented Nov 13, 2017 at 12:04

Renato Afonso · Accepted Answer · 2017-11-14 17:12:12Z

3

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;

edited Nov 14, 2017 at 17:12

answered Nov 13, 2017 at 12:04

Renato Afonso

6546 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Wernfried Domscheit Over a year ago

It would surprise me if you could use bind variables i.e. USING ... in such statement.

kenpeter Over a year ago

execute immediate 'ALTER TABLE ADV_TEST_COURSE_CREDIT DISABLE :1' using name; I have issue with the substitution. Error report - ORA-06550: line 12, column 26: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 3, column 1: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Rene Over a year ago

Change the last line to: execute immediate 'ALTER TABLE ADV_TEST_COURSE_CREDIT DISABLE CONSTRAINT '||name;

Renato Afonso Over a year ago

Yeah, sorry. But Rene already showed the fix. I'll correct it.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related