+ Reply to Thread
Results 1 to 5 of 5

GUID to CHAR conversion function?

  1. GUID to CHAR conversion function?

    Hi All,

    I am an Oracle newbie and could use a little help, please. We are
    porting an app to Oracle that already runs with Ingres and MSSQL. Our
    UUID (GUID) columns are stored as RAW(16). I would like to write a
    function that will convert the 36-char (or 38-char) GUID string to
    RAW(16) and a function to convert the RAW(16) to the 36-char string.

    When converting from CHAR to RAW(16), the input string would look like
    this: '00000000-0000-0000-0000-00000000000' or
    '{00000000-0000-0000-0000-00000000000} '.

    When converting from RAW(16) to CHAR, the output string would always
    look like '00000000-0000-0000-0000-00000000000'.

    Does anyone out there have such a function or can point me to one. I
    haven't found mention of anything like this in the Oracle doc.

    Thanks very much
    Troy Rudolph


  2. Re: GUID to CHAR conversion function?

    Troy, take a look at the $ORACLE_HOME/rdbms/admin/utlraw.sql script
    which creates the utl_raw package. The package contains procedures to
    convert varchar2 to raw and raw to varchar2. You would need to write
    you own function to handle the formatting of individual areas of your
    key.

    Oracle has a function named sys_guid that is intended to generate
    global unique identifiers that you may want to look at. See SQL
    manual.

    HTH -- Mark D Powell --


  3. Re: GUID to CHAR conversion function?

    Troy, take a look at the $ORACLE_HOME/rdbms/admin/utlraw.sql script
    which creates the utl_raw package. The package contains procedures to
    convert varchar2 to raw and raw to varchar2. You would need to write
    you own function to handle the formatting of individual areas of your
    key.

    Oracle has a function named sys_guid that is intended to generate
    global unique identifiers that you may want to look at. See SQL
    manual.

    HTH -- Mark D Powell --


  4. Re: GUID to CHAR conversion function?

    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)

    wrote in message
    news:1142628614.440513.116480@j52g2000cwj.googlegroups.com...
    > Hi All,
    >
    > I am an Oracle newbie and could use a little help, please. We are
    > porting an app to Oracle that already runs with Ingres and MSSQL. Our
    > UUID (GUID) columns are stored as RAW(16). I would like to write a
    > function that will convert the 36-char (or 38-char) GUID string to
    > RAW(16) and a function to convert the RAW(16) to the 36-char string.
    >
    > When converting from CHAR to RAW(16), the input string would look like
    > this: '00000000-0000-0000-0000-00000000000' or
    > '{00000000-0000-0000-0000-00000000000} '.
    >
    > When converting from RAW(16) to CHAR, the output string would always
    > look like '00000000-0000-0000-0000-00000000000'.
    >
    > Does anyone out there have such a function or can point me to one. I
    > haven't found mention of anything like this in the Oracle doc.
    >
    > Thanks very much
    > Troy Rudolph
    >




  5. Re: GUID to CHAR conversion function?

    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)

    wrote in message
    news:1142628614.440513.116480@j52g2000cwj.googlegroups.com...
    > Hi All,
    >
    > I am an Oracle newbie and could use a little help, please. We are
    > porting an app to Oracle that already runs with Ingres and MSSQL. Our
    > UUID (GUID) columns are stored as RAW(16). I would like to write a
    > function that will convert the 36-char (or 38-char) GUID string to
    > RAW(16) and a function to convert the RAW(16) to the 36-char string.
    >
    > When converting from CHAR to RAW(16), the input string would look like
    > this: '00000000-0000-0000-0000-00000000000' or
    > '{00000000-0000-0000-0000-00000000000} '.
    >
    > When converting from RAW(16) to CHAR, the output string would always
    > look like '00000000-0000-0000-0000-00000000000'.
    >
    > Does anyone out there have such a function or can point me to one. I
    > haven't found mention of anything like this in the Oracle doc.
    >
    > Thanks very much
    > Troy Rudolph
    >




+ Reply to Thread