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;
LVL 1
rdytmireAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mudumbeConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.