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