PL/SQL Generate a unique identifier

Posted on 2009-05-12
Last Modified: 2013-12-19

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

Question by:HRMorton

Expert Comment

ID: 24363143
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


Author Comment

ID: 24363172

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.
LVL 73

Expert Comment

ID: 24363490
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 73

Assisted Solution

sdstuber earned 100 total points
ID: 24370350
need anything else?  I see you have open questions going back to the beginning of the year.  please close your old questions.

Author Comment

ID: 24372347

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

LVL 51

Accepted Solution

HainKurt earned 150 total points
ID: 24378750
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

LVL 73

Expert Comment

ID: 24567360
I recommend a split   24363490 and 24378750  

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Calculating percentages per course - Oracle Query 3 41
automatic email alert 1 41
Not able to add the URL for the   access control list - oracle plsql 10 49
oracle 11g 23 48
Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now