Re: DROP COLUMN Oracle 7.3

From: Tom Scott <spam>
Date: Thu, 18 Jun 1998 21:34:18 -0700
Message-ID: <6mcp7o$3up@newshub.atmnet.net>

Sorry, no can do in Oracle 7.3. I believe this is coming in Oracle 8.00.??

To do this you have to:

1. find all constraints dependent on the table you are about to change.
2. Disable them.
3. Rename the table.
4. Create the new table without the column.
5. Run an SQL statement that loads the new table from the renamed table, minus the column you don't want.
6. Enable the dependencies.

The following is part of a utility package I have that helps with the dependent constraints. If you don't have a consistent naming convention for foreign keys, it won't work. If you do, you can simply change the line,
"R_Pk_Name := UPPER(TableName_in) || '_PK';" to fit your needs. Sorry, I wrote this one more for my needs
than anything else.

/*

||  NOTE: This procedure will disable or enable DEPENDENT foreign key


||        constraints for the table specified in TableName_in. It


||        depends on a Primary Key constraint name, wherein the constraint


||        for the primary key consists of the table name + "_PK'.


*/
   PROCEDURE Set_FK_State(TableName_in  VARCHAR2, State_in VARCHAR2)
   IS
      CURSOR FK_Cur(RName VARCHAR2) IS
         SELECT        R_Constraint_Name, Constraint_Name, Table_Name
         FROM          User_Constraints
         WHERE         R_Constraint_Name = RName;
         FK_Rec        FK_Cur%ROWTYPE;
         Cur_Handle    INTEGER;
         Sql_Stmt      VARCHAR2(500);
         R_Pk_Name     VARCHAR2(50);
         V_Changed     INTEGER;
         Con_Count     INTEGER;
         StateStr      VARCHAR2(8);
   BEGIN
      R_Pk_Name := UPPER(TableName_in) || '_PK';
      IF UPPER(SUBSTR(State_in,1,1)) = 'D' THEN StateStr := 'DISABLE ';
      ELSE StateStr := 'ENABLE ';
      END IF;
      Con_Count := 0;
      OPEN FK_Cur(R_Pk_Name);
      Cur_Handle := DBMS_SQL.OPEN_CURSOR;
      LOOP
         FETCH FK_Cur INTO FK_Rec;
         IF FK_Cur%NOTFOUND THEN EXIT;
         END IF;
         Sql_Stmt := 'ALTER TABLE ' || FK_Rec.Table_Name ||
          ' ' || StateStr || ' CONSTRAINT ' || FK_Rec.Constraint_Name;
         DBMS_OUTPUT.PUT_LINE(StateStr || FK_Rec.Constraint_Name ||
            ' for Table: ' || FK_Rec.Table_Name);
         DBMS_SQL.PARSE(Cur_Handle, Sql_Stmt, DBMS_SQL.V7);
         V_Changed := DBMS_SQL.EXECUTE(Cur_Handle);
         Con_Count := Con_Count + 1;
      END LOOP;
      DBMS_SQL.CLOSE_CURSOR(Cur_Handle);
      CLOSE FK_Cur;
      DBMS_OUTPUT.ENABLE(100000);
      DBMS_OUTPUT.PUT_LINE(TO_CHAR(Con_Count) ||
         ' dependent constraints were altered.');

   END Set_FK_State; Received on Thu Jun 18 1998 - 23:34:18 CDT