• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7371
  • Last Modified:

using ALTER TABLE in ORACLE 9i to RENAME NOT NULL CONSTRAINTS IN A LOOP

I am getting an error message in the following SQL at the two lines where I invoke ALTER.  What I need to do is rename all NOT NULL constraints in my database to a more meaningful name.  Can someone help me with this?  I need to get this done quick!

DROP TABLE TEMP_CONSTRAINT_RENAMER;
CREATE TABLE TEMP_CONSTRAINT_RENAMER (
aTABLE_NAME VARCHAR2(32),
aCOLUMN_NAME VARCHAR2(32),
aOLD_CONSTRAINT_NAME VARCHAR2(32),
aNEW_CONSTRAINT_NAME VARCHAR2(32)
);

INSERT INTO TEMP_CONSTRAINT_RENAMER (aTABLE_NAME,aCOLUMN_NAME,aOLD_CONSTRAINT_NAME,aNEW_CONSTRAINT_NAME)
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, 'NN_' || substr(TABLE_NAME,6,12) || '_' || substr(COLUMN_NAME,1,12) || '_' ||  ROWNUM AS NEW_CONSTRAINT_NAME
FROM USER_CONS_COLUMNS
WHERE CONSTRAINT_NAME LIKE 'SYS%';

CREATE OR REPLACE PROCEDURE TEMP_MAKE_NN_NAMES IS

CURSOR C1 IS SELECT ATABLE_NAME,ACOLUMN_NAME,AOLD_CONSTRAINT_NAME,ANEW_CONSTRAINT_NAME FROM TEMP_CONSTRAINT_RENAMER;

myTABLE_NAME VARCHAR(32);
myCOLUMN_NAME VARCHAR(32);
myOLD_CONSTRAINT_NAME VARCHAR(32);
myNEW_CONSTRAINT_NAME VARCHAR(32);

BEGIN
  FETCH C1 INTO myTABLE_NAME, myCOLUMN_NAME, myOLD_CONSTRAINT_NAME, myNEW_CONSTRAINT_NAME;
  WHILE C1%FOUND LOOP
    /* THE FOLLOWING TWO LINES OF CODE WILL NOT COMPILE!  HELP!
     ALTER TABLE myTABLE_NAME DROP CONSTRAINT myOLD_CONSTRAINT_NAME;
     ALTER TABLE myTABLE_NAME MODIFY myCOLUMN_NAME CONSTRAINT myNEW_CONSTRAINT_NAME NOT NULL;
     FETCH C1 INTO myTABLE_NAME, myCOLUMN_NAME, myOLD_CONSTRAINT_NAME, myNEW_CONSTRAINT_NAME;
  END LOOP;
  CLOSE C1;
END TEMP_MAKE_NN_NAMES;

/* EXEC TEMP_MAKE_NN_NAMES;
0
rdytmire
Asked:
rdytmire
1 Solution
 
mudumbeCommented:
There are 2 problems:

1.  You need OPEN c1 stmt before the first fetch.
2.  In order to your ALTER TABLE, the easiest way is to use EXECUTE IMMEDIATE.  PL/SQL does not support the way you have constructed ALTER TABLE

     execute immediate 'ALTER TABLE ' || myTABLE_NAME || ' DROP CONSTRAINT ' ||
     myOLD_CONSTRAINT_NAME;
     execute immediate 'ALTER TABLE '  ||myTABLE_NAME || ' MODIFY ' ||
     myCOLUMN_NAME || '  CONSTRAINT ' || myNEW_CONSTRAINT_NAME || ' NOT NULL';

NOTE the extra spaces around the quoted string (keywords) to avoid runtime sql syntax errors.
0
 
rdytmireAuthor Commented:
Thanks.. this solved the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now