CODEWITHSUNDEEP
oracle-databasedynamic-sqlddlalter-table
Wekerle Tibor
Wekerle Tibor

Reputation: 467

Oracle sql, alter constraint without specifying it's name

I would like to rename a constraint, in oracle database, without specify it's name, but with selecting it's name. I would like to do something like this: 

ALTER TABLE O4Y_USER RENAME CONSTRAINT
    (SELECT constraint_name
            FROM user_constraints
            WHERE table_name    = 'O4Y_USER'
            AND constraint_type = 'P'
     ) TO 'O4Y_USER_PK';

It is not working, I have the following error

enter image description here

The select alone, is working well, it returns the correct value. How is the correct syntax to do, to work the alter statement?

Upvotes: 0

Views: 1174

Answers (2)

Connor McDonald
Connor McDonald

Reputation: 11616

And just the same method using SQL*Plus or similar command line tools.

SQL> create table t ( x int primary key );

Table created.

SQL>
SQL> col cname new_value x
SQL>
SQL> SELECT constraint_name cname
  2  FROM   user_constraints
  3  WHERE  table_name    = 'T'
  4  AND    constraint_type = 'P';

CNAME
----------------------------------------------------------------------
SYS_C0068724

SQL>
SQL> ALTER TABLE t RENAME CONSTRAINT &&x to my_new_name;
old   1: ALTER TABLE t RENAME CONSTRAINT &&x to my_new_name
new   1: ALTER TABLE t RENAME CONSTRAINT SYS_C0068724 to my_new_name

Table altered.

Upvotes: 2

MT0
MT0

Reputation: 168623

You can use dynamic SQL. Something like:

DECLARE
  p_constraint_name VARCHAR2(30);
  p_sql             VARCHAR2(4000);
BEGIN
  SELECT constraint_name
  INTO   p_constraint_name
  FROM   user_constraints
  WHERE  table_name    = 'O4Y_USER'
  AND    constraint_type = 'P';

  p_sql := 'ALTER TABLE O4Y_USER RENAME CONSTRAINT "'
           || p_constraint_name
           || '" TO ''O4Y_USER_PK''';

  DBMS_OUTPUT.PUT_LINE( p_sql );
  EXECUTE IMMEDIATE p_sql;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE( 'Constraint not found!' );
END;
/

Upvotes: 3

Related Questions