How to get last Quence on Oracle?

Question

I tried 3 different ways, to make one 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 obtain this value ?

Answer 1

The CURRVAL command is only for the session that sequentially ran, 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 search the table that it is used for the insert and check the MAX country.

Answer 2

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

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

This way your query will return the entered value.

If you want to access it later just do so:

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