4 Replies Latest reply on Nov 24, 2013 2:05 AM by Aasubram-Oracle

    SQL DEV DIFF TOOL : OMITS OBJECT IDENTIFIER CLAUSE

    Aasubram-Oracle

      Hi,

      The following is encountered in SQLDeveloper 3.2.20.09 where using Tools > Database Diff... results in DDL that omits the "OBJECT IDENTIFIER IS PRIMARY KEY" clause.

      Test SQL:
      create type dummy_typ as object
        (
        id_num    integer,
        other_num integer
        );
      /
      create table dummy_otbl of dummy_typ
        (
        constraint dummy_otbl_pk primary key ( id_num ) using index
        )
        object identifier is primary key;
      /


      When the diff tool generates the create table script it creates it but omits the OBJECT IDENTIFIER IS PRIMARY KEY clause. 

      Example:
        CREATE TABLE "AIMS_FEATURES"."DUMMY_OTBL" OF "AIMS_FEATURES"."DUMMY_TYP"
        ( CONSTRAINT "DUMMY_OTBL_PK" PRIMARY KEY ("ID_NUM") ENABLE
        );


      Is this a Bug of sorts? Thanks in advance for your responses


      Cheers,

      Anand

        • 1. Re: SQL DEV DIFF TOOL : OMITS OBJECT IDENTIFIER CLAUSE
          thatJeffSmith-Oracle

          Possibly.

           

          What's the DDL for the object you're comparing it to, or is it just 'missing?'

           

          I'm looking at the DDL generated for the object in the object editor and it's including the phrase so the DIFF should also pick that up.

           

            CREATE TABLE "HR"."DUMMY_OTBL" OF "HR"."DUMMY_TYP"

             ( CONSTRAINT "DUMMY_OTBL_PK" PRIMARY KEY ("ID_NUM")

            USING INDEX (CREATE UNIQUE INDEX "HR"."DUMMY_OTBL_PK" ON "HR"."DUMMY_OTBL" ("ID_NUM")

            PCTFREE 10 INITRANS 2 MAXTRANS 255

            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

            TABLESPACE "USERS" )  ENABLE

             ) OBJECT IDENTIFIER IS PRIMARY KEY

             PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

          NOCOMPRESS LOGGING

            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

            TABLESPACE "USERS" ;

          • 2. Re: SQL DEV DIFF TOOL : OMITS OBJECT IDENTIFIER CLAUSE
            Aasubram-Oracle

            Jeff,

             

            The diff tool should pick it up but it doesn't.  That seems to be to be a bug.  The DBMS_METADATA package that generated the above script is correct but it's not what SQL Developer appears to be displaying. Correct me if I am wrong.

            • 3. Re: SQL DEV DIFF TOOL : OMITS OBJECT IDENTIFIER CLAUSE
              thatJeffSmith-Oracle

              If you open the table editor, what SQL does SQL Developer generate for the 'missing' table?

               

              What I'm showing above is what SQL Developer generates for your example. Just haven't ran it through a DIFF scenario yet.

              • 4. Re: SQL DEV DIFF TOOL : OMITS OBJECT IDENTIFIER CLAUSE
                Aasubram-Oracle

                Hi Jeff,

                 

                CREATE TABLE "AIMS_FEATURES"."DUMMY_OTBL" OF "AIMS_FEATURES"."DUMMY_TYP"
                   ( CONSTRAINT "DUMMY_OTBL_PK" PRIMARY KEY ("ID_NUM")
                  USING INDEX (CREATE UNIQUE INDEX "AIMS_FEATURES"."DUMMY_OTBL_PK" ON "AIMS_FEATURES"."DUMMY_OTBL" ("ID_NUM")
                  PCTFREE 10 INITRANS 2 MAXTRANS 255
                  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
                  TABLESPACE "AIMS_DATA" )  ENABLE
                   ) OBJECT IDENTIFIER IS PRIMARY KEY
                   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
                  TABLESPACE "AIMS_DATA" ;


                This is what I see


                Thanks

                Anand