CURRVAL AND NEXTVAL 

    A sequence is a schema object that can generate unique sequential
    values.  These values are often used for primary and unique keys.
    You can refer to sequence values in SQL statements with these
    pseudocolumns: 

CURRVAL
    returns the current value of a sequence. 

NEXTVAL
    increments the sequence and returns the next value. 

    You must qualify CURRVAL and NEXTVAL with the name of the sequence: 

    sequence.CURRVAL
    sequence.NEXTVAL 

    To refer to the current or next value of a sequence in the schema of
    another user, you must have been granted either SELECT object
    privilege on the sequence or SELECT ANY SEQUENCE system privilege
    and you must qualify the sequence with the schema containing it: 

    schema.sequence.CURRVAL
    schema.sequence.NEXTVAL 

    To refer to the value of a sequence on a remote database, you must
    qualify the sequence with a complete or partial name of a database
    link: 

    schema.sequence.CURRVAL@dblink
    schema.sequence.NEXTVAL@dblink 

    If you are using Trusted Oracle in DBMS MAC mode, you can only refer
    to a sequence if your DBMS label dominates the sequence's creation
    label or if one of these criteria is satisfied: 

    * If the sequence's creation label is higher than your DBMS label,
      you must have READUP and WRITEUP system privileges.
    * If the sequence's creation label is lower than your DBMS label,
      you must have WRITEDOWN system privilege.
    * If the sequence's creation label and your DBMS label are
      noncomparable, you must have READUP, WRITEUP, and WRITEDOWN system
      privileges. 

    If you are using Trusted Oracle in OS MAC mode, you cannot refer to
    a sequence with a lower creation label than your DBMS label. 

USING SEQUENCE VALUES:
    You can use CURRVAL and NEXTVAL in these places: 

    * the SELECT list of a SELECT statement
    * the VALUES clause of an INSERT statement
    * the SET clause of an UPDATE statement 

    You cannot use CURRVAL and NEXTVAL in these places: 

    * a subquery
    * a view's query or snapshot's query
    * a SELECT statement with the DISTINCT operator
    * a SELECT statement with a GROUP BY or ORDER BY clause
    * a SELECT statement that is combined with another SELECT statement
      with the UNION, INTERSECT, or MINUS set operator
    * the WHERE clause of a SELECT statement
    * DEFAULT value of a column in a CREATE TABLE or ALTER TABLE
      statement
    * the condition of a CHECK constraint 

    Also, within a single SQL statement, all referenced sequences, LONG
    columns, updated tables, and locked tables must be located on the
    same database. 

    When you create a sequence, you can define its initial value and the
    increment between its values.  The first reference to NEXTVAL
    returns the sequence's initial value.  Subsequent references to
    NEXTVAL increment the sequence value by the defined increment and
    return the new value.  Any reference to CURRVAL always returns the
    sequence's current value, which is the value returned by the last
    reference to NEXTVAL.  Note that before you use CURRVAL for a
    sequence in your session, you must first increment the sequence with
    NEXTVAL. 

    You can only increment a sequence once in a single SQL statement.
    If a statement contains more than one reference to NEXTVAL for a
    sequence, Oracle increments the sequence once and returns the same
    value for all occurrences of NEXTVAL.  If a statement contains
    references to both CURRVAL and NEXTVAL, Oracle increments the
    sequence and returns the same value for both CURRVAL and NEXTVAL
    regardless of their order within the statement. 

    A sequence can be accessed by many users concurrently with no
    waiting or locking. 

Example I
    This example selects the current value of the employee sequence: 

    SELECT empseq.currval
        FROM DUAL 

Example II
    This example increments the employee sequence and uses its value for
    a new employee inserted into the employee table: 

    INSERT INTO emp
        VALUES (empseq.nextval, 'LEWIS', 'CLERK',
                7902, SYSDATE, 1200, NULL, 20) 

Example III
    This example adds a new order with the next order number to the
    master order table and then adds suborders with this number to the
    detail order table: 

    INSERT INTO master_order(orderno, customer, orderdate)
        VALUES (orderseq.nextval, 'Al''s Auto Shop', SYSDATE)
    INSERT INTO detail_order (orderno, part, quantity)
        VALUES (orderseq.currval, 'SPARKPLUG', 4)
    INSERT INTO detail_order (orderno, part, quantity)
        VALUES (orderseq.currval, 'FUEL PUMP', 1)
    INSERT INTO detail_order (orderno, part, quantity)
        VALUES (orderseq.currval, 'TAILPIPE', 2) 

SEE:
    INSERT, PSEUDOCOLUMNS, SELECT, UPDATE
This article is from:

http://www4.utc.fr/~nf17/DOCS/complement/sqlplus-ref/CURRVAL-AND-NEXTVAL.html

References:

http://www.techonthenet.com/oracle/sequences.php