| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Reformatting GUIDs
Charles Hooper wrote:
> barthome1_at_comcast.net wrote:
> > Here is an older message from this group. The point is to reformat an
> > Oracle GUID into the SQLServer format.
> > *******************************
> > Troy,
> >
> > Built-in HEXTORAW function will handle the char to raw conversion:
> >
> > HEXTORAW(TRANSLATE(your_guid_string,'0{-}','0'))
> >
> > TRANSLATE is there to remove those curly braces and dashes from the
> > input string before converting it to raw. For converting a raw guid
> > back
> > to formatted string you will probably need to create your own
> > formatting
> > function that will insert dashes in proper positions. To get a
> > converted
> > string without dashes you use RAWTOHEX built-in function on raw guid
> > value. You can then insert dashes into it like this:
> >
> > return
> > substr(guid,1,8)||'-'||substr(guid,9,4)||'-'||substr(guid,13,4)......
> >
> > Hth,
> > Vladimir M. Zakharychev
> > N-Networks, makers of Dynamic PSP(tm)
> > ******************************
> >
> > I need to do the same thing inline within a SQL select statement. The
> > best I can figure to translate this is;
> > select
> > substr(RAWTOHEX(sys_guid()),1,8)||'-'||substr(RAWTOHEX(sys_guid()),9,4)||---ETC.
> >
> > The problem I see is that each call to sys_guid() returns another GUID,
> > so I am merging the results of several GUIDs. I doubt that will keep
> > my GUIDs unique (or will it)?
> >
> > Any suggestions how to get around this?
> >
> > Bart
>
> 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
SQL> / MSSQL_GUID
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
Received on Wed Dec 20 2006 - 14:07:16 CST
 |
 |