The UTL_RAW package allows you to manipulate or retrieve the length of raw data types.

Note An administrator must grant execute permissions to each user or group before they can use this package.
Function/Procedure Category 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 into 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.

The implementation of UTL_RAW in PolarDB databases compatible with Oracle is a partial implementation when compared with native Oracle. Only those functions and procedures listed in the preceding table are supported.

CAST_TO_RAW

You can use the CAST_TO_RAW function to convert a VARCHAR2 string to a RAW value. The function has the following signature:

CAST_TO_RAW(c VARCHAR2)

The function returns a RAW value if you pass a non-NULL value. If you pass a NULL value, the function returns NULL.

Parameters

Parameter Description
c The VARCHAR2 value that is converted to RAW.

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

You can use the CAST_TO_VARCHAR2 function to convert RAW data to VARCHAR2 data. The function has the following signature:

CAST_TO_VARCHAR2(r RAW)

The function returns a VARCHAR2 value if you pass a non-NULL value. If you pass a NULL value, the function returns NULL.

Parameters

Parameter Description
r The RAW value that is 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(v);
  v := UTL_RAW.CAST_TO_VARCHAR2(r);
  dbms_output.put_line(r);
END;

The result set includes the content of the original string and the converted RAW value.

\x4163636f756e7473
Accounts

CONCAT

You can use the CONCAT function to concatenate multiple RAW values into a single RAW value. The function has the following signature:

CONCAT(r1 RAW, r2 RAW, r3 RAW,...)

The function returns a RAW value. Different from the Oracle implementation, the implementation of PolarDB databases compatible with Oracles is a variadic function, and does not limit the number of values that can be concatenated.

Parameters

Parameter Description
r1, r2, r3,... The RAW values that CONCAT concatenates.

Examples

The following example shows how the CONCAT function concatenates multiple RAW values into a single RAW value:

SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.CONCAT('\x61', '\x62', '\x63')) FROM DUAL;  concat
--------  abc(1 row)

The concatenated values as the result is then converted to the VARCHAR2 format by the CAST_TO_VARCHAR2 function.

CONVERT

You can use the CONVERT function to convert a string from one encoding format to another encoding format and returns the result as a RAW value. The function has the following signature:

CONVERT(r RAW, to_charset VARCHAR2, from_charset VARCHAR2)

The function returns a RAW value.

Parameters

Parameter Description
r The RAW value that is converted.
to_charset The name of the encoding format to which r is converted.
from_charset The name of the encoding format from which r is converted.

Examples

The following example shows how the UTL_RAW.CAST_TO_RAW function converts the 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);

The example returns the VARCHAR2 value, the RAW value, and the converted values.

Accounts
\x4163636f756e7473
\x4163636f756e7473
\x4163636f756e7473

LENGTH

You can use the LENGTH function to return the length of a RAW value. The function has the following signature:

LENGTH(r RAW)

The function returns a RAW value.

Parameters

Parameter Description
r The RAW value that LENGTH evaluates.

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;  length
--------8(1 row)

The following example uses the LENGTH function to return the length of a RAW value that includes multi-byte characters:

SELECT UTL_RAW.LENGTH(UTL_RAW.CAST_TO_RAW('独孤求败'));
 length
--------
     12
(1 row)

SUBSTR

You can use the SUBSTR function to return a substring of a RAW value. The function has the following signature:

SUBSTR (r RAW, pos INTEGER, len INTEGER)

The function returns a RAW value.

Parameters

Parameter Description
r The RAW value from which the substring is returned.
pos The position within the RAW value where the first byte of the returned substring is located.
  • If pos is set to 0 or 1, the substring begins at the first byte of the RAW value.
  • If pos is greater than one, the substring begins at the first byte specified by pos. For example, if pos is set to 3, the substring begins at the third byte of the value.
  • If pos is negative, the substring covers a length of pos bytes from the end of the source value. For example, if pos is set to -3, the substring begins at the third byte from the end of the value.
len The maximum number of bytes that are returned.

Examples

The following example shows how the SUBSTR function retrieves a 3-byte substring that starts from the beginning of a RAW value:

SELECT UTL_RAW.SUBSTR(UTL_RAW.CAST_TO_RAW('Accounts'), 3, 5) FROM DUAL;
 substr--------  count(1 row)

The following example shows how the SUBSTR function retrieves a 5-byte substring that starts from the end of a RAW value:

SELECT UTL_RAW.SUBSTR(UTL_RAW.CAST_TO_RAW('Accounts'), -5 , 3) FROM DUAL;
 substr
--------
 oun
(1 row)