oracle - Drop auto generated constraint name -

i have auto-generated primary key constriant. key wrong. should against 2 columns , against one. need run script on multiple databases , constraint name may different each database. need constraint name , drop , create new primary key.

here script have far

declare con_name varchar(255);  begin  select constraint_name con_name all_constraints table_name = 'my_table' , constraint_type = 'p';  execute immediate 'alter table my_table drop constraint con_name';  execute immediate 'alter table my_table add constraint mt_pk primary key (rev, id)';  commit; / exit; 

however have problem in think trying drop con_name rather value within variable.

error on script: error number: -2443, error message: ora-02443: cannot drop constraint  - nonexistent constraint 

can help? need drop pk in table. there other way or approach right sort of way.

thanks

your con_name variable out of scope within ddl statement you're executing; you're trying drop constraint called con_name, not 1 named value holds - suspected. can't use bind variable here you'll need concatenate name:

declare   con_name all_constraints.constraint_name%type; begin   select constraint_name con_name   all_constraints   table_name = 'my_table' , constraint_type = 'p';    execute immediate 'alter table my_table drop constraint ' || con_name;    execute immediate 'alter table my_table add constraint mt_pk primary key (rev, id)'; end; / 

as nicholas krasnov pointed out in comment, don't need @ all; can drop primary key without specifying name, without using dynamic sql or pl/sql block:

alter table my_table drop primary key; alter table my_table add constraint mt_pk primary key (rev, id); 

hopefully don't have tables foreign key constraints against pk.