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;