5 Replies Latest reply on Feb 24, 2015 9:10 PM by jmarton

    Migration in Oracle 12c - why identity still has trigger and sequence?

    matte2111

      Hello,

      I'm migrating a SQL Server to an Oracle 12c. Though I checked the option "Identity columns" in the Translator configuration option, I still find sequence and trigger in the master.sql file.

       

      This is the code for my table and there correctly is "GENERATED BY DEFAULT ON NULL AS IDENTITY":

       

      CREATE TABLE "EXTRADIO_EXTENSARADIO"."TBREPORTIDENTITY"

         (    "GUID" RAW(16) NOT NULL ENABLE,

          "ID" NUMBER(10,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE,

           CONSTRAINT "PK_TBREPORTIDENTITY" PRIMARY KEY ("GUID")

         );

       

      But in the master.sql file there is also:

      CREATE OR REPLACE EDITIONABLE TRIGGER "EXTRADIO_EXTENSARADIO"."TBREPORTIDENTITY_ID_TRG" AFTER INSERT ON tbReportIdentity

      FOR EACH ROW

      DECLARE

      v_newVal NUMBER(12) := 0;

      BEGIN

        v_newVal := :new.Id;

        -- save this to emulate @@identity

        utils.identity_value := v_newVal;

      END;

      /

      ALTER TRIGGER "EXTRADIO_EXTENSARADIO"."TBREPORTIDENTITY_ID_TRG" ENABLE;

       

      What's the use of these sequence and trigger? Why are they generated?

      I was expecting them if I didn't have checked the "Identity columns" option....

       

      Thank you!

      matte

        • 1. Re: Migration in Oracle 12c - why identity still has trigger and sequence?
          Vic010

          That's a new feature in 12c. That's how identity columns work in Oracle. You can add an identity column using SQL Developer. I think they're just trying to make it easier to create the equivalent of an 'Autonumber column' as in the other Database product you mention....

          Although I feel the trigger is unnecessary IO (on our system) so I don't advocate using it, I'd rather insert into a table using sequence.nextval.

           

          Regards.

           

          Vic

          • 2. Re: Migration in Oracle 12c - why identity still has trigger and sequence?
            matte2111

            Hi Vic, thanks for your reply.

            I know this is a new feature in 12c. What i find strange is the creation of the trigger, that bring the identity management to the same level of the previous version (11g and before): sequence + trigger.

            If I create a table with an identity column, the default value of this column is something like ""MY_OWNER"."ISEQ$$_115319".nextval" with the ISEQ$$_115319 sequence created and managed by default.

            But there is NO trigger associated to the table.

             

            That's why I don't understand the reason why the Migration process in SQL Developer create a trigger associated to the table THOUGH I check the Identity option in Oracle 12c.

            This is an expected behavior if i had no checked the "Identity columns" option.

             

            Thank you,

            matteo

            • 3. Re: Migration in Oracle 12c - why identity still has trigger and sequence?
              thatJeffSmith-Oracle

              I think we need a 12 jdbc driver to activate the 12c db features, what version of sqldev are you using in your migration project?

              • 4. Re: Migration in Oracle 12c - why identity still has trigger and sequence?
                matte2111

                I'm using sqldeveloper 4.0.3.

                How can i know if we have 12 jdbc driver? And, eventually, how can i handle to get them?

                 

                matte

                • 5. Re: Re: Migration in Oracle 12c - why identity still has trigger and sequence?
                  jmarton

                  I don't think it's a matter of driver. It's the database engine's task to allow for this new feature. Identity column is, from the SQL Developer's perspective, an extension to the create table's (and similar) column definition clause.

                   

                  The table definition as provided in your initial comment seems correct. The trigger generated is an after insert trigger. It does not generate the ID value. Generation is left to the 12c new feature. So the short answer is 12c identity generation is utilized in the migration task.

                   

                  That after insert trigger just saves the ID value generated to the utils.identity_value variable. I don't know what the utils package stands for in the migration task, but the whole solution seems for me as if it was something like a returning into clause in the insert statement. If you don't need, you can delete that trigger. It should not break ID-generation, but it might break some other code in the master.sql.