This discussion is archived
3 Replies Latest reply: Feb 6, 2013 3:26 AM by BluShadow RSS

Procedure to generating an ID

frm40735 Journeyer
Currently Being Moderated
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0
create table INV
(inv_id VARCHAR2(26) not null);

INSERT INTO inv
     (inv_id)
VALUES
     ('ABC');
    
INSERT INTO inv
     (inv_id)
VALUES
     ('ABC.');    
  
INSERT INTO inv
     (inv_id)
VALUES
     ('ABC..');
  
select * from inv
What I want to do is create a procedure with an INPUT of a inv_id value and loop thru the records to generate a unique ID

for example the above if I input ABC I want to loop and check to see if there is already a record there

so it should loop and if found add a '.' to the end of 'ABC' and keep looping until it had 3 '...' at the end, hence it being unique.

What I want to output is an Inv_id of 'ABC...'

apologies if it is not clear. my logic head is out the window today.

thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points