November PL/SQL Tip of the Month - Working with Sequence Objects

From: <info_at_revealnet.com>
Date: Thu, 05 Nov 1998 17:49:53 GMT
Message-ID: <71sog2$pjc$1_at_nnrp1.dejanews.com>

Join the PL/SQL Pipeline - a free internet community for PL/SQL development. It includes active discussion groups, chat rooms, library of technical papers and free software utilities. The PL/SQL Pipeline is hosted by author Steven Feuerstein and sponsored by RevealNet http://www.revealnet.com/pipleline.htm

---

November PL/SQL Tip of the Month Tips for Working with Sequence Objects

A sequence is a database object that offers high-speed access to an integer value guaranteed to be unique (within that sequence). A sequence is used most commonly to generate primary key values for a table. Suppose I created a sequence for my employee table as follows:

CREATE SEQUENCE employee_id_seq;

Then to obtain the "next value" from a sequence, you write code like this:

SELECT employee_id_seq.NEXTVAL INTO new_key FROM dual;

It is an unfortunate fact of life that you must reference NEXTVAL within an SQL statement.

You can also determine the "current value" of the sequence with the CURRVAL keyword, as follows:

SELECT employee_id_seq.CURRVAL INTO new_key FROM dual;

Here are some tips to keep in mind when using sequences:

1.Hide the SELECT statement. Right now you have to reference NEXTVAL and CURRVAL inside a query, but hopefully some day that restriction will be removed. Don't place those awkward looking queries against the dummy table, dual, in your application code. Instead build a function to return the next sequence value as in:

new_key := employee_pkg.next_employee_id;

The function does the query for you. That way, when the SELECT FROM dual is no longer needed, you can simply change the implementation of the function. You could also write a single generic function to retrieve the next value, as is done by the PL/Vision PLVdyn.nextseq function;

new_key := PLVdyn.nextseq ('employee_id_seq');

2.The CURRVAL keyword only returns the current value of the sequence in your current connection. It doesn't really know about the last sequence value used by another session - until you call .NEXTVAL yourself in your session. If you try to use CURRVAL before you use NEXTVAL, you get an error as shown below:

SQL> connect scott/tiger
Connected.
SQL> select employee_id_seq.currval from emp; select employee_id_seq.currval from emp
*
ERROR at line 1:
ORA-08002: sequence EMPLOYEE_ID_SEQ.CURRVAL

           is not yet defined in this session

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Nov 05 1998 - 18:49:53 CET