-12

1.we can use "identity(Start_Value,Increase)" key word in SQL Server to auto increment filed. so, such like that any key word is available in Oracle 10g. if any body know that give me ans...

and if we want to find only Positive difference between two Field....

Waiting... Fast

Dhaval Ghevariya
  • 28
  • 1
  • 6

2 Answers2

1

In 10g you can use the following objects: a Table, a Sequence and a before-insert row-level Trigger:

  • Table T1 with a numeric column (that simulates the auto-incremental behavior)
  • Sequence SEQ_T1_ID1 (to use for the number generation)
  • Trigger TRG_T1_ID1 (that populates your column automatically)

Obviously you need the CREATE TABLE, CREATE SEQUENCE and CREATE TRIGGER privileges, and some quotas (maybe unlimited) on the Tablespace.

CREATE TABLE T1
(ID1 NUMBER,
 TNAME VARCHAR2(128));

CREATE SEQUENCE SEQ_T1_ID1;

CREATE OR REPLACE TRIGGER TRG_T1_ID1
BEFORE INSERT ON T1
FOR EACH ROW
BEGIN
    SELECT SEQ_T1_ID1.NEXTVAL
    INTO :NEW.ID1
    FROM DUAL;
END;
/
Corrado Piola
  • 859
  • 1
  • 14
  • 18
0

Untill 12c Oracle does not have such option as identity autoincrement columns.

There are TONS "how to do" this in Internet and SO in particular:

How to create id with AUTO_INCREMENT on Oracle?

CREATE TABLE T (
  ID           NUMBER(10)
);

ALTER TABLE T ADD (
  CONSTRAINT T_pk PRIMARY KEY (ID));

CREATE SEQUENCE T_seq;

CREATE OR REPLACE TRIGGER T_bifer 
BEFORE INSERT ON T 
FOR EACH ROW

BEGIN
  SELECT T_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/
Community
  • 1
  • 1
Dmitry Nikiforov
  • 2,988
  • 13
  • 12
  • Thnx...That's i know. i want to know any Keywords if exits which used to increment without this syntax...For Ex: create table custommer (CustomerId int identity(101,1)); in this we can increment without syntax in SQL Server – Dhaval Ghevariya Feb 14 '14 at 13:49
  • There is no automatic incremental columns in Oracle 10G like in SQL Server and there is no syntax to instruct Oracle 10G to make like SQL Server. – Dmitry Nikiforov Feb 14 '14 at 13:57