SQL & PL/SQL

Hi Friends ,

We are using oracle forms version as "Forms [32 Bit] Version 6.0.8.27.0 (Production) " and database version as "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production"

We have a system to create invoices,returns ,delivery etc as in normal ERP. We are generating automatic serial # for these documents.The last Serial of all these documents,we are saving in a table namely "INV_STORES_MASTER.This table has following structure :

So,whenever we create any invoice or delivery it takes the last no from these table and create it for next available serial.

For example : if invoice 104 is already present ,then it will create current invoice with 105.

We are using following select statement to get the serial,

-------------------------------------------------------------------------

Select nvl(last_invoice_no,0) into var_last_invoice_no  from  inv_stores_master

  where stores_code = :inv_hdr_invoice.stores_code for update of last_invoice_no;

-------------------------------------------------------------------------

After system get last_serial_no, it checks all the serial no after it. If the record is not existing in inv_hdr_invoice,then it will take it as serial for the invoice.

This process was working fine in oracle 11g database. Just recently 3 months back we migrated to Oracle 12c and it is creating database locks too often with the above query.

Can you give us any idea on how to avoid locking with sessions.

Thanks a lot