CODEWITHSUNDEEP
oracle-databasestored-procedures
ajm
ajm

Reputation: 13213

Oracle stored procedure to generate sequence numbers?

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.

Upvotes: 1

Views: 3486

Answers (3)

archimede
archimede

Reputation: 256

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.

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52346

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?

Upvotes: 1

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

You have two options that I can think of:

  1. Create an Oracle sequence for each product, perhaps with some kind of maintenance procedure that would detect new products and create the sequences dynamically.

  2. 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. ||

Upvotes: 3

Related Questions