Tom,

Long time reader, first time poster...

Developing in Apex 3.2.1 (side note... thanks for sharing "ask the expert" with the Apex community... very helpful "how to" in various areas :-), Database Server 10g R2 (10.2.0.3), Windows for now, but Linux later.

Summary: I'm trying to write a DB trigger (Before Insert) that will generate a unique obfuscated ID so that I can expose the obfuscated ID in a public URL.

Specifically, I have a table (A) with a trigger populated PK (sequence or sys_guid() ). I want to email a URL allowing the display of the newly created row, but I can't send the PK since the next/previous PK value is easily guessable and would allow the bad guys to troll other peoples data. My idea is to have a DB trigger take the PK value and obfuscate it (but the result needs to be unique), store the obfuscated PK in a column (Hidden_in_plain_sight_ID), and use Hidden_in_plain_sight_ID in the public URL. Then my Apex page could act upon the one row uniquely identified by the Hidden_in_plain_sight_ID. I know this does not prevent a brute force attack, I just want to make it "smarter than a fifth grader" to break.

I've thought of sys_guid(), but that is sequential on some platforms (or is there a way to force "psuedo-random" ?), and I've thought of dbms_obfuscation_toolkit.md5( PK ), but I think I read that md5 is not guaranteed to be unique even thought each PK is unique;

I suspect I have just missed an obvious answer, but I hope I have described the goal clearly enough.

Thanks,
Russ

    select  to_number(
                 to_char(ate_seq.nextval) ||
                      lpad( substr( abs(ate_random.rand), 1, 5 ),5, '0' ) ||
                      ltrim(to_char(mod(abs(dbms_utility.get_time),1000000),'000000'))
                 )
    into    g_curr_val
    from   dual;