Re: Reformatting GUIDs

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 20 Dec 2006 13:28:19 -0800
Message-ID: <1166650099.184375.131780@f1g2000cwa.googlegroups.com>

Vladimir M. Zakharychev wrote:
> Charles Hooper wrote:
> > I wonder if you can look at this from a different direction. What if
> > you use the random number generating capabilities to generate each of
> > the hex numbers between the dashes?
> >
> > For example, the second section of the GUID contains a four character
> > hex number. If you have Oracle provide a hex number between hex 1000
> > and hex FFFF, that would be sufficient to satisfy that portion of the
> > GUID. If you pre-convert the hex numbers to their decimal equivalents,
> > you will save Oracle a little work, but it will be more difficult to
> > see what is happening. For example:
> > SELECT
> >
> > TRIM(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(TO_NUMBER('1000','XXXX')+1,TO_NUMBER('FFFF','XXXX')+1)),'XXXX'))
> > P2
> > FROM
> > DUAL;
> >
> > P2
> > ==
> > 8FC4
> >
> > 1 ROW SELECTED
> >
> > Extending this approach by sliding the above into an inline view:
> > SELECT
> > P1||'-'||P2 GUID,
> > P1||'-'||P2 GUID_AGAIN
> > FROM
> > (SELECT
> >
> > TRIM(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(TO_NUMBER('10000000','XXXXXXXX')+1,TO_NUMBER('FFFFFFFF','XXXXXXXX')+1)),'XXXXXXXX'))
> > P1,
> >
> > TRIM(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(TO_NUMBER('1000','XXXX')+1,TO_NUMBER('FFFF','XXXX')+1)),'XXXX'))
> > P2
> > FROM
> > DUAL);
> >
> > GUID GUID_AGAIN
> > ==============
> > 16C89EF9-734C 16C89EF9-734C
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.

>

> The only problem with this approach as I see it is that use of
> DBMS_RANDOM will probably have adverse effect on "GU" part of the
> "GUID" (GU = Globally Unique.) SYS_GUID() guarantees that all returned
> values are globally unique, because afaik it uses system timestamp and
> unique host information for GUID generation, among other things.
> DBMS_RANDOM doesn't guarantee uniqueness of returned values, only
> randomness.

>

> On 10g, one solution that doesn't involve coding your own function is
> this:

>

> SQL> SELECT REGEXP_REPLACE(
> 2 SYS_GUID(),
> 3 '(.{8})(.{4})(.{4})(.{4})(.{12})',
> 4 '{\1-\2-\3-\4-\5}') MSSQL_GUID
> 5 FROM DUAL
> 6 /

>

> MSSQL_GUID
> --------------------------------------------------------------------------------
> {250FA7A8-E8A4-4F30-E044-0800209B083C}

>

> SQL> /

>

> MSSQL_GUID
> --------------------------------------------------------------------------------
> {250FA7A8-E8A5-4F30-E044-0800209B083C}

>

> On pre-10g releases you can only do it with your own function as RE
> support is only available since 10.1.

>


> Hth,

>     Vladimir M. Zakharychev

>     N-Networks, makers of Dynamic PSP(tm)

>     http://www.dynamicpsp.com


I am not disagreeing with you, but I believe that the random number
generator in Oracle can be seeded with the system time.  The reason
that a GUID is thought to be unique is due to the range of possible

values, essentially 15 * 16^31 (or if the first digit can also be a 0, 16^32, or 16 * 16^31).

Note that there is nothing stopping the OP from calling sys_guid() five times, each time extracting a different sub-string portion of the value returned. This would allow the OP to retrieve a formatted GUID in a single SQL statement without a PL/SQL function.

Very nice example with the REGEXP_REPLACE function, so far your response is the most efficient, and likely the best solution. Great to see more than one way of solving problems.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Dec 20 2006 - 15:28:19 CST