Home » Developer & Programmer » Forms » sequences
sequences [message #300798] Mon, 18 February 2008 02:48 Go to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20891
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 Go to previous messageGo to next message
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 #300838 is a reply to message #300812] Mon, 18 February 2008 04:14 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
what is the best way to generate a unique invoice number
A sequence; you've already done that.

Quote:
it is not allowed to have some missing numbers
What would happen if some numbers are missing? Once again: alter the sequence. Is the effort worth it? I don't know (not from my point of view).

Check this Ask Tom discussion. A conclusion?
Tom Kyte
If an application requires a sequence which never skips any numbers, a sequence CANNOT BE USED
Re: sequences [message #300886 is a reply to message #300838] Mon, 18 February 2008 08:28 Go to previous messageGo to next message
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 #300938 is a reply to message #300886] Mon, 18 February 2008 15:25 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In a single-user environment you could even do something like
SELECT MAX(inv_no) + 1
  INTO l_inv_no
  FROM your_table
However, in a multi-user environment, it will sooner or later cause the same 'inv_no' to be selected and you'll have to deal with the unique key violation. That's why we use sequences and don't care about gaps.
Re: sequences [message #300965 is a reply to message #300938] Mon, 18 February 2008 18:30 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
If you really, really, MUST have a 'no holes' number then use Littlefoot's code in the database 'insert' trigger. By definition, only one copy of the database trigger for a particular table runs at any one time.

David
Re: sequences [message #300974 is a reply to message #300798] Mon, 18 February 2008 21:31 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #300979 is a reply to message #300974] Mon, 18 February 2008 22:02 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Jiri,

What's wrong with 'MAX(inv_no) + 1' in the 'before insert' database trigger? As long as this is the only place where this calculation is done, you should be okay. If the commit fails, the 'inv_no' will not be incremented.

David
Re: sequences [message #300993 is a reply to message #300798] Mon, 18 February 2008 23:11 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #301001 is a reply to message #300993] Mon, 18 February 2008 23:53 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Jiri,

Are you saying that one table can have two database 'insert' triggers running at the same time?

David
Re: sequences [message #301066 is a reply to message #300798] Tue, 19 February 2008 02:07 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Jiri,

Thank you for testing and confirming this long time held belief of mine.

David
Previous Topic: ESC KEY
Next Topic: Master-detail relationship on 2 tabs : coordination problem
Goto Forum:
  


Current Time: Sun Dec 04 16:37:02 CST 2016

Total time taken to generate the page: 0.09450 seconds