RE: sequences

From: James Bullard <jbullard_at_onvia.com>
Date: Wed, 12 Jul 2000 10:29:02 -0700
Message-Id: <10556.111836@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01BFEC26.AC0DEA6E
Content-Type: text/plain;

charset="iso-8859-1"

A sequence is a database object in Oracle that can be used as part of an insert statement. It is basically a number generator that you create and define how to increment it. For example, to insert new unique or pk values an insert statement might look like this: insert into table customers values (cust_id_seq.nextval, value2, value3 ...),
where cust_id_seq is the sequence and the nextval function is how to pull the next available number within the sequence.

My apologies in advance if I have missed the gist of your question.

James

-----Original Message-----
From: Glenn Travis [mailto:Glenn.Travis_at_wcom.com] Sent: Wednesday, July 12, 2000 10:23 AM
To: Multiple recipients of list ORACLE-L Subject: sequences

I wanted to ask how people are handling the insertion of unique keys in Oracle. Please understand that my experience with generating unique keys comes from an Informix background, wherein you can have a column with datatype of 'serial'. This is an integer column which gets generated at insert time with the next value automatically. You can then check the sqlca area (return buffer) for the inserted value.

I know in Oracle you can accomplish this with the SEQUENCE function. As this is not automatically inserted by Oracle, how is the unqiue key value inserted? Do most people use a before trigger, stored procs, or call the sequence themselves? If using a client program (c, java), how is the value for the unique key returned to the program which performed the insert?

Examples, methods, design recommendations welcome...

------_=_NextPart_001_01BFEC26.AC0DEA6E
Content-Type: text/html;

charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">

<META content="MSHTML 5.00.2919.6307" name=GENERATOR></HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=166192317-12072000>A
sequence is a database object in Oracle that can be used as part of an insert statement.  It is basically a number generator that you create and define how to increment it.  For example, to insert new unique or pk values an insert statement might look like  this:</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=166192317-12072000><STRONG>insert into table customers values (<FONT color=#ff0000>cust_id_seq.nextval</FONT>, value2, value3 ...),
</STRONG></SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=166192317-12072000>where
<STRONG>cust_id_seq </STRONG>is the sequence and the nextval function is how to
pull the next available number within the sequence.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=166192317-12072000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=166192317-12072000>My
apologies in advance if I have missed the gist of your question.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=166192317-12072000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=166192317-12072000>James</SPAN></FONT></DIV>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">

  <BLOCKQUOTE style="MARGIN-RIGHT: 0px">     <DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma     size=2>-----Original Message-----<BR><B>From:</B> Glenn Travis     [mailto:Glenn.Travis_at_wcom.com]<BR><B>Sent:</B> Wednesday, July 12, 2000     10:23 AM<BR><B>To:</B> Multiple recipients of list     ORACLE-L<BR><B>Subject:</B> sequences<BR><BR></DIV></FONT>     <DIV><FONT face=Arial size=2><SPAN class=770075615-12072000>I wanted to ask     how people are handling the insertion of unique keys in Oracle.  Please     understand that my experience with generating unique keys comes from an     Informix background, wherein you can have a column with datatype of     'serial'.  This is an integer column which gets generated at insert     time with the next value automatically.  You can then check the sqlca     area (return buffer) for the inserted value.</SPAN></FONT></DIV>     <DIV><FONT face=Arial size=2><SPAN
    class=770075615-12072000></SPAN></FONT> </DIV>     <DIV><FONT face=Arial size=2><SPAN class=770075615-12072000>I know in Oracle     you can accomplish this with the SEQUENCE function.  As this is not     automatically inserted by Oracle, how is the unqiue key value     inserted?  Do most people use a before trigger, stored procs, or     call the sequence themselves?  If using a client program (c, java), how     is the value for the unique key returned to the program which performed the     insert?</SPAN></FONT></DIV>
    <DIV> </DIV>
    <DIV><FONT face=Arial size=2><SPAN class=770075615-12072000>Examples,     methods, design recommendations welcome...</SPAN></FONT></DIV>     <DIV> </DIV></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML> Received on Wed Jul 12 2000 - 12:29:02 CDT