Call A Sequence To Generate Unique Id With In A Procedure

12:09 Admin 5 comments

First Create A Sequence:-

CREATE SEQUENCE seq
    INCREMENT BY 10
    START WITH 1
    MAXVALUE 10000
    NOCACHE
    NOCYCLE;

Now Create A procedure And Use This Sequence:-

create or replace procedure ST_INSERT(
                                       ST_NAME VARCHAR,
                                       ST_BRANCH VARCHAR )
as
begin
insert into STUD (STUDENT_ID,NAME,BRANCH)
          values ('CDS'||SEQ.NEXTVAL,ST_NAME,ST_BRANCH );
     end ST_INSERT;
     /

Posted in: PL/SQL,Procedure

5 comments:

iRod says:

7 February 2012 at 21:35

Use TYPE or ROWTYPE instead of VARCHAR to avoid buffer overflow.

In a multiuser environment you'll never know wich ID was taken, use RETURNING clause on statement.

A FUNCTION will fit better for this, returning unique ID or SQLCODE.

Hope this helps you...

Anonymous says:

8 February 2012 at 03:20

Actually... It is VERY common to do this sort of thing in a Before Insert DB Trigger.
I do this all the time for just about all of my tables.
However, I usually do...
CREATE SEQUENCE seq
START WITH 1
INCREMENT BY 1
ORDER
NOCACHE
NOCYCLE;

Admin says:

9 February 2012 at 14:27

Thanks for your ideas

Anonymous says:

11 February 2012 at 22:23

The use of ORDER NOCACHE is really bad practice. It's about as slow as you can go. You should at least use NOORDER CACHE 100.

I would suggest moving away from sequences and use GUIDs instead:

create table t (
id raw(16) default sys_guid() primary key,
other_columns... );

This avoids having any PL/SQL or triggers at all. And it guarantees that all ID's across all databases will be unique.