 alter sequence when an exception occurs in PL\SQL [message #379297] |
Mon, 05 January 2009 23:55  |
AnjuMS
Messages: 2
Registered: January 2009
|
Junior Member |
|
|
Hi All,
I have a table whose primary key is generated using a SEQUENCE. While inserting records if any exception happens I need to decrement the SEQ by -1 and continue in the loop. This is reqired because I dont want to miss any number in the SEQ in case of an exception.I have written the below statements for the same
S_TEMP := 'ALTER SEQUENCE SEQ_NO INCREMENT BY -1';
S_TEMP1 := 'ALTER SEQUENCE SEQ_NO INCREMENT BY 1';
FOR ... LOOP
BEGIN
SAVEPOINT loop;
SELECT SEQ_NO.NEXTVAL INTO T_SEQUENCENO FROM DUAL;
EXECUTE IMMEDIATE S_TEMP1;
Commit;
EXCEPTION WHEN OTHERS THEN
EXECUTE IMMEDIATE S_TEMP;
ROLLBACK to loop;
END;
END LOOP;
But while executing the procedure the loop is not continuing. After the SEQ is decreemented its not processing the next record. Please help me with this issue. I am new to PL\SQL scripts.
[Updated on: Mon, 05 January 2009 23:56] Report message to a moderator |
|
|
|
| Re: alter sequence when an exception occurs in PL\SQL [message #379304 is a reply to message #379297] |
Tue, 06 January 2009 00:23  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| Quote: | This is reqired because I dont want to miss any number in the SEQ in case of an exception
|
I am not sure what you mean by "miss", as you do not use its value anywhere in the block.
However, if you want "gapless" value generator, do not use sequences. You will need to serialize access to "inserting records" (only one session will be able to do it in one moment), so there may be problems with multiple sessions as they will have to wait on the currently "inserting" ones. Possible solutions are described in e.g. this thread: http://www.orafaq.com/forum/m/300974/96705/#msg_300974.
|
|
|
|
|
|
|
|
| Re: alter sequence when an exception occurs in PL\SQL [message #379308 is a reply to message #379306] |
Tue, 06 January 2009 00:38 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
You are missing the point flyboy is making: if you use sequences you will, one day or another, end up with gaps in your numbers.
Normally (99.999% of the time) this is no issue, because sequences are used to fill ID fields that need to be unique.
Why does it matter that there are gaps? Maybe we can help better if we know the reason.
For example: is it allowed to have gaps in between insert-streaks?
|
|
|
|
| Re: alter sequence when an exception occurs in PL\SQL [message #379310 is a reply to message #379304] |
Tue, 06 January 2009 00:45 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| flyboy wrote on Tue, 06 January 2009 07:23 | However, if you want "gapless" value generator, do not use sequences.
|
Sequences are designed for getting unique values, not gapless. Period. By the way, are you aware that ALTER SEQUENCE statement (as any DDL) makes implicit COMMIT, so it ends the transaction and starts a new one. Not a good idea inside the LOOP.
By the way, ALTER SEQUENCE just hm... alters the sequence; it does not change its value.
|
|
|
|
| Re: alter sequence when an exception occurs in PL\SQL [message #379500 is a reply to message #379297] |
Tue, 06 January 2009 17:38 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
A gapless sequence is a dream. There is no way to create a gapless sequence that works, in any system that requires concurrency. For a single user system, gapless is fine, but what database today is single user?
Your first step AnjuMS is to go back to whoever told you to use a gapless sequence and ask them why. Then ask them if they understand what a single user system is and if they want their system to be single user. Then explain to them why a gapless sequence makes mutli-user systems act like single user systems.
20 years ago, this is how I was taught to create a gapless sequence.
create table gapless_sequence_table
(
seqno integer not null
)
/
insert into gapless_sequence_table values (0)
/
commit
/
The above table is assumed to have all the necessary constraints and checks to ensure there is only one row in the table at a time. Additionally, this table must be protected with backups etc. Lastly, code must use the table correctly.
This is the hard part of course, getting all code to use the sequence table correctly. You must do the following:
declare
seqno_v integer;
begin
update gapless_sequence_emp set seqno = seqno + 1;
select seqno into seqno_v from gapless_sequence_table;
insert into emp values (seqno_v,'Joe');
-- ... do the rest of my transaction work here ...
-- ... if this takes 2 hours to complete then no one else does work for 2 hours ...
commit;
end;
/
The above equates to this:
| Quote: | 1) lock the one row in the table
2) update its seqno column to +1
3) read the row you just updated
4) do your transaction (which might involve multiple trips to this table)
5) commit
|
The problem of course is that while you are doing your work, no one else can get access to this gapless sequence. If they did, and you subsequently did a rollback, there would be a gap in allocated sequence numbers when done.
So, a gapless sequence serializes access (serialize means makes everbody get in line and wait for you to check out) to all people needing the sequence such that only one person can do work at a time. The gapless sequence has turned your system into a single user system.
Over the years I have seen many variations on this theme, but they all had flaws, the most flagrant of which being that many of these alternatives allowed for "temporary gaps" in the sequences. I guess these are just "loose interpretations" of gapless.
So my friend, go back to the people who want gapless and tell them NO. If you need help, point them here and we will be happy to explain why.
Good luck, Kevin
[Updated on: Tue, 06 January 2009 17:40] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
