| sequences [message #300798] |
Mon, 18 February 2008 02:48  |
akramrabaya
Messages: 41
Registered: November 2007
|
Member |
|
|
if you generate a new value for a sequence inthe form suppose you have a sequence to generate an inv_no
when button press trigger
begin
..
..
..
..
select inv_no_seq.nextval into v_inv_no from dual;
..
..
..
..
..
..
end
now supose for any reason the trigger didn't complete or raise any problem after generating the number how can i cancel this new number generated ...
the problem the in sequance even if u didn't commit the changes take place
akram
|
|
|
|
| Re: sequences [message #300805 is a reply to message #300798] |
Mon, 18 February 2008 02:58  |
 |
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
It doesn't really matter; sequences will make sure that no duplicate values are returned (which is more important than having consecutive numbers), but there may be gaps between fetched values.
You can't "rollback" this operation, so - in order to do what you want - you'd have to alter the sequence. First find the last correct value, find the current sequence value, alter the sequence so that it increments "backwards", fetch the next value, alter the sequence again to increment normally.
Or, you might drop the sequence and use START WITH the "last valid value + 1".
In my opinion, you shouldn't bother at all. Let it be.
|
|
|
|
| Re: sequences [message #300812 is a reply to message #300798] |
Mon, 18 February 2008 03:12 |
akramrabaya
Messages: 41
Registered: November 2007
|
Member |
|
|
so what is the best way to generate a unique invoice number in my case...
( invoice number cannot be duplicated and alsoo it is not allowed to have some missing numbers )
i have multi users issuing invoices at same time
if i didn't use sequences the number maybe duplicated and if i use sequence some number maybe missing !!!! how to cover this two situations !!!!
akram
|
|
|
|
|
|
| Re: sequences [message #300886 is a reply to message #300838] |
Mon, 18 February 2008 08:28 |
akramrabaya
Messages: 41
Registered: November 2007
|
Member |
|
|
the non missing numbers is a clints requirements fore some auditing purposes any way ... i just thought if there is a way to have a unique inv. no. with out any missing numbers
thanks 4 your replies
akram
|
|
|
|
|
|
|
|
| Re: sequences [message #300974 is a reply to message #300798] |
Mon, 18 February 2008 21:31 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
In multiuser environment, you would have to (with problems listed below):
- create a special table with one varchar2 column (identification) and one numeric column (value)
- insert row with useful identification (eg. table name) and initial value
- when requesting a new value, select the actual value for given identification FOR UPDATE (will lock the row), increment it and update that row
- if the request was not successful (other user locks the row), fail and report it to the user
- after commit (or session termination), the row lock is released automatically, no extra action is needed
As only one user will get the requested value, others will have to wait for him and nobody will be able to insert rows into the table. Prepare for complaints (all the time) and the possibility that session will "hang" before commit (eg. wait for another resource).
But, as you want gapless column, this serialization is necessary to ensure it.
|
|
|
|
|
|
| Re: sequences [message #300993 is a reply to message #300798] |
Mon, 18 February 2008 23:11 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
David,
it will not be safe as INSERT and COMMIT is not atomic operation.
Changes in one session are visible to other sessions after COMMIT, so in the meantime (as readers do not block readers) two sessions may get the same number.
You can use other way, eg. lock the whole table, use DBMS_LOCK or SERIALIZABLE transaction for this block, but you have to ensure serialization.
Jiri
P.S. If the filled column is primary key or unique index, INSERT statement will wait until COMMIT, so (if INSERT with triggers is atomic) it should be OK.
But select from the same table in before insert trigger will most likely raise mutating table error. If so, it is unusable.
[Edit: Thought about it deeper, written in P.S.]
[Updated on: Mon, 18 February 2008 23:54] Report message to a moderator |
|
|
|
|
|
| Re: sequences [message #301066 is a reply to message #300798] |
Tue, 19 February 2008 02:07 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
David,
you are right. I was too eager to forget primary key (or unique) constraint, which will ensure the serialization lock. Even mutating does not come into play, as the whole table is locked. My fault, I should try it before, as this demonstration shows:
SQL> CREATE TABLE t1 (
2 c1 INTEGER NOT NULL CONSTRAINT t1_pk PRIMARY KEY
3 );
Table created.
SQL> CREATE TRIGGER t1_tg
2 BEFORE INSERT ON t1 FOR EACH ROW
3 BEGIN
4 SELECT NVL(MAX(c1),0)+1 INTO :NEW.c1
5 FROM t1;
6 END;
7 /
Trigger created.
Session 1: SQL> INSERT INTO t1 (c2) VALUES ('SESSION 1')
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> SELECT * FROM t1;
C1 C2
---------- ----------
1 SESSION 1
2 SESSION 1
3 SESSION 1
SQL>
Session 2: SQL> INSERT INTO t1 (c2) VALUES ('SESSION 2');
-- waiting for Session 1
Session 1: SQL> ROLLBACK;
Rollback complete.
SQL>
Session 2:
1 row created.
SQL> SELECT * FROM t1;
C1 C2
---------- ----------
1 SESSION 2
SQL> COMMIT;
Commit complete.
SQL> |
|
|
|
| Re: sequences [message #301083 is a reply to message #301066] |
Tue, 19 February 2008 02:56 |
akramrabaya
Messages: 41
Registered: November 2007
|
Member |
|
|
mr david and mr Jiri
i tried the both cases let us say the 1st case which i can call it the "update lock" is work smoothly without any problems weather the filed is primary key or not and the hand that u was talking about is not even noted and that solve my problem.
but the second one "database trigger" have this note
it is only work on primary keys or let us unique fields .. and the inv_no in an invoice is not a primary key cuz one invoice may have more than one record ...!!
so we vannot generate a new number 4 each record on the invoice
thank u agian guys 4 the discussion and help
akram
|
|
|
|
| Re: sequences [message #301261 is a reply to message #300798] |
Tue, 19 February 2008 21:46 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
thank you for feedback akram
> is work smoothly without any problems ... the hand that u was talking about is not even noted
If I understand it correctly, the users do not complain now. However some session may block this generation in future, so just be prepared that it may happen.
> cuz one invoice may have more than one record
It seems like very strange design to me. But, as I do not know requirements, it may be good.
|
|
|
|
| Re: sequences [message #301263 is a reply to message #301066] |
Tue, 19 February 2008 22:03 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
Jiri,
Thank you for testing and confirming this long time held belief of mine.
David
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
