UTL_RAW functions for processing RAW values - PolarDB

The built-in UTL_RAW package provides functions for manipulating RAW binary data. Standard SQL functions do not operate on RAW values, and PL/SQL does not allow overloading between RAW and CHAR types. UTL_RAW fills this gap with dedicated subprograms for bitwise operations, type conversion, character set conversion, and byte sequence manipulation.

Subprograms

Subprogram	Description
BIT_AND	Performs a bitwise AND on two RAW values and returns the result as RAW.
BIT_COMPLEMENT	Performs a bitwise two's complement on a RAW value and returns the result as RAW.
BIT_OR	Performs a bitwise OR on two RAW values and returns the result as RAW.
BIT_XOR	Performs a bitwise XOR on two RAW values and returns the result as RAW.
CAST_TO_RAW	Converts a VARCHAR2 value to a RAW value.
CAST_TO_VARCHAR2	Converts a RAW value to a VARCHAR2 value.
COMPARE	Compares two RAW values and returns the position of the first differing byte.
CONCAT	Concatenates up to 12 RAW values into a single RAW value.
CONVERT	Converts a RAW value from one character set to another.
COPIES	Returns a RAW value composed of n concatenated copies of the input.
LENGTH	Returns the length of a RAW value in bytes.
OVERLAY	Overlays a range of bytes in a target RAW value with bytes from another RAW value.
REVERSE	Reverses the byte sequence of a RAW value.
SUBSTR	Returns a byte subsequence from a RAW value, starting at a given position.
TRANSLATE	Translates bytes in a RAW value using a from/to byte mapping.
TRANSLITERATE	Converts bytes in a RAW value using a from/to byte mapping, with optional padding.
XRANGE	Returns a contiguous sequence of single-byte RAW values between two boundary bytes.

BIT_AND function

Performs a bitwise AND on RAW values r1 and r2 and returns the result as RAW.

Syntax

UTL_RAW.BIT_AND (
   r1 IN RAW,
   r2 IN RAW)
RETURN RAW;

Parameters

Parameter	Description
`r1`	The first RAW operand.
`r2`	The second RAW operand.

Return values

Return value	Description
`RAW`	The result of the bitwise AND operation.

Example

SELECT utl_raw.bit_and('1234567890aabbccddeeff','1234567890') FROM dual;
                    bit_and
------------------------------------------------
 \x31323334353637383930616162626363646465656666
(1 row)

BIT_COMPLEMENT function

Performs a bitwise two's complement on r and returns the result as RAW.

Syntax

UTL_RAW.BIT_COMPLEMENT (
   r IN RAW)
RETURN RAW;

Parameters

Parameter	Description
`r`	The RAW value to complement.

Return values

Return value	Description
`RAW`	The result of the two's complement operation.

Example

SELECT utl_raw.bit_complement('1234567890') FROM dual;
     bit_complement
------------------------
 \xcecdcccbcac9c8c7c6cf
(1 row)

BIT_OR function

Performs a bitwise OR on RAW values r1 and r2 and returns the result as RAW.

Syntax

UTL_RAW.BIT_OR (
   r1 IN RAW,
   r2 IN RAW)
RETURN RAW;

Parameters

Parameter	Description
`r1`	The first RAW operand.
`r2`	The second RAW operand.

Return values

Return value	Description
`RAW`	The result of the bitwise OR operation.

Example

SELECT utl_raw.bit_or('\x1234567890','\x1234567890') FROM dual;
    bit_or
--------------
 \x1234567890
(1 row)

BIT_XOR function

Performs a bitwise XOR on RAW values r1 and r2 and returns the result as RAW.

Syntax

UTL_RAW.BIT_XOR (
   r1 IN RAW,
   r2 IN RAW)
RETURN RAW;

Parameters

Parameter	Description
`r1`	The first RAW operand.
`r2`	The second RAW operand.

Return values

Return value	Description
`RAW`	The result of the bitwise XOR operation.

Example

SELECT utl_raw.bit_xor('\x1234567890','\x123456') FROM dual;
   bit_xor
--------------
 \x0000007890
(1 row)

CAST_TO_RAW function

Converts a VARCHAR2 value to a RAW value containing the same binary data.

Syntax

UTL_RAW.CAST_TO_RAW (
   c IN VARCHAR2)
RETURN RAW;

Parameters

Parameter	Description
`c`	The VARCHAR2 value to convert.

Return values

Return value	Description
`RAW`	Contains binary data of the same type as the input VARCHAR2 value.

Example

SELECT utl_raw.cast_to_raw('ABCDEFGHIJKLMNOPQRSTUVWXYZ') FROM dual;
                      cast_to_raw
--------------------------------------------------------
 \x4142434445464748494a4b4c4d4e4f505152535455565758595a
(1 row)

