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: http://stackoverflow.com/a/11296469/212950 — MAbraham1, Aug 09 '16 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, Aug 09 '16 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, Aug 12 '16 at 13:24
I am using the second one in my project since we are not using oracle 12c. i cannot use identity. — mohitkira, Aug 12 '16 at 18:08

score 1 · Accepted Answer · edited May 23 '17 at 12:02

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?

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. — mohitkira, Aug 09 '16 at 17:27

Auto increment in oracle database for java developer

1 Answers1