Solved

Using sequential id numbers in Oracle

Posted on 2004-08-16
7
818 Views
Last Modified: 2008-03-03
I am new to using oracle, and i have created a table with these commands:


CREATE TABLE contacts_cat (
 id number(9) NOT NULL,
 name varchar2(50),
 incld number(1),
 nick varchar2(15))
tablespace user_data storage(initial 500k next 500k pctincrease 0) / alter table contacts_cat add constraint pk_contacts_cat PRIMARY KEY (id) using index tablespace indx;

create sequence seq_contacts_cat increment by 1 start with 1 minvalue 1 maxvalue 100000000 cycle cache 30;

But I am not really sure what all of the commands mean.
Now I need to insert into the Db.

How do I make use of the sequential numbering for the id field (i.e., I just want each new line to follow a sequential numbering pattern and increment by one every time so that there are no duplicates).

Thanks,
-MD
0
Comment
Question by:mderbin
7 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 75 total points
ID: 11810169
When you insert into the table, for the id column you need to use seq_contacts_cat.nextval to get the next sequence number.

If you do not want to put it in the SQL, you can create a trigger like this:

create trigger <trigger_name>
before insert on contacts_cat
for each row
begin
  :new.id := seq_contact_cat.nextval;
end;

The trigger will populate the ID field for you.
0
 
LVL 1

Expert Comment

by:lobitojc
ID: 11810213
Hi
what development tool are you using ???
0
 

Expert Comment

by:suganthkumar1
ID: 11810234
hi,

you can do it in two ways

1. you can use the sequence directly

insert into contacts_cat (id, name, incld, nick) values (seq_contacts_cat.nextval, ......);

2. or you can create a before insert trigger

create or replace trigger <trigger_name>
before insert on contacts_cat for each row
begin
select seq_contacts_cat.nextval into new.id from dual;
end;

if you are using the trigger, you can give the insert query as

insert into contacts_cat (name, incld, nick) values ('some name', ....);
the id field will be filled automatically


Suganth
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:mderbin
ID: 11810794
I have placed it in the SQL, but it gets an error of 'Page cannot diplay':

Here is my insert statement:

INSERT into products (id,cat,type) VALUES ("+seq_products.nextval+"," + $cat + "," + $type + ")

The statement works if I put a number in there, but when I put the nextval piece in there, it doesn't work - do I need to declare the nextval variable somewhere?

Thanks,
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 50 total points
ID: 11811002
Rewrite your insert like this:

INSERT into products (id,cat,type) VALUES (seq_products.nextval," + $cat + "," + $type + ")

0
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11811147
>>create sequence seq_contacts_cat increment by 1 start with 1 minvalue 1 maxvalue 100000000 cycle cache 30;

I think you know what start by 1, increment by 1, minvalue and maxvalue, mean.

The cache clause is one that will speed up large bulk inserts, but can cause some numbers to be skipped between sessions.  If this doesn't matter, caching will improve performance on bulk inserts.  If you are inserting a lot of rows, bump this up, and change it later with an ALTER SEQUENCE command.

You can also use sequence.currval to see what the current value is.  But, if you are caching, this may give an error if you have not done a nextval in your session.

Hope this helps.

0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 11813115
If you want to use the sequence directly in the insert statement from your application (like in MikeOM DBA's last example) you may be missing a grant and/or synonym in the database.  You will need these if the Oracle user account for the application is different from the Oracle account used to create the sequence.  You won't need a synonym or grant if you fill this column with a trigger (assuming that the table, trigger and sequence are all in the same schema).

To create a grant, log in as the owner of the sequence, then:
grant select on [sequence_name] to [application_user];

If this user can create public synonyms, then:
create public synonym [sequence_name] for [sequence_name];

If this user cannot create public synonyms, then you need to do one of these:
1. log in as a DBA, then give the sequence owner permission to create public synonyms, then log in as the sequence owner and create the public synonym
2. log in as a DBA, and create the public synonym like this:
create public synonym [sequence_name] for [sequence_owner].[sequence_name];
3. log in as the application user and create a private synonym:
create synonym [sequence_name] for [sequence_owner].[sequence_name];
 
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Format Number Field 10 39
Salary Amount Format 13 56
Oracle Syntax 8 42
Oracle -- identify blocking session 24 22
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now