| Check Sequence [message #10169] |
Wed, 07 January 2004 03:05  |
Marcel
Messages: 7
Registered: August 2001
|
Junior Member |
|
|
Hi, I'm working on a database and am using a Sequence to increase the number of the primary key. If the user or operator wants to insert 'manually' a new record insert into ...values(33,'Mc Doo'). I want to check if 33 is the next value in the sequence. We want to prevent gaps in our database.How can I do this? I've already tried this :
create or replace trigger seq2
before insert or update on klanten
for each row
DECLARE
huidig NUMBER(4);
hulp NUMBER(4);
BEGIN
hulp:= :new.klant_id;
huidig := kla_id.CURRVAL;
if(huidig <> hulp) then
raise_application_error(-20001,'Verkeerd getal');
end if;
END;
/
sql/plus don't recognize kla_id.CURRVAL in this context. Could you help us out?
|
|
|
|
| Re: Check Sequence [message #10171 is a reply to message #10169] |
Wed, 07 January 2004 03:22  |
resy
Messages: 86
Registered: December 2003
|
Member |
|
|
You can rewrite your code like this.
create or replace trigger seq2
before insert or update on klanten
for each row
DECLARE
huidig NUMBER(4);
hulp NUMBER(4);
BEGIN
hulp:= :new.klant_id;
select kla_id.CURRVAL into huidig from klanten;
if(huidig <> hulp) then
raise_application_error(-20001,'Verkeerd getal');
end if;
END;
/
where kla.id is the sequence name i believe.
try .
|
|
|
|
| Re: Check Sequence (Attention!) [message #10174 is a reply to message #10169] |
Wed, 07 January 2004 04:02 |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
You can NEVER be sure that CURRVAL is not leaving gaps: If I do a "select some_sequence.nextval from dual" a couple of times, the values are lost for this sequence. If user A tries to insert some values using a sequence while user B does the same thing and user B commits while user A rolls back the transaction you have gaps. SEQUENCES WILL LEAVE GAPS!
To quote Tom Kyte:
----------------------------------------------------------------------
There are three things I am sure of in life:
o Death
o Taxes
o Gaps in my sequences
----------------------------------------------------------------------
And perhaps more useful:
----------------------------------------------------------------------
well, if you use a gap free approach -- you know what'll happen in this case?
NO ONE ELSE IN YOUR ENTIRE SYSTEM WILL BE ABLE TO WORK, that is what. Why?
cause this session with uncommitted data will have the id generating table
locked.
Sigh, I hesitate to show you this, but here goes. Gap free, non-concurrent,
highly NON-scalable:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table ids ( name varchar2(30), id
number );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function get_nextval(
p_name in varchar2 ) return number
2 as
3 l_id number;
4 begin
5 update ids set id = id+1 where name = upper(p_name)
6 returning id into l_id;
7
8 if ( sql%rowcount = 0 )
9 then
10 raise_application_error( -20001, 'No such id name ' || p_name );
11 end if;
12 return l_id;
13 end;
14 /
Function created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( get_nextval( 'foo'
) )
BEGIN dbms_output.put_line( get_nextval( 'foo' ) ); END;
*
ERROR at line 1:
ORA-20001: No such id name foo
ORA-06512: at "OPS$TKYTE.GET_NEXTVAL", line 10
ORA-06512: at line 1
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into ids values ( 'FOO', 0 );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( get_nextval( 'foo'
) )
1
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( get_nextval( 'foo'
) )
2
PL/SQL procedure successfully completed.
Now, when you want to reset, just update ids set id = 0 where name = 'whatever'.
Yuck.</HR>You can find more at his site (http://asktom.oracle.com).
Lijkt me een Nederlands programma...
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
