PL/SQL Generate a unique identifier


I'm trying to generate a unique identifier to user as the primary key for an insert into a table.
One of the columns in the table is a person_id, and I need to use the first 7 numbers from this field to make up the unique id. (the last 3 numbers are the unique part).

I tried stripping the first 7 numbers and adding on 3 from the sysdate minues and seconds, then checking if the number existed if not continuing to loop. This worked but was very slow.
Only doing 2,000 inserts in an hour.

Code is included below.  I would appreciate any suggestions as to a quicker/more efficent way of doing this.


CURSOR c_get_cc_id( cp_per_id ci_acct_per.per_id%TYPE )
       SELECT TRIM(TO_CHAR(TO_NUMBER(SUBSTR(cp_per_id,1,7)) || 
         FROM dual;

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can create a sequence first and then retrieve it in your code.

CURSOR c_get_cc_id( cp_per_id ci_acct_per.per_id%TYPE )
         SELECT obj_id_sequence.nextval INTO l_job_id FROM DUAL;

Open in new window

HRMortonAuthor Commented:

Sorry, sequence won't work as I need the first 7 characters from the person id, and this changes for every record, so I would have 2,000+ different starting characters.
Also there are existing records in the table for the person id so I can't even just hard-code a specific number that would accommodate all the records.
why not just append the sequence to the end of the 7 characters?

SELECT TO_CHAR(TO_NUMBER(SUBSTR(cp_per_id,1,7)) || to_charyour_sequence.nextval) from dual
using sysdate is slow because if you have a collision, you'll keep colliding in a loop until the next second ticks.  So every insert with a problem takes a minimum of 1 second.
but using a sequence helps with that.

you also use milliseconds from systimestamp

or use dbms_random to generate a random number, I'd go with the sequence or milliseconds though.  time both and see which works best
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

need anything else?  I see you have open questions going back to the beginning of the year.  please close your old questions.
HRMortonAuthor Commented:

Thanks, have cleaned up my open questions.
I decided to use the random number generator, seemed to work slightly better.

The delay appears to be in a second cursor that I use to check if the 'random' number exists in the table, looping until it doesn't. Is there any way to combine the cusor to select the random number with a check that it doesn't already exist ? Would this be done with a %NOTFOUND and re-select ?

It's the loop with cursor c_check_cc_id that seems to be causing me the grief.

Have attached the full script in case it helps.

    l_cc_id cisadm.ci_cc.cc_id%TYPE;
    l_insert                   BOOLEAN := FALSE;
    l_check                    ci_cc.cc_id%TYPE;
    err_code                    VARCHAR2(100);
    err_msg                     VARCHAR2(200);
  CURSOR c_get_sa
     SELECT DISTINCT adj.sa_id sa_id, aper.per_id per_id
       FROM cm_adjustments adj,
            ci_acct_per aper,
	        ci_sa sa
      WHERE adj.sa_id = sa.sa_id
        AND sa.acct_id = aper.acct_id
        AND aper.main_cust_sw = 'Y'
                           FROM ci_cc cc 
                          WHERE cc.cc_cl_cd = 'ACCT'
                            AND cc.cc_type_cd = 'BAL ENQ'
							AND cc.per_id = aper.per_id
                            AND TRUNC(cc.cc_dttm) >= TO_DATE('08-MAY-2009','DD-MON-RRRR')
                            AND cc.user_id = 'CDX');
     CURSOR c_get_cc_id( cp_per_id ci_acct_per.per_id%TYPE )
       SELECT SUBSTR(cp_per_id,1,7) || round(dbms_random.value(100,999))
         FROM dual;
     CURSOR c_check_cc_id( cp_cc_id ci_cc.cc_id%TYPE )
	     FROM ci_cc
		WHERE cc_id = cp_cc_id;
  FOR rec IN c_get_sa
      l_insert := FALSE;
      WHILE l_insert = FALSE
  	    -- Retrieve the cc_id based on per_id, if not found return '999999999'
	     OPEN c_get_cc_id( cp_per_id => rec.per_id);
	    FETCH c_get_cc_id
	     INTO l_cc_id;
	    IF c_get_cc_id%NOTFOUND
	      l_cc_id := '99999999999';
	    END IF;
	    CLOSE c_get_cc_id;
		/** Before returning the adj_id check it is not already being used in ci_adj table **/
	      OPEN c_check_cc_id( cp_cc_id => l_cc_id);
	     FETCH c_check_cc_id
	      INTO l_check;
	     IF c_check_cc_id%NOTFOUND
	       l_insert := TRUE;
             END IF;
	     CLOSE c_check_cc_id;
      END LOOP;

Open in new window

HainKurtSr. System AnalystCommented:
you can try to get all unused numbers in one shot instead of looping...

i tried this...

create table numbers as (select rownum n from lit_page_items where rownum<1000)

create a table based on a table which has more than 1000 records... it will contain one column n, 999 records vaues 1..999. we will use this table in the query...

and use the folllowing script... modify it a bit to match your varieble and table... it returns you one record (newid, rn, person_id). use the newid column for your new record...

            FROM (SELECT newid, rn, person_id
                    FROM (SELECT DBMS_RANDOM.VALUE AS rn,
                                    SUBSTR (current_person_id, 1, 7) || LPAD (n, 3, '0') AS newid
                            FROM numbers) x,
                         person_table p
                   WHERE x.newid = p.person_id(+))
           WHERE person_id IS NULL
        ORDER BY rn)

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I recommend a split   24363490 and 24378750  
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.