Auto increment in oracle database for java developer

Question

I am migrating a database from MySql to an Oracle database, using SQL developer as the migration interface. I am new to Oracle, and am not sure whether this warrants a rewrite in Oracle using triggers or using auto-increment in Oracle, if that exists.

After generating the migration script For autoincrement, here is the resulting MySQL trigger code:

CREATE OR REPLACE TRIGGER Trigger_name BEFORE INSERT ON Table_name
FOR EACH ROW
DECLARE 
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.table_PK_id IS NULL THEN
   SELECT  sequence_name.NEXTVAL INTO v_newVal FROM DUAL;
    IF v_newVal = 1 THEN 
     SELECT NVL(max(table_PK_id),0) INTO v_newVal FROM Table_name;
     v_newVal := v_newVal + 1;
     LOOP
       EXIT WHEN v_incval>=v_newVal;
       SELECT sequence_name.nextval INTO v_incval FROM dual;
     END LOOP;
  END IF;
 :new.table_PK_id := v_newVal;
END IF;
 END;

...and here is the equivalent trigger generated in Oracle:

CREATE OR REPLACE TRIGGER Trigger_name 
  BEFORE INSERT ON Table_name
  FOR EACH ROW
BEGIN
  SELECT sequence_name.nextval
  INTO :new.table_PK_id
  FROM dual;
END;

Should I use the Oracle DDL trigger code in my application, or does Oracle offer something better?

Similar to this question, and the selected answer can resolve your question: stackoverflow.com/a/11296469/212950 — MAbraham1
– MAbraham1, Commented Aug 9, 2016 at 17:17
yes I got the solution from the link you provided me @MAbraham1 my question is if I use the second trigger I don't have to mention the id when I am writing the insert statement, right? — mohitkira
– mohitkira, Commented Aug 9, 2016 at 17:29
If that worked, please don't forget to mark the answer I provided. Just click on the checkbox beside it. Thank you. — MAbraham1
– MAbraham1, Commented Aug 12, 2016 at 13:24
I am using the second one in my project since we are not using oracle 12c. i cannot use identity. — mohitkira
– mohitkira, Commented Aug 12, 2016 at 18:08

Community · Accepted Answer · 2017-05-23 12:02:42Z

1

It's very important to determine which version of Oracle you are migrating to because you can potentially leverage some great features that Oracle offers. Conversely, you could get stuck converting MySQL functions to Oracle procedures, which can be challenging depending on early versions of Oracle.

For example, in releases of Oracle prior to 12.1, auto-increment had not been provided, although it could be implemented using a combination of sequences and triggers. Therefore if the version of Oracle is 12.1 or later, I recommend using an identity column with the sequence pseudo columns as default values, like this:

CREATE TABLE t1 (c1 NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, 
                   c2 VARCHAR2(10));

You can learn more about Identity Columns in Oracle Database 12c (12.1) and here.

Otherwise if Oracle is an earlier version, then yes your trigger code is sound but you will have to include the correct table and column, similar to below:

Table definition:

    CREATE TABLE departments (
      ID           NUMBER(10)    NOT NULL,
      DESCRIPTION  VARCHAR2(50)  NOT NULL);

    ALTER TABLE departments ADD (
      CONSTRAINT dept_pk PRIMARY KEY (ID));

    CREATE SEQUENCE dept_seq;

Trigger definition:

   CREATE OR REPLACE TRIGGER dept_bir 
    BEFORE INSERT ON departments 
    FOR EACH ROW

    BEGIN
      SELECT dept_seq.NEXTVAL
      INTO   :new.id
      FROM   dual;
    END;
    /

Note that this SQL DDL can also be found here:
How to create id with AUTO_INCREMENT on Oracle?

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Aug 9, 2016 at 17:23

MAbraham1

1,7784 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mohitkira Over a year ago

As of now I am not sure which version of database we are using. It might be the old one. That why I am using trigger for generating the id of the table.

Collectives™ on Stack Overflow

Auto increment in oracle database for java developer

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related