PL/SQL Generating a Unique Number

[Michael42]

Hello,

In a PL/SQL script on an Oracle 9i database I'd like to generate a unique number to be used for a PK field. I would be generating this at most once a second. The total rows would be less then 10,000. I cannot use sequences for this.

Can anyone share a simple function or technique to do this?

Thanks,

Michael42

 

[SimonSellick]

If it has to work within an SQL DML statement:

select nvl(max(col), 0) + 1 from tbl

Where tbl.col is the pk column.

Why not a sequence?

 

[carp]

I agree with SimonSellick - why not a sequence? Especially since it would be the most efficient way to get what you are after.
The alternative to Simon's approach would be to have a table full of unique numbers and you select the minimum number that hasn't been used yet. But Simon's approach is more efficient.
The problem with both approaches is that if you have multiple sessions trying to get unique numbers at the same time, all but one of them is going to wind up with a non-unique number.
A sequence is not only more efficient, but would guarantee uniqueness, regardless of how many concurrent programs are accessing it.

 

[Michael42]

SimonSellick,

Perfect - thanks for posting.

>> Why not a sequence?

The short answer is, for this environment, too much overhead. We have unit, test, staging and production systems. Because of the way the appliction is written, getting sequences to all be happy has many opportunities for disaster. As you, I generally I see the advantages of using sequences

carp,

I understand your point and it is well taken. For this environment multiple concurrent inserts is not a big issue.

-Michael42

 

[LKBrwnDBA]

You could try using either one of these:

SYS_GUID - generates and returns a globally unique identifier (RAW value) made up of 16 bytes

DBMS_UTILITY.GET_TIME - Returns time as the number of 100th's of a second from some arbitrary epoch.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb