Oracle/PLSQL: Sequences (Autonumber)

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.

The syntax for a sequence is:

CREATE SEQUENCE sequence_name
MINVALUE  value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;

For example:
CREATE SEQUENCE supplier_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;

Now that you’ve created a sequence object to simulate an autonumber field, we’ll cover how to retrieve a value from this sequence object.

To retrieve the next value in the sequence order, you need to use nextval.
For example:supplier_seq.nextval

Written by Ravi Nallakukkala on August 1st, 2007 with no comments.
Read more articles on Database.

Limit & offset functionality in mySql, PostgreSQL

One of the excellent features provided by both my mySql & Postgre is support for Limit and offset functionality.

Usage syntax

SELECT <select_list>
    FROM <table_expression>
    [LIMIT { number | ALL }] [OFFSET number]

This is a useful feature when implementing pagination!
unfortunately this functionality is not currently being supported by Oracle!

Written by Ravi Nallakukkala on July 5th, 2007 with no comments.
Read more articles on Database.