How to get next value of Sequence dynamically in Oracle

oraclesequence

I used to a SQL guy and trying my hands on Oracle front.

I am getting dynamic values of sequence in a variable. I like to fetch next value for that sequence object in some output variable. How to handle this in Oracle.

I wrote something same for SQL like this:

SET @v_SQLString = 'SELECT @v_new_seq = next value for '+@v_table_name
SET @v_ParmDefinition = '@v_new_seq NUMERIC(19) OUTPUT'

EXECUTE sp_executesql
    @v_SQLString,
    @v_ParmDefinition,
    @v_new_seq = @v_new_seq OUTPUT

Looking for something same in Oracle.

Best Answer

My_Sequence_Name.NextVal - check here and here. Psoug is a great site for Oracle.

Related Solutions

How to verify that next sequence value can be used as a new primary key

The values of the PK comes from different sources: horrible. Using the sequence would be the preferred way. You can not tell what exactly is the next value for the sequence by lookin in the dictionary. There is caching in effect, obviously for performance reasons. If you look in the dictionary you will find the value that is valid after the cache ran out of numbers. What you don't know, is how far the cache is used.

    drop sequence z;    
    create sequence z;  -- default cache size 20
    select last_number from user_sequences where sequence_name = 'Z'; --> shows 1
    select z.nextval from dual; --> shows 1
    select last_number from user_sequences where sequence_name = 'Z'; --> shows 21

Can you ignore the pre entered value for the PK, so effectively only using the sequence? The way it works now is almost as bad as querying the max(pk)+1 from the table.

Mysql – simulate sequence on thesql in preparation for move to oracle

The 'gotcha' is that in nearly every case you really don't want to take the performance and maintenance hit for triggers just to hide the pk insert.

You really should write the queries to insert sequence.nextval with the INSERT. Do it during the migration before you regret it a few years from now.

Best Answer

Related Solutions

How to verify that next sequence value can be used as a new primary key

Mysql – simulate sequence on thesql in preparation for move to oracle

Related Question