Sequence Numbering Question 2

[sitadba]

All,

I am using TOAD on and Oracle 8i database. I have created a sequence called seq_loc_id. Now I need to implement that sequence into an existing table. I'm not sure how.

My table is ops_locations. There is a column called pk_loc_id. There is currently no data in this table. Can I alter this table so that the column pk_loc_id uses the sequence?

CREATE TABLE OPS_LOCATIONS (
PK_LOC_ID NUMBER (8) NOT NULL,
GREGION VARCHAR2 (30) NOT NULL,
CITY VARCHAR2 (30),
LOCATION VARCHAR2 (60),
COUNTRY VARCHAR2 (30),
LOCATION_TYPE VARCHAR2 (25),
CONSTRAINT PKOPS_LOCATIONS
PRIMARY KEY ( PK_LOC_ID )

Thank you & Brgds,
Denise

 

[SantaMufasa]

Denise,

If there are no data in your table, then at that point when you do start populating the table, that is when you access your sequence for values. To do so, you could use code such as this:

INSERT INTO OPS_LOCATIONS values (seq_loc_id.nextval,'Northeast','New York',...);

If, however, there were data already in your table and you wished to uniquely sequence those pre-existing rows, then you could say:

UPDATE OPS_LOCATIONS set PK_LOC_ID = seq_loc_id.nextval;

The problem in this example is that PK_LOC_ID already would have values since the column has a "NOT NULL" constraint.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:30 (23Jun04) UTC (aka "GMT" and "Zulu"), 11:30 (23Jun04) Mountain Time)

 

[sitadba]

Dave,

Thank you very much. That was a big help. I have 1 more question. The end users will be updating this table via a user interface application and not through an insert statement via sql plus. Do I need a trigger on insert whenever a record is being added to the ops_locations table? What would be the syntax for calling the sequence?

Thank you very much.

Brgds,
Denise

 

[SantaMufasa]

Denise,

I'm a bit puzzled..."OPS_LOCATIONS.PK_LOC_ID" is NOT NULL. The "user interface application" must be doing something to PK_LOC_ID to avoid an INSERT error. Can you explain what is happening in the user interface application to avoid an error? Once we resolve that, then we can look at follow-on alternatives for PK_LOC_ID.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:13 (23Jun04) UTC (aka "GMT" and "Zulu"), 14:13 (23Jun04) Mountain Time)

 

[sitadba]

Dave,
There is no data yet, so we haven't run into a problem. The user interface is TOAD. Next week we will be manually updating the table 1 record at a time and not from an insert statement. I want to be able to add all the fields except pk_loc_id. Then when I save, the pk_loc_id field is populated with the next sequence #.

What we will need to do otherwise is manually enter the next available seq #. 1, 2, 3, 4 etc..

Brgds,
Denise

 

[SantaMufasa]

Denise,

If you want to automatically assign a unique sequence value as part of the INSERT process without having to refer explicitly to the sequence in TOAD, then here is a trigger that does what you want:

create table ops_locations (pk_loc_id number not null, x varchar2(20));
create or replace trigger OPS_LOCATIONS_PK_CREATE
	before insert on OPS_LOCATIONS for each row
Begin
	select seq_loc_id.nextval into :new.pk_loc_id from dual;
End;
/

Note that if you abandon (rollback) the TOAD record insertion, the sequence value is already "gone". But that is just the way life with Oracle sequences: once you refer to a sequence value, it's similar to shopping at a fine China store: You break it, you buy it.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:52 (23Jun04) UTC (aka "GMT" and "Zulu"), 14:52 (23Jun04) Mountain Time)

 

[SantaMufasa]

Sorry, the "CREATE TABLE..." statement, above, is extraneous to the solution. (I just accidentally pasted the CREATE TABLE in the post.) You just need the CREATE...TRIGGER... statement.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:55 (23Jun04) UTC (aka "GMT" and "Zulu"), 14:55 (23Jun04) Mountain Time)

 

[sitadba]

Dave,

This is exactly what I needed. Thanks alot for all your help.

Brgds,
Denise