Forum Stats

  • 3,855,338 Users
  • 2,264,499 Discussions
  • 7,905,972 Comments

Discussions

Why I am getting Alter table statement and after copy and run the alter table statement inside my US

Erhan_toronto
Erhan_toronto Member Posts: 241
edited Sep 4, 2013 11:08AM in SQL & PL/SQL

Oracle 11gR2

-------------------------------------------

When I run below Query.... I am getting Alter table statement and after copy and run the alter table statement inside my USER2... I supposed to get nothing when second time run the below query. but some reason I am still getting Alter Table statement...What is the reason?

select REGEXP_REPLACE(dbms_metadata_diff.compare_alter('TABLE','STUDENT','STUDENT','USER2','USER1'),('USER2...'),'', 1, 0, 'i') from dual

statement

  ALTER TABLE "STUDENT" DROP CONSTRAINT "STU_ZIP_FK"

  ALTER TABLE "STUDENT" ADD CONSTRAINT "STU_ZIP_FK" FOREIGN KEY ("ZIP") REFERENCES "USER1"."ZIPCODE"("ZIP") ENABLE

User1 _student Table

CREATE TABLE "USER1"."STUDENT"

( "STUDENT_ID" NUMBER(8,0) CONSTRAINT "STU_STUDENT_ID_NNULL" NOT NULL ENABLE,

"SALUTATION" VARCHAR2(5),

"FIRST_NAME" VARCHAR2(25),

"LAST_NAME" VARCHAR2(25) CONSTRAINT "STU_LAST_NAME_NNULL" NOT NULL ENABLE,

"STREET_ADDRESS" VARCHAR2(50),

"ZIP" VARCHAR2(5) CONSTRAINT "STU_ZIP_NNULL" NOT NULL ENABLE,

"PHONE" VARCHAR2(15),

"EMPLOYER" VARCHAR2(50),

"REGISTRATION_DATE" DATE CONSTRAINT "STU_REGISTRATION_DATE_NNULL" NOT NULL ENABLE,

"CREATED_BY" VARCHAR2(30) CONSTRAINT "STU_CREATED_BY_NNULL" NOT NULL ENABLE,

"CREATED_DATE" DATE CONSTRAINT "STU_CREATED_DATE_NNULL" NOT NULL ENABLE,

"MODIFIED_BY" VARCHAR2(30) CONSTRAINT "STU_MODIFIED_BY_NNULL" NOT NULL ENABLE,

"MODIFIED_DATE" DATE CONSTRAINT "STU_MODIFIED_DATE_NNULL" NOT NULL ENABLE,

CONSTRAINT "STU_PK" PRIMARY KEY ("STUDENT_ID")

User2_student Table

CREATE TABLE "USER2"."STUDENT"

( "STUDENT_ID" NUMBER(8,0) CONSTRAINT "STU_STUDENT_ID_NNULL" NOT NULL ENABLE,

"SALUTATION" VARCHAR2(5),

"FIRST_NAME" VARCHAR2(25),

"LAST_NAME" VARCHAR2(25) CONSTRAINT "STU_LAST_NAME_NNULL" NOT NULL ENABLE,

"STREET_ADDRESS" VARCHAR2(50),

"ZIP" VARCHAR2(5) CONSTRAINT "STU_ZIP_NNULL" NOT NULL ENABLE,

"PHONE" VARCHAR2(15),

"EMPLOYER" VARCHAR2(50),

"REGISTRATION_DATE" DATE CONSTRAINT "STU_REGISTRATION_DATE_NNULL" NOT NULL ENABLE,

"CREATED_BY" VARCHAR2(30) CONSTRAINT "STU_CREATED_BY_NNULL" NOT NULL ENABLE,

"CREATED_DATE" DATE CONSTRAINT "STU_CREATED_DATE_NNULL" NOT NULL ENABLE,

"MODIFIED_BY" VARCHAR2(30) CONSTRAINT "STU_MODIFIED_BY_NNULL" NOT NULL ENABLE,

"MODIFIED_DATE" DATE CONSTRAINT "STU_MODIFIED_DATE_NNULL" NOT NULL ENABLE,

CONSTRAINT "STU_PK" PRIMARY KEY ("STUDENT_ID")

Answers

This discussion has been closed.