Oracle sql, alter constraint without specifying it's name

Question

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?

MT0 · Accepted Answer · 2018-04-19 08:37:37Z

3

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;
/

answered Apr 19, 2018 at 8:37

MT0

173k12 gold badges70 silver badges136 bronze badges

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

Comments

Connor McDonald · Accepted Answer · 2018-04-19 08:55:54Z

2

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.

answered Apr 19, 2018 at 8:55

Connor McDonald

11.8k1 gold badge14 silver badges21 bronze badges

Collectives™ on Stack Overflow

Oracle sql, alter constraint without specifying it's name

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related