Sequence Key in Oracle

[9911782]

Hi

Please help. I need to know on how does sequence key works in Oracle? How do you create the sequence key in an existing table.

The main reason why asking this, I've been adding some new records on Ms Access forms, but now when trying to retrieve them, I can't find them.So, somebody told me that, I should put the sequence key in my table where it will increment for every new entry in the table.

Ur help is appreciated.
Thankx
9911782

 

[blom0344]

You need to create a sequence object in Oracle itself and call this in the insert DML.

CREATE SEQUENCE SEQ1
INCREMENT BY 1
START WITH [..........]
NOCYCLE
NOCACHE

The start value is set at the recordcount of the current table + 1.

Create a copy of the table, truncate the table and add a field to hold the new sequencefield.

Read data from the copy into the empty table like:

INSERT INTO TABLE
(SELECT SEQ1.NEXTVAL,T.* FROM COPY T)
COMMIT;

With Nocache option you are enforcing that no gaps will occur. However this will not work if a rollback is ever used.
A safer bet is to increment from the application (in this case Access), which will offer more control.



Ties Blom