CAST_TO_VARCHAR2 function

Converts a RAW value to a VARCHAR2 value containing the same binary data.

Syntax

UTL_RAW.CAST_TO_VARCHAR2 (
   r IN RAW)
RETURN VARCHAR2;

Parameters

Parameter	Description
`r`	The RAW value to convert.

Return values

Return value	Description
`VARCHAR2`	Contains binary data of the same type as the input RAW value.

Example

SELECT utl_raw.cast_to_varchar2(utl_raw.cast_to_raw('ABCDEFGHIJKLMNOPQRSTUVWXYZ')) FROM dual;
      cast_to_varchar2
----------------------------
 ABCDEFGHIJKLMNOPQRSTUVWXYZ
(1 row)

COMPARE function

Compares RAW values r1 and r2. If they differ in length, the shorter value is right-padded using pad before comparison.

Syntax

UTL_RAW.COMPARE (
   r1  IN RAW,
   r2  IN RAW,
   pad IN RAW DEFAULT NULL)
RETURN NUMBER;

Parameters

Parameter	Description
`r1`	The first RAW value to compare. Can be NULL or an empty byte sequence.
`r2`	The second RAW value to compare. Can be NULL or an empty byte sequence.
`pad`	(Optional) The byte used to right-pad the shorter value before comparison. Defaults to `x'00'`.

Return values

Return value	Description
`NUMBER`	If `r1` and `r2` are equal, or both are NULL or empty, the value `0` is returned. Otherwise, the position of the first byte where `r1` and `r2` differ is returned.

Example

SELECT utl_raw.compare(NULL, '0102', '01') FROM dual;
 compare
---------
       2
(1 row)

CONCAT function

Concatenates up to 12 RAW values into a single RAW value.

Syntax

UTL_RAW.CONCAT (
   r1  IN RAW DEFAULT NULL,
   r2  IN RAW DEFAULT NULL,
   r3  IN RAW DEFAULT NULL,
   r4  IN RAW DEFAULT NULL,
   r5  IN RAW DEFAULT NULL,
   r6  IN RAW DEFAULT NULL,
   r7  IN RAW DEFAULT NULL,
   r8  IN RAW DEFAULT NULL,
   r9  IN RAW DEFAULT NULL,
   r10 IN RAW DEFAULT NULL,
   r11 IN RAW DEFAULT NULL,
   r12 IN RAW DEFAULT NULL)
RETURN RAW;

Parameters

Parameter	Description
`r1`, `r2`, ..., `r12`	The RAW values to concatenate, in order. All parameters are optional.

Return values

Return value	Description
`RAW`	The concatenated result of all input values.

Example

SELECT utl_raw.concat('\xff', '\x00120349', '') FROM dual;
    concat
--------------
 \xff00120349
(1 row)

CONVERT function

Converts a RAW value from the character set specified by from_charset to the character set specified by to_charset.

Syntax

UTL_RAW.CONVERT (
   r            IN RAW,
   to_charset   IN VARCHAR2,
   from_charset IN VARCHAR2)
RETURN RAW;

Parameters

Parameter	Description
`r`	The RAW value to convert.
`to_charset`	The target character set name.
`from_charset`	The current character set of `r`.

Return values

Return value	Description
`RAW`	The input value re-encoded in the target character set.

Example

SELECT utl_raw.convert('31323334353637383930', 'UTF8', 'WIN1258');
                  convert
--------------------------------------------
 \x3331333233333334333533363337333833393330
(1 row)

COPIES function

Returns a RAW value formed by concatenating n copies of r.

Syntax

UTL_RAW.COPIES (
   r IN RAW,
   n IN NUMBER)
RETURN RAW;

Parameters

Parameter	Description
`r`	The RAW value to copy.
`n`	The number of times to copy `r`. Must be a positive integer.

Return values

Return value	Description
`RAW`	The result of concatenating `n` copies of `r`.

Example

SELECT utl_raw.copies('10203040', 5) FROM dual;
                                       copies
------------------------------------------------------------------------------------
 \x31303230333034303130323033303430313032303330343031303230333034303130323033303430
(1 row)

LENGTH function

Returns the length of a RAW value in bytes.

Syntax

UTL_RAW.LENGTH (
   r IN RAW)
RETURN NUMBER;

Parameters

Parameter	Description
`r`	The RAW value whose byte length to calculate.

Return values

Return value	Description
`NUMBER`	The length of `r` in bytes.

Example

SELECT utl_raw.length('\x31323334353637383930') FROM dual;
 length
--------
     10
(1 row)

OVERLAY function

Overlays a range of bytes in target with bytes from overlay_str, starting at position pos for len bytes. If overlay_str is shorter than len, or if pos is beyond the end of target, the remaining positions are filled with pad.

Syntax

