PL/SQL Adding Columns to a Table without Loosing any Data

-- Renaming the last 4 column names
alter table TEST_TABLE_NAME rename column KAYIT_KULLANICI_ID to XKAYIT_KULLANICI_ID;
alter table TEST_TABLE_NAME rename column KAYIT_TARIHI to XKAYIT_TARIHI;
alter table TEST_TABLE_NAME rename column GUNCELLEME_KULLANICI_ID to XGUNCELLEME_KULLANICI_ID;
alter table TEST_TABLE_NAME rename column GUNCELLEME_TARIHI to XGUNCELLEME_TARIHI;
alter table TEST_TABLE_NAME drop CONSTRAINT "FK_TEST_TABLE_GNC_KULL_ID";
alter table TEST_TABLE_NAME drop CONSTRAINT "FK_TEST_TABLE_KYT_KULL_ID";

-- Adding new columns
alter table TEST_TABLE_NAME add "SICIL_NO"    VARCHAR2(20 BYTE);
alter table TEST_TABLE_NAME add "ACIKLAMA" VARCHAR2(200 BYTE);

-- Adding new "last 4 fields" which were renamed at the beginning
alter table TEST_TABLE_NAME add "KAYIT_KULLANICI_ID" NUMBER(18,0);
alter table TEST_TABLE_NAME add "KAYIT_TARIHI" DATE;
alter table TEST_TABLE_NAME add "GUNCELLEME_KULLANICI_ID" NUMBER(18,0);
alter table TEST_TABLE_NAME add "GUNCELLEME_TARIHI" DATE;

-- Updating new "last 4 fields" from ex- "last 4 fields"
update TEST_TABLE_NAME set
KAYIT_KULLANICI_ID = XKAYIT_KULLANICI_ID,
KAYIT_TARIHI = XKAYIT_TARIHI,
GUNCELLEME_KULLANICI_ID = XGUNCELLEME_KULLANICI_ID,
GUNCELLEME_TARIHI = XGUNCELLEME_TARIHI;

-- New "last 4 fields" are being made "not nullable"
alter table TEST_TABLE_NAME modify "KAYIT_KULLANICI_ID" NUMBER(18,0) NOT NULL ENABLE;
alter table TEST_TABLE_NAME modify "KAYIT_TARIHI" DATE DEFAULT sysdate NOT NULL ENABLE;
alter table TEST_TABLE_NAME add CONSTRAINT "FK_TEST_TABLE_GNC_KULL_ID" FOREIGN KEY ("GUNCELLEME_KULLANICI_ID")
REFERENCES "SCHEMA_NAME"."TEST_TABLE_NAME_2" ("ID") ENABLE;
alter table TEST_TABLE_NAME add CONSTRAINT "FK_TEST_TABLE_KYT_KULL_ID" FOREIGN KEY ("KAYIT_KULLANICI_ID")
REFERENCES "SCHEMA_NAME"."TEST_TABLE_NAME_2" ("ID") ENABLE;

-- Dropping ex-"last 4 fields"
alter table TEST_TABLE_NAME drop column "XKAYIT_KULLANICI_ID";
alter table TEST_TABLE_NAME drop column "XKAYIT_TARIHI";
alter table TEST_TABLE_NAME drop column "XGUNCELLEME_KULLANICI_ID";
alter table TEST_TABLE_NAME drop column "XGUNCELLEME_TARIHI";

Comments

Popular posts from this blog

Solution for "Can not obtain Oracle client information from registry"

Solution for the fault: "System.Diagnostics.Process.Start is not working on IIS, but working on ASP.NET Development Server"