Table of Contents
This chapter briefly introduces the UTL_RAW package, and describes how to use the functions of the package.
UTL_RAW provides various functions related to RAW type data.
In a SQL statement, functions that can be used for CHAR or VARCHAR data cannot be used for RAW data. Data conversion is not allowed among the RAW, CHAR, and VARCHAR types.
To solve this problem, functions defined in UTL_RAW can be used so that the functions used for CHAR and VARCHAR data can also be used for RAW data.
This section describes the functions provided by the UTL_RAW package, in alphabetical order.
Returns the result of performing AND operation of two binary operands. If the lengths of the two operands are different, the result is as long as the longer operand.
Details about the BIT_AND function are as follows:
Prototype
UTL_RAW.BIT_AND
(
r1 IN RAW,
r2 IN RAW
)
RETURN RAW;
Parameter
| Parameter | Description |
|---|---|
| r1 | First operand. |
| r2 | Second operand. |
Return Value
| Value | Description |
|---|---|
| RAW | Returns the AND of r1 and r2. |
| NULL | Returned if either r1 or r2 is NULL. |
Example
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_RAW.BIT_AND('abc', '012'));
END;
/
0010
PSM completed
SQL>
Returns the 1's complement of a binary data.
Details about the BIT_COMPLEMENT function are as follows:
Prototype
UTL_RAW.BIT_COMPLEMENT
(
r IN RAW
)
RETURN RAW;
Parameter
| Parameter | Description |
|---|---|
| r | Operand. |
Return Value
| Value | Description |
|---|---|
| RAW | Returns the COMPLEMENT of r. |
| NULL | Returned if r is NULL. |
Example
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_RAW.BIT_COMPLEMENT('456'));
END;
/
FBA9
PSM completed
SQL>
Performs an OR operation of two binary operands. If the lengths of the two operands are different, the result is as long as the longer operand.
Details about the BIT_OR function are as follows:
Prototype
UTL_RAW.BIT_OR
(
r1 IN RAW,
r2 IN RAW
)
RETURN RAW;
Parameter
| Parameter | Description |
|---|---|
| r1 | First operand. |
| r2 | Second operand. |
Return Value
| Value | Description |
|---|---|
| RAW | Returns the OR of r1 and r2. |
| NULL | Returned if either r1 or r2 is NULL. |
Example
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_RAW.BIT_OR('123', '456'));
END;
/
0577
PSM completed
SQL>
Performs an XOR operation for two binary operands. If the length of the two operands is different, the result is as long as the longer operand.
Details about the BIT_XOR function are as follows:
Prototype
UTL_RAW.BIT_XOR
(
r1 IN RAW,
r2 IN RAW
)
RETURN RAW;
Parameter
| Parameter | Description |
|---|---|
| r1 | First operand. |
| r2 | Second operand. |
Return Value
| Value | Description |
|---|---|
| RAW | Returns the XOR of r1 and r2. |
| NULL | Returned if either r1 or r2 is NULL. |
Example
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_RAW.BIT_XOR('123', '456'));
END;
/
0575
PSM completed
SQL>
Converts a BINARY_DOUBLE value to RAW data. The length of the returned data is 8 bytes, and the specified endianness determines the byte order.
The endianess parameter represents the order in which bytes of RAW data is stored. If the system is little-endian, and the parameter is big-endian, the byte order of n is reversed, and if the parameter is little-endian, the bytes are stored in the same order as the byte order of the system.
Details about the CAST_FROM_BINARY_DOUBLE function are as follows:
Prototype
UTL_RAW.CAST_FROM_BINARY_DOUBLE
(
n IN BINARY_DOUBLE,
endianess IN PLS_INTEGER DEFAULT 1
)
RETURN RAW;
Parameter
| Parameter | Description |
|---|---|
| n | BINARY_DOUBLE value to convert. |
| endianess | Endianness. Either 1 (big-endian) or 2 (little-endian). |
Return Value
| Value | Description |
|---|---|
| RAW | Returns a binary representation of the BINARY_DOUBLE value. |
| NULL | Returned if the input value is NULL. |
Exception
| Exception | Description |
|---|---|
| INVALID_ENDIAN | Occurs when the endian value is not 1 or 2. |
Example
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_FROM_BINARY_DOUBLE(1.5d));
END;
/
3FF8000000000000
PSM completed
SQL>
Converts a BINARY_FLOAT value to RAW data. The length of the returned data is four bytes, and the specified endianness determines the byte order.
The endianess parameter represents the order in which bytes of RAW data is stored. If the system is little-endian, and the parameter is big-endian, the byte order of n is reversed, and if the parameter is little-endian, the bytes are stored in the same order as the byte order of the system.
Details about the CAST_FROM_BINARY_FLOAT function are as follows:
Prototype
UTL_RAW.CAST_FROM_BINARY_FLOAT
(
n IN BINARY_FLOAT,
endianess IN PLS_INTEGER DEFAULT 1
)
RETURN RAW;
Parameter
| Parameter | Description |
|---|---|
| n | BINARY_FLOAT value to convert |
| endianess | Endianness. Either 1 (big-endian) or 2 (little-endian). |
Return Value
| Value | Description |
|---|---|
| RAW | Returns a binary representation of the BINARY_FLOAT value. |
| NULL | Returned if the input value is NULL. |
Exception
| Exception | Description |
|---|---|
| INVALID_ENDIAN | Occurs when the endian value is not 1 or 2. |
Example
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_FROM_BINARY_FLOAT(1.5f));
END;
/
3FC00000
PSM completed
SQL>
Converts a BINARY_INTEGER value to RAW data. The length of the returned data is 4 bytes, and the specified endianness determines the byte order.
The endianess parameter represents the order in which bytes of RAW data is stored. If the system is little-endian, and the parameter is big-endian, the byte order of n is reversed, and if the parameter is little-endian, the bytes are stored in the same order as the byte order of the system.
Details about the CAST_FROM_BINARY_INTEGER function are as follows:
Prototype
UTL_RAW.CAST_FROM_BINARY_INTEGER
(
n IN BINARY_INTEGER,
endianess IN PLS_INTEGER DEFAULT 1
)
RETURN RAW;
Parameter
| Parameter | Description |
|---|---|
| n | BINARY_INTEGER value to convert |
| endianess | Endianness. Either 1 (big-endian) or 2 (little-endian). |
Return Value
| Value | Description |
|---|---|
| RAW | Returns a binary representation of the BINARY_INTEGER value. |
| NULL | Returned if the input value is NULL. |
Exception
| Exception | Description |
|---|---|
| INVALID_ENDIAN | Occurs when the endian value is not 1 or 2. |
Example
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_FROM_BINARY_INTEGER(10));
END;
/
0000000A
PSM completed
SQL>
Converts a NUMBER value to RAW data. The returned binary data is the Tibero internal binary format, and its length is variable depending on the input value.
The endianess parameter represents the order in which bytes of RAW data is stored. If the system is little-endian, and the parameter is big-endian, the byte order of n is reversed, and if the parameter is little-endian, the bytes are stored in the same order as the byte order of the system.
Details about the CAST_FROM_NUMBER function are as follows:
Prototype
UTL_RAW.CAST_FROM_NUMBER
(
n IN NUMBER
)
RETURN RAW;
Parameter
| Parameter | Description |
|---|---|
| n | NUMBER value to convert. |
Return Value
| Value | Description |
|---|---|
| RAW | Returns a binary representation of the NUMBER value. |
| NULL | Returned if the input value is NULL. |
Exception
| Exception | Description |
|---|---|
| INVALID_ENDIAN | Occurs when the endian value is not 1 or 2. |
Example
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_FROM_NUMBER(1.5));
END;
/
03C181B2
PSM completed
SQL>
Converts a RAW value in BINARY_DOUBLE data. The length of the input binary data is 8 bytes.
Details about the CAST_TO_BINARY_DOUBLE function are as follows:
Prototype
UTL_RAW.CAST_TO_BINARY_DOUBLE
(
r IN RAW,
endianess IN PLS_INTEGER DEFAULT 1
)
RETURN BINARY_DOUBLE;
Parameter
| Parameter | Description |
|---|---|
| r | Binary data to convert |
| endianess | Endianness. Either 1 (big-endian) or 2 (little-endian). |
Return Value
| Value | Description |
|---|---|
| BINARY_DOUBLE | Returns the value converted from RAW data to BINARY_DOUBLE data. |
| NULL | Returned if the input value is NULL. |
Exception
| Exception | Description |
|---|---|
| INVALID_ENDIAN | Occurs when the endian value is not 1 or 2. |
Example
DECLARE
bin raw(100);
BEGIN
bin := UTL_RAW.CAST_FROM_BINARY_DOUBLE(1.5d);
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_BINARY_DOUBLE(bin));
END;
/
1.5E+00
PSM completed
SQL>
Converts a RAW value to BINARY_FLOAT data. The length of the input binary data is 4 bytes.
Details about the CAST_TO_BINARY_FLOAT function are as follows:
Prototype
UTL_RAW.CAST_TO_BINARY_FLOAT
(
r IN RAW,
endianess IN PLS_INTEGER DEFAULT 1
)
RETURN BINARY_FLOAT;
Parameter
| Parameter | Description |
|---|---|
| r | Binary data to convert. |
| endianess | Endianness. Either 1 (big-endian) or 2 (little-endian). |
Return Value
| Value | Description |
|---|---|
| BINARY_FLOAT | Returns the value converted from RAW data to BINARY_FLOAT data. |
| NULL | Returned if the input value is NULL. |
Exception
| Exception | Description |
|---|---|
| INVALID_ENDIAN | Occurs when the endian value is not 1 or 2. |
Example
DECLARE
bin raw(100);
BEGIN
bin := UTL_RAW.CAST_FROM_BINARY_FLOAT(1.5f);
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_BINARY_FLOAT(bin));
END;
/
1.5E+00
PSM completed
SQL>
Converts a RAW value to BINARY_INTEGER data. The length of the input binary data is 4 bytes.
Details about the CAST_TO_BINARY_INTEGER function are as follows:
Prototype
UTL_RAW.CAST_TO_BINARY_INTEGER
(
r IN RAW,
endianess IN PLS_INTEGER DEFAULT 1
)
RETURN BINARY_INTEGER;
Parameter
| Parameter | Description |
|---|---|
| r | Binary data to convert. |
| endianess | Endianness. Either 1 (big-endian) or 2 (little-endian). |
Return Value
| Value | Description |
|---|---|
| BINARY_INTEGER | Returns the value converted from RAW data to BINARY_INTEGER data. |
| NULL | Returned if the input value is NULL. |
Exception
| Exception | Description |
|---|---|
| INVALID_ENDIAN | Occurs when the endian value is not 1 or 2. |
Example
DECLARE
bin raw(100);
BEGIN
bin := UTL_RAW.CAST_FROM_BINARY_INTEGER(777);
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_BINARY_INTEGER(bin));
END;
/
777
PSM completed
SQL>
Converts a RAW value to NUMBER data. The returned binary data is the Tibero internal binary format, and its length is variable depending on the input value.
Details about the CAST_TO_NUMBER function are as follows:
Prototype
UTL_RAW.CAST_TO_NUMBER
(
r IN RAW
)
RETURN NUMBER;
Parameter
| Parameter | Description |
|---|---|
| r | Binary data to convert. |
Return Value
| Value | Description |
|---|---|
| NUMBER | Returns the value converted from RAW data to NUMBER data. |
| NULL | Returned if the input value is NULL. |
Exception
| Exception | Description |
|---|---|
| INVALID_ENDIAN | Occurs when the endian value is not 1 or 2. |
Example
DECLARE
bin raw(100);
BEGIN
bin := UTL_RAW.CAST_FROM_NUMBER(1004);
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_NUMBER(bin));
END;
/
1004
PSM completed
SQL>
Converts a VARCHAR type string into RAW data. The input is a string representation of the RAW data.
Details about the CAST_TO_RAW function are as follows:
Prototype
UTL_RAW.CAST_TO_RAW
(
c IN VARCHAR2
)
RETURN RAW;
Parameter
| Parameter | Description |
|---|---|
| c | String to convert. |
Return Value
| Value | Description |
|---|---|
| RAW | Returns a binary representation of VARCHAR2. |
| NULL | Returned if the input value is NULL. |
Example
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_RAW('0a3bcf'));
END;
/
306133626366
PSM completed
SQL>
Converts a RAW type string to VARCHAR2 data. The output is a string representation of binary data.
Details about the CAST_TO_VARCHAR2 function are as follows:
Prototype
UTL_RAW.CAST_TO_VARCHAR2
(
r IN RAW
)
RETURN VARCHAR2;
Parameter
| Parameter | Description |
|---|---|
| r | Binary data to convert. |
Return Value
| Value | Description |
|---|---|
| VARCHAR2 | Returns the value converted from RAW data to VARCHAR2 data. |
| NULL | Returned if the input value is NULL. |
Example
DECLARE
bin RAW(100);
BEGIN
bin := UTL_RAW.CAST_TO_RAW('Nanobase');
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(bin));
END;
/
Nanobase
PSM completed
SQL>
Compares two RAW operands. If the two operands differ in length, the shorter operand is padded so that the lengths of the two operands are the same.
Details about the COMPARE function are as follows:
Prototype
UTL_RAW.COMPARE
(
r1 IN RAW,
r2 IN RAW,
pad IN RAW DEFAULT NULL
)
RETURN INTEGER;
Parameter
| Parameter | Description |
|---|---|
| r1 | First operand. |
| r2 | Second operand. |
| pad | Bytes to append to the shorter operand. Default value: '00' |
Return Value
| Value | Description |
|---|---|
| 0 | Returned if both r1 and r2 are NULL, or their lengths are exactly the same. |
| N >= 1 | Returns the position where byte values of r1 and r2 are different. |
Example
DECLARE
x RAW(20) := UTL_RAW.CAST_TO_RAW('ABCDEF');
y RAW(20) := UTL_RAW.CAST_TO_RAW('ABCDFF');
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_RAW.COMPARE(x, y));
END;
/
5
PSM completed
SQL>
Concatenates up to 12 RAW data. This function can have from 1 to 12 input parameters. The maximum length of the concatenated binary data is 32,767 bytes. If the resulting data length exceeds this limit, the ERROR_EXP_CONCAT_TOO_LONG error occurs.
Details about the CONCAT function are as follows:
Prototype
UTL_RAW.CONCAT
(
r1 IN RAW,
r2 IN RAW,
r3 IN RAW,
r4 IN RAW,
r5 IN RAW,
r6 IN RAW,
r7 IN RAW,
r8 IN RAW,
r9 IN RAW,
r10 IN RAW,
r11 IN RAW,
r12 IN RAW
)
RETURN RAW;
Parameter
| Parameter | Description |
|---|---|
| r1, ..., r12 | Binary data to concatenate. Up to 12 parameters can be used. |
Return Value
| Value | Description |
|---|---|
| RAW | Returns the concatenated RAW data. |
| NULL | Returned if all the input values are NULL. |
Example
DECLARE
x RAW(100) := UTL_RAW.CAST_TO_RAW('Give me liberty');
y RAW(100) := UTL_RAW.CAST_TO_RAW(', or give me death');
z RAW(200);
BEGIN
z := UTL_RAW.CONCAT(x, y);
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(z));
END;
/
Give me liberty, or give me death
PSM completed
SQL>
Concatenates n copies of a RAW data. If the data is longer than 32,767 bytes, an exception occurs.
Details about the COPIES function are as follows:
Prototype
UTL_RAW.COPIES
(
r IN RAW,
n IN NUMBER
)
RETURN RAW;
Parameter
| Parameter | Description |
|---|---|
| r | Binary data to copy. |
| n | Number of times to copy the RAW data. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR |
|
Example
DECLARE
x VARCHAR2(100);
y RAW(200);
z RAW(200);
BEGIN
x := 'Books are ships which pass ';
x := x || 'through the vast seas of time';
y := UTL_RAW.CAST_TO_RAW(x);
z := UTL_RAW.COPIES(y, 1);
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(z));
END;
/
Books are ships which pass through the vast seas of time
PSM completed
SQL>
Returns the length of a RAW data in bytes.
Details about the LENGTH function are as follows:
Prototype
UTL_RAW.LENGTH
(
r IN RAW
)
RETURN INTEGER;
Parameter
| Parameter | Description |
|---|---|
| r | Binary data to return the length of. |
Return Value
| Value | Description |
|---|---|
| NUMBER | Returns the data length. |
| NULL | Returned if the input value is NULL. |
Example
DECLARE
x RAW(20) := UTL_RAW.CAST_TO_RAW('Out of mind');
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_RAW.LENGTH(x));
END;
/
11
PSM completed
SQL>
Overlays RAW source data with RAW target data. The source data which starts from the position specified by pos and whose length is specified by len is copied over the target data.
If the source data is shorter than len, the remaining part is filled with bytes of pad. If the target data is shorter than pos, the pad bytes fill the space from the beginning to the position at pos, and then the source data is copied.
This function returns the data created by copying the source data over the target data without updating the actual target data. The returned data must not be longer than 32,767 bytes.
Details about the OVERLAY function are as follows:
Prototype
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;
Parameter
| Parameter | Description |
|---|---|
| overlay_str | Source binary data. |
| target | Target binary data. |
| pos | Position in the target binary data. Must be greater than or equal to 1. |
| len | Length of the source data to overlay. Must be greater than or equal to 0. If set to NULL, all of the data is overlayed. |
| pad | Pad byte. Default value: '00' |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR |
|
Example
DECLARE
bin RAW(100);
BEGIN
bin := UTL_RAW.OVERLAY('ee', 'fabcdff', 2, 3, '12');
DBMS_OUTPUT.PUT_LINE(bin);
END;
/
0FEE1212
PSM completed
SQL>
Reverses the byte order of a RAW data. The length of the returned binary data is the same as that of the input binary data.
Details about the REVERSE function are as follows:
Prototype
UTL_RAW.REVERSE
(
data IN RAW
)
RETURN RAW;
Parameter
| Parameter | Description |
|---|---|
| data | Binary data to reverse. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when the input data is NULL or its length is 0. |
Example
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_RAW.REVERSE('abcd132'));
END;
/
32D1BC0A
PSM completed
SQL>
Returns all or a part of a RAW data that is len bytes long starting at the position specified in pos. If the binary data is NULL, NULL is returned.
Details about the SUBSTR function are as follows:
Prototype
UTL_RAW.SUBSTR
(
data IN RAW,
pos IN BINARY_INTEGER,
len IN BINARY_INTEGER DEFAULT NULL
)
RETURN RAW;
Parameter
| Parameter | Description |
|---|---|
| data | Binary data. |
| pos | Offset position in the binary data.
|
| len | Length of data to return. If set to NULL, all data from pos to the end of the binary data is returned. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when the parameter pos is 0, or the parameter len is less than 1. |
Example
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_RAW.SUBSTR('abc1234', 2, 2));
END;
/
BC12
PSM completed
SQL>
Translates specified bytes of RAW data. If a byte in from_set exist in the binary data, the byte is replaced by the byte in the corresponding position in to_set. If to_set is too short to include the corresponding byte in the same position, it is replaced by a blank byte.
This function returns the translated data without modifying the input binary data.
Details about the TRANSLATE function are as follows:
Prototype
UTL_RAW.TRANSLATE
(
data IN RAW,
from_set IN RAW,
to_set IN RAW
)
RETURN RAW;
Parameter
| Parameter | Description |
|---|---|
| data | Binary data. |
| from_set | Byte-codes to translate. |
| to_set | Byte-codes to translate to. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when the parameter from_set or to_set is NULL, or if either of the length is 0. |
Example
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_RAW.TRANSLATE('abcde', 'bc', '77'));
END;
/
0A77DE
PSM completed
SQL>
Converts specified bytes of a RAW data. If a byte in from_set exist in the binary data, the byte is replaced by the byte in the corresponding position in to_set. If to_set is too short to include the corresponding byte in the same position, it is replaced by the pad byte.
This function returns the translated data without modifying the input binary data.
Details about the TRANSLITERATE function are as follows:
Prototype
UTL_RAW.TRANSLITERATE
(
data IN RAW,
to_set IN RAW DEFAULT NULL,
from_set IN RAW DEFAULT NULL,
pad IN RAW DEFAULT NULL
)
RETURN RAW;
Parameter
| Parameter | Description |
|---|---|
| data | Binary data. |
| from_set | Byte-codes to translate. |
| to_set | Byte-codes to translate to. |
| pad | 1-byte used if to_set is shorter than from_set. Only 1 byte of the input value is used regardless of the actual length of the input. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when data is NULL or its length is 0. |
Example
DECLARE
bin RAW(100);
BEGIN
bin := UTL_RAW.TRANSLITERATE('abcde', 'cd', 'bcde');
DBMS_OUTPUT.PUT_LINE(bin);
END;
/
0ACD00
PSM completed
SQL>
Returns a RAW data with all bytes in order starting from the start_byte and ending with the end_byte. If start_byte is larger than end_byte, it starts with the start_byte, wraps from 0xFF to 0x00, and ends at the end_byte.
Details about the XRANGE function are as follows:
Prototype
UTL_RAW.XRANGE
(
start_byte IN RAW DEFAULT NULL,
end_byte IN RAW DEFAULT NULL
)
RETURN RAW;
Parameter
| Parameter | Description |
|---|---|
| start_byte | First byte of the result. |
| end_byte | Last byte of the result. |
Example
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_RAW.XRANGE('a', 'd'));
END;
/
0A0B0C0D
PSM completed
SQL>