UTL_RAW.OVERLAY (
   overlay_str IN RAW,
   target      IN RAW,
   pos         IN BINARY_INTEGER DEFAULT 1,
   len         IN BINARY_INTEGER DEFAULT NULL,
   pad         IN RAW            DEFAULT NULL)
RETURN RAW;

Parameters

Parameter	Description
`overlay_str`	The bytes to write into the target.
`target`	The byte sequence to be overlaid.
`pos`	The 1-based start position in `target` where overlaying begins. Defaults to `1`.
`len`	The number of bytes to overlay.
`pad`	The byte used for padding when `len` exceeds the length of `overlay_str`, or when `pos` is beyond the end of `target`.

Return values

Return value	Description
`RAW`	The result after applying the overlay.

Example

SELECT utl_raw.overlay('\xaabbcc', '\x102030', 1, 5, '\x88') FROM dual;
   overlay
--------------
 \xaabbcc8888
(1 row)

REVERSE function

Reverses the byte sequence of a RAW value.

Syntax

UTL_RAW.REVERSE (
   r IN RAW)
RETURN RAW;

Parameters

Parameter	Description
`r`	The RAW value to reverse.

Return values

Return value	Description
`RAW`	The byte-reversed result.

Example

SELECT utl_raw.reverse('123456789012345678901234567890') FROM dual;
                            reverse
----------------------------------------------------------------
 \x303938373635343332313039383736353433323130393837363534333231
(1 row)

SUBSTR function

Returns a byte subsequence from r, starting at position pos for len bytes.

Syntax

UTL_RAW.SUBSTR (
   r   IN RAW,
   pos IN INTEGER,
   len IN INTEGER DEFAULT NULL)
RETURN RAW;

Parameters

Parameter	Description
`r`	The RAW value to extract from.
`pos`	The start position for extraction.
`len`	(Optional) The number of bytes to return. If omitted, all bytes from `pos` to the end of `r` are returned.

Return values

Return value	Description
`RAW`	The extracted byte subsequence.

Example

SELECT utl_raw.substr('\x1234567890', 1, 2) FROM dual;
 substr
--------
 \x1234
(1 row)

TRANSLATE function

Translates bytes in a RAW value based on the sequences of the RAW type specified by the from_set and to_set parameters.

Syntax

UTL_RAW.TRANSLATE (
   r        IN RAW,
   from_set IN RAW,
   to_set   IN RAW)
RETURN RAW;

Parameters

Parameter	Description
`r`	The RAW byte sequence to translate.
`from_set`	The byte value to translate. If the value is less than or equal to the value of `r`, data is returned for this parameter.
`to_set`	The byte value generated after translating `from_set`.

Return values

Return value	Description
`RAW`	The translated byte sequence.

Example

SELECT utl_raw.translate('01020304050607080901020809', '01020304', '1112') FROM dual;
                     translate
----------------------------------------------------
 \x313131323131313531363137313831393131313231383139
(1 row)

TRANSLITERATE function

Converts bytes in a RAW value based on the sequences of the RAW type specified by the from_set and to_set parameters.

Syntax

UTL_RAW.TRANSLITERATE (
   r        IN RAW,
   to_set   IN RAW DEFAULT NULL,
   from_set IN RAW DEFAULT NULL,
   pad      IN RAW DEFAULT NULL)
RETURN RAW;

Parameters

Parameter	Description
`r`	The RAW byte sequence to convert.
`to_set`	The destination bytes corresponding to each byte in `from_set`. Can be any length.
`from_set`	The byte value to convert. Can be any length. If the value is less than or equal to the value of `r`, data is returned for this parameter.
`pad`	A single-byte padding value used when `to_set` is shorter than `from_set`.

Return values

Return value	Description
`RAW`	The converted byte sequence.

Example

SELECT utl_raw.transliterate('01020304050607080901020809', '1112', '01020304', '0a') FROM dual;
                     transliterate
--------------------------------------------------------
 \x3131313231303130313531363137313831393131313231383139
(1 row)

XRANGE function

Returns a contiguous sequence of single-byte RAW values covering all byte codes from start_byte to end_byte, inclusive.

Syntax

UTL_RAW.XRANGE (
   start_byte IN RAW DEFAULT NULL,
   end_byte   IN RAW DEFAULT NULL)
RETURN RAW;

Parameters

Parameter	Description
`start_byte`	The first byte in the sequence. Defaults to `x'00'`.
`end_byte`	The last byte in the sequence. Defaults to `x'FF'`.

Return values

Return value	Description
`RAW`	A sequence of consecutive single bytes from `start_byte` to `end_byte`.

Example

SELECT utl_raw.xrange('', '\x20') FROM dual;
                                xrange
----------------------------------------------------------------------
 \x000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f20
(1 row)