Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Autoincrement with Oracle tables

Re: Autoincrement with Oracle tables

From: Michael Krolewski <vandra_at_u.washington.edu>
Date: Tue, 20 Apr 1999 00:49:18 -0700
Message-ID: <371C317E.A81B78@u.washington.edu>

Brian Lavender wrote:

> I am trying to use paradox as a front end to Oracle tables. I created a
> sequence in Oracle and I want to grab the sequence value and insert it as the
> key for the table for a form I create. How do I do this?
>
> Here is the info on the Oracle table.
>
> parent table
> ---------------------
> parentID number
> first varchar2(8) unique
> last varchar2(8)
> sex varchar2(1)
>
> This is the sequence I created
> create sequence parentID increment by 1 start with 100;
>
> I know the following sql statement will insert a row with a unique ID from the
> sequence table:
>
> insert into parent (parentID, first, last, sex)
> values
> (parentID.value, 'Joe' , 'Schmoo' , 'M');
>

Like the other response, one could use a trigger to populate the parentID.

I suspect that you would like to use the parentID in subsequent operations. An alternative is to build your own trigger.

    Whenever you want to start working with the parentID, you execute

            select parentID.nextval from dual into :something

    This gives you a local copy of the sequence number. When you do the insert use this value. And it can be applied given to all child records (sorry about the pun).

    Remember, once a sequence number is used, it is used. Generally, no one is too concerned about running out of a sequence. The problem comes up if you decide to discard the processing of a parent record prior to commit. That should happen.

 Another alternative is to roll your own sequence number with something like

            select max(parentID) from parent into :something

            Then add 1 to something.

 The major drawback here is if two or more clients do this at the same time, only one has a valid parentID. The others need code to accommadate the fact that there is a unique constraint violation and to change values accordingly. This code can be written but it is generally a pain.

Mike Krolewski

> My guess is I could trap in the event model at data insert record, disable
> default, and insert the values with a sql statement. If this is correct, where
> in the event model would I trap for this?
>
> brian
> ------------
> Brian E. Lavender
> US Army Corps of Engineers -- Programmer / Systems Analyst
> Sacramento, CA (916) 557-6623
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Tue Apr 20 1999 - 02:49:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US