Re: identity in MSSQL7, is Oracle has this concept?

> I'm trying to port a program written to access MSSQL7 database to work with
> Oracle8i.
> Now I have this question:
> In MSSQL 7, you can assign a column with int datatype a IDENTITY attribute
> when you create the table. The data value of this column will then be
> automatically inserted by the database server when new row is inserted in
> the table, user cannot modify the value in this column and the value in the
> column is guaranteed to be unique.
> In Oracle8i, it seems that there is no such keyword as IDENTITY but there is
> a datatype ROWID which seems to serve the same purpose. But when I try to
> retrieve the value of this ROWID column by using "select rowid from
> abctable", the value returned in this rowid column read
> AAAA5xAAJAAAAASAAA for first row and
> AAAA5xAAJAAAAASAAB for second row.
>
> Is there a way to convert this value to a human readable integer?
> Or is there other "CORRECT" way to mimic the IDENTITY function is MSSQL7?
>
> Thanks and best regards
> Lee

Rowid is what is known as a pseudocolumn. It is how Oracle identifies the location of a row of data on a hard disk or disk array and has nothing to do with your issue. The only time you want to concern yourself with ROWID is when doing an UPDATE statement in which case saying:

WHERE rowid = cursor.rowid

is the fastest update possible.

To solve your problem create a sequence and use it in the following manner.

INSERT INTO mytable
(id_column)
VALUES
(mysequence.nextval);

This functionality could also be put into a BEFORE-INSERT trigger which would be the closest mimic for MSSQL7's methodology.

Daniel A. Morgan Received on Fri Jan 12 2001 - 00:58:44 CST