Re: Sequential numbering in Oracle

The only problem now is a big difference in the behavior between Access and Oracle when it comes to these new autonumber values.

When you begin to insert a new row in an Access table with an Autonumber field on it, the new value of that field is available in your recordset.

In Oracle, this number is only generated when you post your change to the table.

Ordinarily not a problem, unless you have some master-detail stuff in an Access form. In which case, your new autonumber for the detail forms isn't really available yet in your recordset, because Access works on the assumption that this new autonumber value is available in your recordset... You can do some trickery to get it (i.e., select autonumberfield.nextval from dual), and modify your triggers on both tables so that if you have passed in a value from autonumberfield that it'll use that value...

On 6 Mar 2002 09:18:30 -0800, ed.prochak_at_alltel.com (Ed prochak) wrote:

>If you are not sure ORACLE has a feature, try to RTFM. ACCESS does
>have some fine features. Now, assuming this isn't a troll to start
>another DB flame war, I'll provide some suggestions where to look.
>
>ORACLE provides a SEQUENCE to deliver a series of numeric values.
>
>When inserting or updating to a table, you can have a TRIGGER that
>adds the next value from the SEQUENCE to the row. This is a very
>common practice in ORACLE. The difference is that there may be gaps in
>the sequence in ORACLE. This is due to the inherent multiuser design
>of ORACLE. You really shouldn't care if there are gaps in the
>sequence. (If you do HAVE TO HAVE a continuous sequence, there are
>ways to control this.)
>
>When loading a table from a flat file, the SQL*Loader also has a
>SEQUENCE feature. This will give you exactly the same result as ACCESS
>would, in particular: a sequence with no gaps. Note the SEQUENCE in
>the loader is different from the SEQUENCE object in the database
>itself.
>
>There are examples of all of this in the manuals. Have a look and try
>it.
>
>If you have specific coding problem, post a small example of the code
>that shows the problem and you will receive lots of help from the fine
>folks here.
>
>And if you were a troll:
> Just remember: where ACCESS holds your hand and gently steps you
>through it, ORACLE expects you to do your own work, especially reading
>the manuals. In the end with ORACLE you can lift much heavier
>workloads than with ACCESS.
>
>Have a great day.
> Ed Prochak
> Magic Interface, Ltd.
> http://www.magicinterface.com
>
>"trub3101" <michael_at_bassline01.fsnet.co.uk> wrote in message news:<a64rj4$re3$2_at_newsg2.svr.pol.co.uk>...
>> Hi everyone,
>>
>> I have just migrated a MS Access database to Oracle 8i by exporting it to a
>> flat comma-seperated file and using SQL*Loader populate the Oracle database.
>>
>> After much tinkering during populating the Oracle database I now seem to
>> have lost the functionality of the primary key (ref) which in Access
>> automatically generated the next number for the next record appended to the
>> database.
>>
>> Could anyone just clarify for me whether or not Oracle provides this
>> functionality?
>> I suspect that it does not, not sure why?
>>
>> Will I have to write some sort of procedure or routine that does this?
>>
>> My first inkling is to create a Java singleton class that generates
>> consecutive numbers when access and link it to the primary key of the table.
>> Am I mad?
>>
>> Thanks (in advance)
>>
>> tb3101
Received on Sun Mar 10 2002 - 21:24:49 CST