Skip to Main Content
  • Questions
  • Get sequencial numbers from table and locking

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Andreas.

Asked: September 06, 2021 - 5:29 am UTC

Last updated: September 07, 2021 - 7:16 am UTC

Version: 11.2.0.4.0 - 64bit

Viewed 1000+ times

You Asked

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

and Chris said...

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

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 06, 2021 - 2:35 pm UTC

Hello,
thank you for the super fast answer.
It's code I have inherited and I tried to replace it with better code hoping that there is a solution to the gap-free numbers problem.

Greetings
Andreas
Connor McDonald
September 07, 2021 - 7:14 am UTC

Given that your criteria for the SELECT and the UPDATE are slightly different, you run the risk of errors sneaking through I suspect.

Chuck Jolley, September 07, 2021 - 2:35 am UTC

Could it be a typo?
Is trim(sysdate) supposed to be trunc(sysdate)


PS it took me 10 years to convince our auditors gaps weren't a problem.
Connor McDonald
September 07, 2021 - 7:16 am UTC

PS it took me 10 years to convince our auditors gaps weren't a problem.

Probably a record for fastest time :-)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library