ℹ️ This is an interpretation of RAW(16) for the GUID on Oracle Database.
SELECT
RAWTOHEX(ID) ID_HEX,
ID ID_BINARY,
-- "UUID_v1" expected 3fa85f64-5717-4562-b3fc-2c963f66afa6
LOWER(
REGEXP_REPLACE(
ID,
'(.{8})(.{4})(.{4})(.{4})(.{12})',
'\1-\2-\3-\4-\5'
)
) "UUID_v1",
-- "UUID_v2" expected 645fa83f-1757-6245-b3fc-2c963f66afa6
LOWER(
REGEXP_REPLACE(
REGEXP_REPLACE(
ID,
'(.{8})(.{4})(.{4})(.{4})(.{12})',
'\1-\2-\3-\4-\5'
),
'(.{2})(.{2})(.{2})(.{2}).(.{2})(.{2}).(.{2})(.{2})(.{18})',
'\4\3\2\1-\6\5-\8\7\9'
)
) "UUID_v2",
-- "UUID_ordered" expected 57174562-5f64-3fa8-b3fc-2c963f66afa6
LOWER(
REGEXP_REPLACE(
REGEXP_REPLACE(
ID,
'(.{8})(.{4})(.{4})(.{4})(.{12})',
'\1-\2-\3-\4-\5'
),
--'(.{2})(.{2})(.{2})(.{2}).(.{2})(.{2}).(.{2})(.{2}).(.{2})(.{2}).(.{12})',
'(.{2})(.{2})(.{2})(.{2}).(.{2})(.{2}).(.{2})(.{2})(.{18})',
'\5\6\7\8-\3\4-\1\2\9'
)
) "UUID_ordered"
FROM (SELECT HEXTORAW('3FA85F6457174562B3FC2C963F66AFA6') ID FROM DUAL);%dw 2.0
output application/json
import toHex, toBase64, fromHex from dw::core::Binaries
// Constants
var cv1 = 'v1'
var cv2 = 'v2'
fun toGuid(raw: Binary | String) = do {
toGuid(raw, cv2)
}
fun toGuid(raw: Any, version: String | Null) = do {
var hex = raw match {
case is Binary -> (toHex(raw as Binary) as String)
case is String -> (raw as String) /** TODO: Maybe add validation of length */
else -> null
}
fun hexToUUIDv1(hex: String) = do {
hex replace /(.{8})(.{4})(.{4})(.{4})(.{12})/
with ($[1] ++ "-" ++ $[2] ++ "-" ++ $[3] ++ "-" ++ $[4] ++ "-" ++ $[5])
}
fun hexToUUIDv2(hex: String) = do {
hexToUUIDv1(hex)
replace /(.{2})(.{2})(.{2})(.{2}).(.{2})(.{2}).(.{2})(.{2})(.{18})/
with (
$[4] ++ $[3] ++ $[2] ++ $[1] ++
"-" ++ $[6] ++ $[5] ++
"-" ++ $[8] ++ $[7] ++ $[9]
)
}
---
(
if(hex != null ) (
version match {
case v: 'v1' -> hexToUUIDv1(hex)
case v: 'v2' -> hexToUUIDv2(hex)
else -> hexToUUIDv2(hex)
}
) else raw
) as String
}
fun toRaw16Hex(GUID: String) = do {
var PATTERN_HEX = /(.{32})/
//var PATTERN_UUIDv1 = /(.{8})(\-)(.{4})(\-)(.{4})(\-)(.{4})(\-)(.{12})/
//var PATTERN_UUIDv2 = /(.{8})(\-)(.{4})(\-)(.{4})(\-)(.{4})(\-)(.{12})/
var PATTERN_UUIDv1 = /(.{2})(.{2})(.{2})(.{2})(\-)(.{2})(.{2})(\-)(.{2})(.{2})(\-)(.{2})(.{2})(\-)(.{12})/
var PATTERN_UUIDv2 = /(.{2})(.{2})(.{2})(.{2})(\-)(.{2})(.{2})(\-)(.{2})(.{2})(\-)(.{2})(.{2})(\-)(.{12})/
fun UUIDv2ToRaw16Hex(UUIDv2: String) = do {
UUIDv2
replace PATTERN_UUIDv2
with (
$[4] ++ $[3] ++ $[2] ++ $[1] ++
$[7] ++ $[6] ++
$[10] ++ $[9] ++
$[12] ++ $[13] ++ $[15]
)
}
fun UUIDv1ToRaw16Hex(UUIDv1: String) = do {
UUIDv1 replace "-" with ""
}
---
(
GUID match {
case v if( v != null and (v matches (PATTERN_UUIDv1))) ->
UUIDv1ToRaw16Hex(v)
//case UUIDv2 if( UUIDv2 != null and ( UUIDv2 matches (PATTERN_UUIDv2) ) ) -> UUIDv2ToRaw16Hex(UUIDv2)
else -> GUID
}
)
}
// https://robobunny.com/cgi-bin/guid
// https://www.sohamkamani.com/uuid-versions-explained/
var hex = "3FA85F6457174562B3FC2C963F66AFA6"
var raw16 = fromHex("3FA85F6457174562B3FC2C963F66AFA6")
var UUIDv1 = upper('3fa85f64-5717-4562-b3fc-2c963f66afa6')
var UUIDv2 = upper('645fa83f-1757-6245-b3fc-2c963f66afa6')
var UUIDordered = upper('57174562-5f64-3fa8-b3fc-2c963f66afa6')
var UUIDv4 = null /** random */
var UUIDv5 = null /** ramdon with fixed */
var GUID = toGuid(raw16)
var GUIDv1 = toGuid(raw16, cv1)
var GUIDv2 = toGuid(raw16, cv2)
---
{
hex: hex,
raw16: raw16,
GUID: GUID,
GUIDv1: GUIDv1,
GUIDv2: GUIDv2,
isHex: (hex ~= upper(GUID)),
isUUIDv1: (UUIDv1 == GUID),
isUUIDv2: (UUIDv2 == GUID),
isUUIDOrdered: (UUIDordered == GUID),
isUUIDv4: (UUIDv4 ~= GUID),
isUUIDv5: (UUIDv5 ~= GUID),
fromUUIDv2ToRaw: toRaw16Hex(UUIDv2),
fromUUIDv1ToRaw: toRaw16Hex(UUIDv1),
isRaw: (toRaw16Hex(UUIDv2) == hex)
}
🏗️ Not Ready
/**
* This module contains DataWeave functions for data transformations based on Oracle Data Type RAW(16) (Binary).
*/
%dw 2.0
import toHex from dw::core::Binaries
/**
* %Replace with your function description%
*
*
* %Add additional information to your function description% (optional section)
*
* === Parameters (optional section)
*
* [%header, cols="1,1,3"]
* |===
* | Name | Type | Description
* | %`The parameter name`% | %`The parameter type`% | %The parameter description% (one row per param)
* |===
*
* === Example (optional section)
*
* %The example description% (optional)
*
* ==== Source (optional section)
*
* [source,%The language%,linenums] (optional)
* ----
* YOUR CODE
* ----
*
* ==== Input (optional section)
*
* The input description (optional)
*
* [source,%The language%,linenums] (optional)
* ----
* YOUR CODE
* ----
*
* ==== Output (optional section)
*
* %The output description% (optional)
*
* [source,%The language%,linenums] (optional)
* ----
* YOUR CODE
* ----
*/
fun toHex(raw: Binary | String) = do {
if(raw != null ) (
raw match {
case is Binary -> (toHex(raw))
case is String -> raw
else -> raw
}
) else null
}
/**
* %Replace with your function description%
*
*
* %Add additional information to your function description% (optional section)
*
* === Parameters (optional section)
*
* [%header, cols="1,1,3"]
* |===
* | Name | Type | Description
* | %`The parameter name`% | %`The parameter type`% | %The parameter description% (one row per param)
* |===
*
* === Example (optional section)
*
* %The example description% (optional)
*
* ==== Source (optional section)
*
* [source,%The language%,linenums] (optional)
* ----
* YOUR CODE
* ----
*
* ==== Input (optional section)
*
* The input description (optional)
*
* [source,%The language%,linenums] (optional)
* ----
* YOUR CODE
* ----
*
* ==== Output (optional section)
*
* %The output description% (optional)
*
* [source,%The language%,linenums] (optional)
* ----
* YOUR CODE
* ----
*/
fun toGuid(raw: Any) = do {
import toHex from dw::core::Binaries
var hex = raw match {
case is Binary -> (toHex(raw as Binary) as String)
case is String -> (raw as String) /** TODO: Maybe add validation of length */
else -> null
}
---
if(hex != null ) (
lower(
hex
replace /(.{8})(.{4})(.{4})(.{4})(.{12})/
with ($[1] ++ "-" ++ $[2] ++ "-" ++ $[3] ++ "-" ++ $[4] ++ "-" ++ $[5])
//replace /(.{2})(.{2})(.{2})(.{2}).(.{2})(.{2}).(.{2})(.{2})(.{18})/
//with ($[4] as String ++ $[3] as String ++ $[2] as String ++ $[1] as String ++ "-" ++ $[6] as String ++ $[5] as String ++ "-" ++ $[8] as String ++ $[7] as String ++ $[9] as String)
)
) else raw
}
Because JDBC has to interpret the type RAW(16), there are situations where is necessary to use Input Parameters on the operations of Database Connector - Mule 4 (anypoint.mulesoft.com/exchange) like Bulk Insert / Update / Delete, because of this it's important to use Parameter Types to indicate the type that JDBC has to use when the column has the data type RAW(16).