The UTL_RAW package allows you to manipulate RAW data or retrieve the length of RAW data.
Prerequisites
An administrator must grant execute permissions to each user or group before they can use this package.
Functions and stored procedures
Function or stored procedure | Type | Return type | Description |
---|---|---|---|
CAST_TO_RAW(c IN VARCHAR2) | Function | RAW | Converts a VARCHAR2 string to a RAW value. |
CAST_TO_VARCHAR2(r IN RAW) | Function | VARCHAR2 | Converts a RAW value to a VARCHAR2 string. |
CONCAT(r1 IN RAW, r2 IN RAW, r3 IN RAW,...) | Function | RAW | Concatenates multiple RAW values to generate a single RAW value. |
CONVERT(r IN RAW, to_charset IN VARCHAR2, from_charset IN VARCHAR2) | Function | RAW | Converts encoded data from one encoding format to another encoding format, and returns the result as a RAW value. |
LENGTH(r IN RAW) | Function | NUMBER | Returns the length of a RAW value. |
SUBSTR(r IN RAW, pos IN INTEGER, len IN INTEGER) | Function | RAW | Returns a portion of a RAW value. |
CAST_TO_RAW
The CAST_TO_RAW function converts a VARCHAR2 string to a RAW value.
CAST_TO_RAW(c VARCHAR2)
- If a value other than NULL is passed in, this function returns a RAW value.
- If NULL is passed in, this function returns NULL.
Parameter
Parameter | Description |
---|---|
c | Specifies the string to be converted. The string is a VARCHAR2 value that is to be converted to a RAW value. |
Examples
The following example shows how the CAST_TO_RAW function converts a VARCHAR2 string to a RAW value:
DECLARE
v VARCHAR2;
r RAW;
BEGIN
v := 'Accounts';
dbms_output.put_line(v);
r := UTL_RAW.CAST_TO_RAW(v);
dbms_output.put_line(r);
END;
The result set includes the content of the original string and the converted RAW value.
Accounts
\x4163636f756e7473
CAST_TO_VARCHAR2
The CAST_TO_VARCHAR2 function converts RAW data to VARCHAR2 data.
CAST_TO_VARCHAR2(r RAW)
- If a value rather than NULL is returned, this function returns the VARCHAR2 value.
- If NULL is passed in, this function returns NULL.
Parameter
Parameter | Description |
---|---|
r | Specifies the RAW value that is to be converted to a VARCHAR2 value. |
Examples
The following example shows how the CAST_TO_VARCHAR2 function converts a RAW value to a VARCHAR2 string:
DECLARE
r RAW;
v VARCHAR2;
BEGIN
r := '\x4163636f756e7473';
dbms_output.put_line(r);
v := UTL_RAW.CAST_TO_VARCHAR2(r);
dbms_output.put_line(v);
END;
The result set includes the content of the RAW value and the converted VARCHAR2 string.
\x4163636f756e7473
Accounts
CONCAT
The CONCAT function concatenates multiple RAW values into a single RAW value.
CONCAT(r1 RAW, r2 RAW, r3 RAW,...)
The function returns a RAW value. Different from the Oracle implementation, the implementation of PolarDB for PostgreSQL(Compatible with Oracle) is a variadic function, and does not limit the number of values that can be concatenated.
Parameter
Parameter | Description |
---|---|
r1, r2, r3,... | Specifies the RAW values that CONCAT is to concatenate. |
Examples
The following example shows how to use the CONCAT function to concatenate multiple RAW values and generate a RAW value. The concatenated values as the result are then converted to the VARCHAR2 format by using the CAST_TO_VARCHAR2 function.
SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.CONCAT('\x61', '\x62', '\x63')) FROM DUAL;
A similar output is returned:concat
--------
abc
(1 row)
CONVERT
The CONVERT function converts a string from one encoding format to another encoding format and returns the result as a RAW value.
CONVERT(r RAW, to_charset VARCHAR2, from_charset VARCHAR2)
The function returns a RAW value. Parameter
Parameter | Description |
---|---|
r | Specifies the RAW value that is to be converted. |
to_charset | Specifies the name of the encoding format to which r is to be converted. |
from_charset | Specifies the name of the encoding format from which r is to be converted. |
Examples
The following example shows how the UTL_RAW.CAST_TO_RAW function converts a VARCHAR2 string Accounts to a RAW value, converts the RAW value from UTF8 to LATIN7, and then converts the value from LATIN7 to UTF8:
DECLARE
r RAW;
v VARCHAR2;
BEGIN
v:= 'Accounts';
dbms_output.put_line(v);
r:= UTL_RAW.CAST_TO_RAW(v);
dbms_output.put_line(r);
r:= UTL_RAW.CONVERT(r, 'UTF8', 'LATIN7');
dbms_output.put_line(r);
r:= UTL_RAW.CONVERT(r, 'LATIN7', 'UTF8');
dbms_output.put_line(r);
END;
The example returns the VARCHAR2 value, the RAW value, and the converted values.
Accounts
\x4163636f756e7473
\x4163636f756e7473
\x4163636f756e7473
LENGTH
LENGTH(r RAW)
Parameter
Parameter | Description |
---|---|
r | Specifies the RAW value that LENGTH is to evaluate. |
Examples
The following example shows how the LENGTH function returns the length of a RAW value:
SELECT UTL_RAW.LENGTH(UTL_RAW.CAST_TO_RAW('Accounts')) FROM DUAL;
A similar output is returned:length
--------
8
(1 row)
The following example uses the LENGTH function to return the length of a RAW value that includes multibyte characters:
SELECT UTL_RAW.LENGTH(UTL_RAW.CAST_TO_RAW('独孤求败'));
A similar output is returned: length
--------
12
(1 row)
SUBSTR
The SUBSTR function returns a substring of a RAW value.
SUBSTR (r RAW, pos INTEGER, len INTEGER)
Parameter
Parameter | Description |
---|---|
r | Specifies the RAW value from which the substring is to be returned. |
pos | Specifies the position within the RAW value where the first byte of the returned substring is located.
|
len | Specifies the maximum number of bytes that are to be returned. |
Examples
The following example shows how the SUBSTR function retrieves a substring that starts from the third byte of a RAW value:
SELECT UTL_RAW.SUBSTR(UTL_RAW.CAST_TO_RAW('Accounts'), 3, 5) FROM DUAL;
A similar output is returned:
substr
--------
count
(1 row)
The following example shows how the SUBSTR function retrieves a substring that starts from the fifth byte of a RAW value:
SELECT UTL_RAW.SUBSTR(UTL_RAW.CAST_TO_RAW('Accounts'), -5 , 3) FROM DUAL;
A similar output is returned: substr
--------
oun
(1 row)