Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help ORACLE.

Re: Help ORACLE.

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Wed, 26 Jun 2002 21:45:34 +0100
Message-ID: <3d1a27ed$0$8508$cc9e4d1f@news.dial.pipex.com>


if you absolutely must have consecutive numbers then you shoudl create a counter table and do something like the following.

begin transaction
lock table counter;
insert into tab(id,) select max(id) from counter; insert rest of values
update counter set id = id+ 1;
commit;

ie you must serialize on the insert. you run into problems when the sequence of events goes

11:59am.
begin transaction
lock table counter;
insert into tab(id,) select max(id) from counter; insert rest of values
update counter set id = id+ 1;
<go to lunch come back late and get called into meeting> 4:30pm.
<oops forgot to save my work>
commit;

--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Oscar" <oscar_ll_at_jofre.com> wrote in message
news:afcngs$k3a$1_at_nsnmrro2-gest.nuria.telefonica-data.net...

> Hi, I have a big problem with Oracle. Can anyone help me, make the test.
> The problem is that the sequence is not correlative.
>
> CreateTable:
>
> drop table LGC02
> ;
> drop sequence ID_LGC02
> ;
> create table LGC02 (
> ID_LGC02 DEC (9,0) NULL,
> TIARLG CHAR (1) NULL,
> TITRLG CHAR (1) NULL,
> FECHLG DEC (6, 0) NULL,
> HORALG DEC (6, 0) NULL,
> WSIDLG CHAR (10) NULL,
> USERLG CHAR (8) NULL,
> DATA_A CHAR (200) NULL
> )
> ;
> create unique index IID_LGC02 on LGC02 (ID_LGC02)
> ;
> create sequence ID_LGC02 start with 1
> ;
>
> Now Introduce values:
>
> insert into LGC02 ( ID_LGC02,TIARLG, TITRLG, FECHLG, HORALG, WSIDLG,
USERLG,
> DATA_A)
> VALUES (ID_TIGSA_LGC02.nextval, 'O',
> 'A', 260602, 165225, 'SERVIDOR ',
> 'CGIFS ',
> 'VC11020601000011020601
>
0000000000000000000000000000000000000000000000000000000000000000000000000000
> ')
>
> make this sentence 4 times. ( then ID_LGC02 will be 1,2,3,4 )
>
> NOW the sentence:
>
> select ID_LGC02.nextval from LGC02
>
> OK if you did insert 4 times it will show 5,6,7,8 !!!! OK but then
>
> THIS IS THE MISTAKE !! OR NOT
>
> NOW:
>
> insert into LGC02 ( ID_LGC02,TIARLG, TITRLG, FECHLG, HORALG, WSIDLG,
USERLG,
> DATA_A)
> VALUES (ID_TIGSA_LGC02.nextval, 'O',
> 'A', 260602, 165225, 'SERVIDOR ',
> 'CGIFS ',
> 'VC11020601000011020601
>
0000000000000000000000000000000000000000000000000000000000000000000000000000
> ')
>
>
> is supose to introduce like a next val 5 but NOT it puts 9 !!!!
>
>
> My problem is that I need to have 5 insteas of 9. Can Anyone help
> me ? Thank's. Oscar
>
>
Received on Wed Jun 26 2002 - 15:45:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US