Solved

PL/SQL Generate a unique identifier

Posted on 2009-05-12
8
2,624 Views
Last Modified: 2013-12-19
Hi,

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.

Thanks

CURSOR c_get_cc_id( cp_per_id ci_acct_per.per_id%TYPE )

     IS

       SELECT TRIM(TO_CHAR(TO_NUMBER(SUBSTR(cp_per_id,1,7)) || 

                   SUBSTR(TO_CHAR(TO_NUMBER(TO_CHAR(sysdate,'MI')||TO_CHAR(sysdate,'SS'))),1,3),

            '0999999999'))

         FROM dual;

Open in new window

0
Comment
Question by:HRMorton
8 Comments
 
LVL 1

Expert Comment

by:sundar62
Comment Utility
You can create a sequence first and then retrieve it in your code.

http://www.techonthenet.com/oracle/sequences.php


CURSOR c_get_cc_id( cp_per_id ci_acct_per.per_id%TYPE )

     IS

         

         SELECT obj_id_sequence.nextval INTO l_job_id FROM DUAL;

Open in new window

0
 

Author Comment

by:HRMorton
Comment Utility
Hi,

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

Expert Comment

by:sdstuber
Comment Utility
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
0
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

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

Author Comment

by:HRMorton
Comment Utility
Hi,

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.

Thanks
Heather
DECLARE
 

    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

  IS

     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'

		AND NOT EXISTS ( SELECT NULL

                           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 )

     IS

       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 )

	 IS

	   SELECT NULL

	     FROM ci_cc

		WHERE cc_id = cp_cc_id;
 

BEGIN
 

  FOR rec IN c_get_sa

  LOOP
 

      l_insert := FALSE;

      WHILE l_insert = FALSE

      LOOP
 

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

	    THEN

	      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

	     THEN

	       l_insert := TRUE;

             END IF;

	     CLOSE c_check_cc_id;

      END LOOP;

Open in new window

0
 
LVL 51

Accepted Solution

by:
HainKurt earned 150 total points
Comment Utility
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...



SELECT *

  FROM (SELECT   *

            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)

 WHERE ROWNUM = 1

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
I recommend a split   24363490 and 24378750  
0

Featured Post

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.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

771 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

10 Experts available now in Live!

Get 1:1 Help Now