Dear Tom,
we have a table with records for different invoice types. One column contains the number of the last invoice.
We need a function that locks the record counts up by one and then stores the new number ensuring that no other session gets the same number.
We have this code
lock table codif_vdok_nr in row exclusive mode;
begin
select t.co_donr_nr+1,t.co_donr_jahr
into p_doku_nr,p_jahr
from codif_vdok_nr t
where t.co_donr_vser_id = p_vser_id
and t.co_donr_von <= trim(sysdate) -- 21.02.2009
and nvl(t.co_donr_bis,sysdate) >= trim(sysdate) -- to_date(sysdate,'dd.mm.yyyy')
for update nowait;
exception
when others then
po_message := 'doku_nr: Fehler Suche DokumentNr - Serie: ' || to_char(p_vser_id) || ' ' || sqlerrm;
raise_application_error(-20010,po_message );
end;
update codif_vdok_nr t
set t.co_donr_nr = p_doku_nr
where t.co_donr_vser_id = p_vser_id
and trunc(t.co_donr_von) <= trunc(sysdate)
and trunc(nvl(t.co_donr_bis,sysdate)) >= trunc(sysdate);
but it happened that two sessions got the same invoice number.
Could you please tell me where the error is or how it could be solved.
Regards
Andreas
I don't understand exactly how the update relates to the select for update above. So I'm not sure precisely why you're getting duplicate numbers.
That said there are several potential issues with the code, including:
- trim ( sysdate ) => implicit to_char conversion. This could lead to unexpected outcomes:
select * from dual
where sysdate = sysdate;
DUMMY
X
select * from dual
where trim ( sysdate ) = sysdate;
no rows selected
- Using sysdate as a filter means you could select different rows for a given co_donr_vser_id. Could this be the cause?
- How are you stopping other code from inserting/updating this table?
- The when others error handler could be triggered by anything (e.g. too_many_rows). Does the update still execute if the for update fails? If so, where does the value p_doku_nr come from?
I'm guessing you're using this in an attempt to generate gap-free numbers.
This is a hard problem to solve. As suggested above, even if the code works correctly, what's to stop someone changing the table directly in SQL*Plus/dev/cl? Or even some other page in the app?
You're better off accepting there will be gaps. Use a sequence to generate the numbers and audit all changes to the table instead:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:317107500346751182 https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1508205334476