Doubt Sequence usage - Oracle

Question

Doubt Sequence usage - Oracle

Navigation

#1 by (2 votes)

1

I have a table in oracle 11g which in turn has a sequence

CREATE TABLE "BANCO"."TEXTO" 
    ("TEXTOID" NUMBER NOT NULL ENABLE, 
    "NOME" VARCHAR2(4000 BYTE) NOT NULL ENABLE, 
    "VALOR" VARCHAR2(4000 BYTE) NOT NULL ENABLE, 
    "DATACADASTRO" DATE NOT NULL ENABLE, 
    "DATAATUALIZACAO" DATE NOT NULL ENABLE, 
     CONSTRAINT "TEXTO_PK" PRIMARY KEY ("TEXTOID"))

CREATE SEQUENCE  "BANCO"."TEXTO_SEQUENCE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 41 CACHE 20 NOORDER  NOCYCLE ;

And I have an insert script from our vendor:

INSERT INTO Texto
  (nome,valor,datacadastro,dataatualizacao,textoid)
VALUES
  ('texto1','valor1',sysdate,sysdate,(SELECT MAX(textoid)+1 FROM texto));

If I add the items this way in a script, will I be breaking the sequence? Taking into account that the table does not have a trigger to enter the value of the sequence in the record id!

oracle

asked by anonymous 09.03.2017 / 13:23

1 answer

Array storage OCI8 in PHP 7.0 with Xampp 32bit

score 2 · Accepted Answer

So you will not be using SEQUENCE . You should add the command INSERT as follows:

INSERT INTO Texto
  (nome,valor,datacadastro,dataatualizacao,textoid)
VALUES
  ('texto1','valor1',sysdate,sysdate,texto_sequence.nextval);

The great advantage over max mentioned is that SEQUENCE is atomic , guaranteeing new unique values, even in competition. In the case of max, you would have problems with concurrency in a possible rollback or lengthy commit, where another user could get the same id .