Joseph DeVore's Blog: Oracle - PL/SQL

In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

A sequence is referenced in SQL statements with the NEXTVAL and CURRVAL pseudocolumns; each new sequence number is generated by a reference to the sequence’s pseudocolumn NEXTVAL, while the current sequence number can be repeatedly referenced using the pseudo-column CURRVAL.

With respect to a sequence, the CACHE option specifies how many sequence values will be stored in memory for faster access.

The downside of creating a sequence with a cache is that if a system failure occurs, all cached sequence values that have not been used, will be "lost". This results in a "gap" in the assigned sequence values. When the system comes back up, Oracle will cache new numbers from where it left off in the sequence, ignoring the so called "lost" sequence values.

(Note: To recover the lost sequence values, you can always execute an ALTER SEQUENCE command to reset the counter to the correct value.)

I will set the cache level to something low like 20 for my example and outage_apps_seq.

To populate the auto number you would use something like the following:

Oracle is like the battleship of a database. PL/SQL is cool so far. Maybe I think Oracle is more complex just because I'm a MySQL and MS SQL/T-SQL/DTS/SSIS type of dude. It's just been my experience over the past 13 years and all of companies I've worked for and with have had DBAs that did all of the work for me in their databases. I've only worked with simple queries on views in Oracle in the past, but the time has come to get into scripting complex queries and I'm ready - it's far overdue. I have no problem with any of the other RDBMS' but Oracle syntax is kinda different.

So here we go~ ORACLE TIME!