How to get last sequence in Oracle?

Question

How to get last sequence in Oracle?

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I tried 3 different ways, to make a select of the last sequence of my table, but none worked.

SELECT SEQ_ID_PAIS.CURRVAL FROM DUAL;

Error:

 ORA-08002: a sequência SEQ_ID_PAIS.CURRVAL ainda não foi definida nesta sessão
08002. 00000 -  "sequence %s.CURRVAL is not yet defined in this session"

SELECT LAST_NUMBER FROM SEQ_ID_PAIS;

Error:

 ORA-02201: sequência não permitida aqui
    02201. 00000 -  "sequence not allowed here"

SELECT LAST_VALUE FROM SEQ_ID_PAIS;

Error:

ORA-02201: sequência não permitida aqui
02201. 00000 -  "sequence not allowed here"

What would be the correct way to get this value?

sql oracle

asked by anonymous 01.10.2017 / 15:38

2 answers

Error in asp.net mvc application HTTP connection with server does not return JSON data

score 1 · Answer 1

The CURRVAL command is only for the session that sequencer was executed, so you will no longer have this value.

You can try to see the structure of it,

SELECT *
  FROM user_sequences
 WHERE sequence_name = 'nome_da_sua_sequence';

Failing, you can look up the table it's used for insert and check the MAX of the field.

score 0 · Answer 2

If you want to return immediately after insertion you can do so:

insert into SIG_PROJETOAGRUPAMENTOSERVICO
  (idagrupamentoservico, descricao)
values
  (sq_Sig_Projagrupamentoserv.nextval, :nomeAgrupamento)
 returning idagrupamentoservico into :idagrupamento

In this way your accounts will return the value entered.

If you want to access it then just do so:

SELECT last_number
  FROM user_sequences
 WHERE sequence_name = '<sequence_name>';