Oracle stored procedure to generate sequence numbers?

Question

I have a employee, product and product odometer tables which looks like below

enter image description here

Each employee has a unique emp_code depending on the product he has access to.

Each product will have a unique prefix to its identifier.

I need to generate a unique alphanumeric identifiers per product and assign it to the employee.

This product identifier is going to be 9 characters long including the prefix.

Odometer table will store the last emp_code assigned to employees in that particular product.

How do I write a stored procedure to generate these alphanumeric emp_codes based on product for each new employee being added in the system?

Please help.

EDIT 1:

Just a small correction in the odometer table, we may not need to store the odometer as A00000001. Instead we could only store 00000001 and then append the prefix.

EDIT 2:

This is what I have do so far.

create or replace PROCEDURE SP_GEN_NEXT_DUMMY_DB_PRISM_ID
(
  in_product_id number,
  db_prism_id out varchar2
)
AS
BEGIN

  UPDATE BI_DB_PRISM_ID_ODOMETER
  SET DB_PRISM_ID =  DB_PRISM_ID + 1
  WHERE 
    PRODUCT_ID = in_product_id; 

  SELECT to_char(db_prism_id, 'FM00000000') into db_prism_id FROM     BI_DB_PRISM_ID_ODOMETER WHERE PRODUCT_ID = in_product_id; 

END;

But how can I make sure that it runs in a transaction and also how do I append the product prefix to the number generated.

@zerkms.Yes. They need to be consecutive. It should not be some random number. — ajm
– ajm, Commented May 18, 2012 at 4:35
@zerkms. It might happen that we generate a sequential number but its never assigned to the employee in that case it is lost which is ok with us. — ajm
– ajm, Commented May 18, 2012 at 4:39
doesn't one shared sequence prepended with product code satisfy your needs? — zerkms
– zerkms, Commented May 18, 2012 at 5:02
@zerkms. No, we will need numbers in sequence for each product. — ajm
– ajm, Commented May 18, 2012 at 5:09

Jeffrey Kemp · Accepted Answer · 2012-05-18 06:58:00Z

3

You have two options that I can think of:

Create an Oracle sequence for each product, perhaps with some kind of maintenance procedure that would detect new products and create the sequences dynamically.
Roll your own sequence code. Store the last value in the Product table. Write a procedure that generates a new value for a given product. The procedure would first obtain a row lock on the product table (so that you don't get two sessions getting the same value at the same time), advance the value, and write the new value to the Product table. You would need this procedure to be an autonomous transaction (otherwise other sessions would just wait if a session doesn't commit or rollback straight away).

An advantage of #1 is that it's the fastest.

A disadvantage of #1 is you're having to run dynamic DDL, and you'd have to make sure the DDL for a product has been run before trying to insert odometer records for it. You might also need to think about whether to drop a sequence if a product is deleted.

Another disadvantage of #1 is that you can only get sequence values with dynamic SQL (you'd have to determine the name of the sequence at runtime).

A disadvantage of #2 is that it's easy to get it wrong for a system that allows concurrent DML. You'd want to make sure you get the logic right, and test it under high concurrent load. Also, #2 will perform worse than #1 because it serializes access for each product.

Edit

"But how can I make sure that it runs in a transaction and also how do I append the product prefix to the number generated."

Transactions are automatic in Oracle. In this case, however, what you want is an autonomous transaction.

Appending the product prefix is done using the string concatenation function - e.g. ||

edited May 18, 2012 at 6:58

answered May 18, 2012 at 6:40

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ajm Over a year ago

Please see I have updated my answer with what I have tried so far.

Jeffrey Kemp Over a year ago

Updated answer. By the way, it's not clear from your code which table you're referring to - you seem to be incrementing the ID on the odometer table, which doesn't make sense to me?

David Aldridge · Accepted Answer · 2012-05-18 08:00:36Z

1

Seems to be that you're denormalising the data model here.

The odometer table is 1:1 with the product table, and the odometer value also depends on the number of employees currently assigned with that product.

The emp_code sequence number can be derived from the employee id and the product table. Aerguably the identifier prefix can be inferred from the product id also, so in fact you have redundant columns and a redundant table in your schema.

Are you sure that you really need them?

answered May 18, 2012 at 8:00

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

Comments

archimede · Accepted Answer · 2012-05-18 08:03:01Z

0

Perhaps I'm missing something here, if so accept my apologies.

It seems to me that the odometer is merely a counter of how many Employees have access to each product.

If that is the case, why not get rid of the table (and the EMP_CODE column) altogether and "build" that info in one or more views?

Alessandro

PS: looks like I posted the same suggestion of David Aldridge. Sorry for the confusion.

answered May 18, 2012 at 8:03

archimede

2561 silver badge2 bronze badges

Collectives™ on Stack Overflow

Oracle stored procedure to generate sequence numbers?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related