automatically assigned record ID

[mullrich24]

I recently installed oracle 8i on a windows 98 platform and am now trying to establish the tables for a new database. First let me say that I am faily aware of the relational database concept but very new to oracle and putting the concept to work. Having said this:

I want to establish a table that should have a primary key (lets say account_ID) that is established automatically by the database everytime a new record is created. everytime a new record is created I want the database to automatically assign a new unique account_ID that can be used to identify the associated record. how do I design and establish the table with oracle 8i navigator? I would like the account_ID to be a 6 digit number and would like to start the first record with 100000 (second record 100001).

I have been trying to use the rowID (sounded right and the documentation doesn't shed much light on how to use it). When I try to input data into the table the column using type rowID says N/A. If I enter the remaining fields of theat record and try to insert a new record I get the following error
ORA-01400: cannot insert NULL into (column with type rowID)

Please help.
Marc

[sambavan]

The rowid is something that you can use for query and not for inserting in. This rowid gets assigned by oracle internally. The best way to establish your needs is use the SEQUENCE and then insert those sequence number for your primary key.

Sam

[edwtsang]

You may use Oracle Sequence to get unique primary key.

CREATE SEQUENCE UNIQUENUM START WITH 100000 INCREMENT BY 1 NOCACHE

Then, you can use

SELECT UNIQUENUM.NextVal from Dual to get latest sequence no

or

INSERT INTO SAMPLE_TABLE (ID, D1, D2) VALUES (UniqueNum.NextVal, 'DATA', 'DATAB');

[nalanda]

--First create a table according to your requirements. e.g.,

CREATE TABLE Temp_Table
(ID number(6),
NAME varchar2(20));

--Then create a SEQUENCE that generates sequentially generated numbers starting with 100000. e.g.,

CREATE SEQUENCE SEQ_Temp_Table START WITH 100000 nocache
/

--Now create a FUNCTION which return the next value to be inserted into your table. e.g.,

CREATE FUNCTION Temp_Table_NEXT_ID RETURN NUMBER IS
X NUMBER;
BEGIN
SELECT SEQ_Temp_Table.NextVal INTO X FROM DUAL;
RETURN X;
END;
/

--Then INSERT your records. e.g.,

INSERT INTO Temp_Table Values
(Temp_Table_NEXT_ID , 'MYNAME');

I think this should work.

Hope this helps.

[mullrich24]

thanks so much - that was the concept I was looking for. now I know how I establish a sequence object. what do I have to do to use the sequence with the table. I assume I establish the ID column as number type, size 6, scale 0, no default, primary key 1, no foreign key. Sorry for being such a beginner.

Marc

[mullrich24]

i was a bit late with my reply. how can I do this in oracle 8i navigator. if not which application do I use (sql plus?)

thanks
Marc

[pando]

CREATE SEQUENCE PR_SEQ_COD_OFI
INCREMENT BY 1
START WITH 100000
NOCYCLE
NOCACHE
;


INSERT INTO XYZ(COD_OFI,)
SELECT PR_SEQ_COD_OFI.NEXTVAL
FROM ABC
;

is this what you are looking for?