Oracle for SQL Server Developers, Vol. 4: Identity Columns & Sequences

Picking up where I left off in my previous “Oracle for SQL Server Developers” posts (one, two, three)…

In SQL Server, we commonly have a primary key column that is a integer and this number gets incremented, created, and inserted automatically by the database. This is a SQL Server identity column and it makes our lives easy. 

If you think about how this works in SQL Server, the logic for managing the identity value (the current value of the number) is managed as part of the database table.  In Oracle, this is split in to two pieces: the table and something called a sequence.  A sequence is a queryable object in the database that hands out sequential integers. 

Since a sequence is separate from the table (unlike identity and tables in SQL Server), you have to create it. 

Create an Oracle Sequence:
CREATE SEQUENCE sequence_name

Get the next value from an Oracle Sequence:
sequence_name.NEXTVAL

Get the current value from an Oracle Sequence:
sequence_name.CURRVAL

Question: What is the Oracle equivalent of SQL Server’s @@IDENTITY or SCOPE_IDENTITY()? 

Answer: sequence_name.CURRVAL although CURRVAL is more like SCOPE_IDENTITY() than @@IDENTITY.  (HINT to SQL Server developers: you probably shouldn’t be using @@IDENTITY and should move to SCOPE_IDENTITY() as fast as you can.)

Question: How do I INSERT a record in to a database that requires a SEQUENCE value?

Answer: Well, let’s assume that we have a table named Restaurant and we’ve already created a sequence called SEQ_RESTAURANT_ID.  Here’s an insert statement into the Restaurant table.

INSERT INTO Restaurant
(
  restaurant_id,
  name,
  address,
  city,
  state
)
VALUES
(
  SEQ_RESTAURANT_ID.NEXTVAL,
  ‘Via Matta’,
  ’79 Park Plaza’,
  ‘Boston’,
  ‘MA’
);

The key piece there is the first item in the VALUES clause — SEQ_RESTAURANT_ID.NEXTVAL.  That’s where we call the sequence to get the new value and then push that value into the RESTAURANT_ID column for the INSERT statement.

Question: How do I get the next value from an Oracle SEQUENCE and put it in a variable?

Answer: This is where that weird pseudo-table named DUAL comes in.  Whenever you need to select a value from something that isn’t a table, it’s a safe bet that you’ll need to say “FROM DUAL” somewhere in the query.

So, if you’ve declared a variable named “temp_restaurant_id” and you want to grab a sequence value and store it in that variable, here’s the query.

select SEQ_RESTAURANT_ID.NEXTVAL into temp_restaurant_id from dual;

-Ben