Re: SQL Server 7 vs Oracle regarding IDENTITY

"Moshe Solomon" <moshe_at_impactinfosys.com> wrote in message news:tcf23mjgngoocd_at_corp.supernews.com...
> I'm very familiar with SQL Server 7. How do I duplicate the IDENTITY
 concept
> to Oracle. I know there is something called ROWID, but I'm unsure how this
> works. Do I need to create a field specifically for the row id as I would
 in
> SQL Server, or does each row in a table in Oracle have this attribute
> automatically. Can I reference this ROWID in my procedures? I'm very new
 to
> Oracle, so please be patient.
>
> Regards,
> MS
>
>

Rowid is the internal Oracle identification of a row. It cannot be compared with the identity concept. You would also be wise to use it never in any procedure. In Oracle 7 you could be sure the rowid didn't change for the live of a row, in Oracle 8 with partitioning enabled you can't. You need to use a feature of Oracle which is called sequence. A sequence can be used everywhere and is not bound to a specific table. Your 'identity' column would typically be set using a before insert for each row trigger
using
select <sequence>.nextval
into :new.<identity column>
from dual;

Further discussions in the Oracle Application Developers Guide.

Hth,

Sybrand Bakker, Oracle DBA Received on Sun Apr 01 2001 - 15:06:38 CDT