Skip to content

Instantly share code, notes, and snippets.

@sandcastle

sandcastle/oracle_guid_helpers.sql

Last active December 9, 2024 13:48
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save sandcastle/03d4f7be338b7d28e7e7 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save sandcastle/03d4f7be338b7d28e7e7 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. sandcastle revised this gist Jan 14, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion oracle_guid_helpers.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -54,7 +54,7 @@ end;

    create or replace function guid_to_raw ( guid in varchar2 ) return raw
    is
    hex varchar2(32);
    hex varchar2(32);
    begin

    hex := substr(guid, 7, 2)
  2. sandcastle revised this gist Jan 14, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion oracle_guid_helpers.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -2,7 +2,7 @@ set serveroutput on;

    declare
    raw_guid raw(16);
    guid varchar2(128);
    guid varchar2(64);
    begin

    raw_guid := guid_to_raw ('88c6a267-65d2-48d6-8da2-6f45e2c22726');
  3. sandcastle created this gist Jan 14, 2015.
    79 changes: 79 additions & 0 deletions oracle_guid_helpers.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,79 @@
    set serveroutput on;

    declare
    raw_guid raw(16);
    guid varchar2(128);
    begin

    raw_guid := guid_to_raw ('88c6a267-65d2-48d6-8da2-6f45e2c22726');
    guid := raw_to_guid('67A2C688D265D6488DA26F45E2C22726');

    dbms_output.put_line('RAW');
    dbms_output.put_line(raw_guid);
    dbms_output.put_line('GUID');
    dbms_output.put_line(guid);

    /*
    Expected output:
    RAW
    67A2C688D265D6488DA26F45E2C22726
    GUID
    88C6A267-65D2-48D6-8DA2-6F45E2C22726
    */

    end;
    /

    create or replace function raw_to_guid( raw_guid in raw ) return varchar2
    is
    hex varchar2(32);
    begin

    hex := rawtohex(raw_guid);

    return substr(hex, 7, 2)
    || substr(hex, 5, 2)
    || substr(hex, 3, 2)
    || substr(hex, 1, 2)
    || '-'
    || substr(hex, 11, 2)
    || substr(hex, 9, 2)
    || '-'
    || substr(hex, 15, 2)
    || substr(hex, 13, 2)
    || '-'
    || substr(hex, 17, 4)
    || '-'
    || substr(hex, 21, 12);

    end;
    /

    create or replace function guid_to_raw ( guid in varchar2 ) return raw
    is
    hex varchar2(32);
    begin

    hex := substr(guid, 7, 2)
    || substr(guid, 5, 2)
    || substr(guid, 3, 2)
    || substr(guid, 1, 2)
    --
    || substr(guid, 12, 2)
    || substr(guid, 10, 2)
    --
    || substr(guid, 17, 2)
    || substr(guid, 15, 2)
    --
    || substr(guid, 20, 2)
    || substr(guid, 22, 2)
    --
    || substr(guid, 25, 12);

    return hextoraw(hex);

    end;
    /