Re: Newbie question -- auto counter

From: M. Armaghan Saqib <armaghan_at_yahoo.com>
Date: Fri, 10 Mar 2000 03:06:13 GMT
Message-ID: <8a9or2$jhq$1@nnrp1.deja.com>

Oracle provides a 'sequence' object which needs to be linked to a table column through a trigger to provide such a functionality. A little more work
then MS Access or SQL Server.

Fortunately my freeware SQLPlusPlus allows you to generate all this code with a single command in SQLPlusPlus. Following is an example of generating
dept ids for SCOTT.DEPT table.

SQL> exec S2.BLDSEQ('seq_deptno','ORACLE','dept', 'deptno')
| ----------------------------------------------------------------------

---

-
| DROP SEQUENCE seq_deptno;
|
| CREATE SEQUENCE seq_deptno
| START WITH 1 INCREMENT BY 1
| MINVALUE 1 MAXVALUE 999999999999999999999999999
| CYCLE CACHE 20 ORDER;
|
| -- This optional trigger applies sequence values to a table column
thus
| -- creating an auto-increment column which can be used as primary-key
|
| CREATE OR REPLACE TRIGGER trg1_dept
| BEFORE INSERT
| ON dept
| FOR EACH ROW
| BEGIN
| SELECT seq_deptno.NEXTVAL
| INTO :NEW.deptno
| FROM DUAL;
| END;
| /

SQLPP even allows you to simulate a sequence using a database table.

SQL> exec s2.bldseq('seq_deptno','TABLE','dept','deptno')
| ----------------------------------------------------------------------

---

-
| CREATE TABLE seq_table(
| seq_name VARCHAR2(30) PRIMARY KEY,
| next_value NUMBER,
| inc_by NUMBER);
|
| INSERT INTO seq_table VALUES('SEQ_DEPTNO', 1, 1);
|
| CREATE OR REPLACE TRIGGER dept_ins_trg1
| BEFORE INSERT
| ON dept
| FOR EACH ROW
| BEGIN
| SELECT next_value
| INTO :NEW.deptno
| FROM seq_table
| WHERE seq_name='SEQ_DEPTNO' FOR UPDATE;
| UPDATE seq_table
| SET next_value = next_value + inc_by
| WHERE seq_name='SEQ_DEPTNO';
| END;
| /

regards,
M. Armaghan Saqib

+---------------------------------------------------------------


| 1. SQL PlusPlus => Add power to SQL Plus command line

| 2. SQL Link for XL => Integrate Oracle with XL
| 3. Oracle CBT with sample GL Accounting System
| Download free: http://www.geocities.com/armaghan/

+---------------------------------------------------------------

| SQLPlusPlus now on http://www.ioug.org/
| "PL/SQL package that extends SQL*Plus to another dimension.
| Contains a PL/SQL code generator and set of extremely useful
| utilites with extensive documentation." IOUG Web Site

+---------------------------------------------------------------

Young Lee <young.lee_at_supplysolution.com> wrote in message news:38C856E2.249DA02E_at_supplysolution.com...

>
> In SQL Server a column can be created with an identity property.
> Whenever a new record gets inserted, the field is automatically
> incremented by a defined value like incremented by 1 or whatever.
>
> Is there any equivalent thing in Oracle?
>
> Thank you,
> Young

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Mar 09 2000 - 21:06:13 CST