PL/SQL Generate a unique identifier

Posted on 2009-05-12
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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 74

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

LVL 74

Assisted Solution

sdstuber earned 400 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 58

Accepted Solution

HainKurt earned 600 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 74

Expert Comment

ID: 24567360
I recommend a split   24363490 and 24378750  

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

762 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