Use UTL_RAW to manipulate RAW data types or retrieve the length of RAW data types - PolarDB

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

Prerequisites

An administrator must grant execute permissions to each user or user group before they can use this package.

Functions and stored procedures

Function or stored procedure	Type	Data type of the return value	Description
CAST_TO_RAW(c IN VARCHAR2)	Function	RAW	Converts a VARCHAR2 string into a RAW value.
CAST_TO_VARCHAR2(r IN RAW)	Function	VARCHAR2	Converts a RAW value into 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 substring of a RAW value.

Description

CAST_TO_RAW

Description

The CAST_TO_RAW function converts a VARCHAR2 string into 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.

Parameters

Parameter	Description
c	The string that you want to convert into a RAW value. The string is of the VARCHAR2 type.

Example

Use the CAST_TO_RAW function to convert a VARCHAR2 string into 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 returned result contains the original string and the converted RAW value.

Accounts
\x4163636f756e7473

CAST_TO_VARCHAR2

Description

The CAST_TO_VARCHAR2 function converts RAW data to VARCHAR2 data.

CAST_TO_VARCHAR2(r RAW)

If a value other than NULL is passed in, this function returns a VARCHAR2 value.
If NULL is passed in, this function returns NULL.

Parameters

Parameter	Description
r	The RAW value that you want to convert into a VARCHAR2 value.

Example

Use the CAST_TO_VARCHAR2 function to convert a RAW value into 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 returned result contains the original RAW value and the converted VARCHAR2 string.

\x4163636f756e7473
Accounts

CONCAT

Description

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.

Parameters

Parameter	Description
r1, r2, r3,...	The RAW values that you want CONCAT to concatenate.

Example

Use the CONCAT function to concatenate multiple RAW values into a single RAW value.

SELECT UTL_RAW.CONCAT('\x61', '\x62', '\x63') FROM DUAL;

Sample result:

  concat  
----------
 \x616263
(1 row)

CONVERT

Description

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.

Parameters

Parameter	Description
r	The RAW value that you want to convert.
to_charset	The name of the encoding format to which you want to convert r.
from_charset	The name of the encoding format from which you want to convert r.

Example

Use the CAST_TO_RAW function to convert the VARCHAR2 string Accounts into a RAW value, and then use the CONVERT function to convert the RAW value from UTF-8 to LATIN7 and convert the value from LATIN7 to UTF-8.

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;

In this example, the VARCHAR2 string, the RAW value, and the converted values are returned.

Accounts
\x4163636f756e7473
\x4163636f756e7473
\x4163636f756e7473

LENGTH

Description

The LENGTH function returns the length of a RAW value.

LENGTH(r RAW)

Parameters

Parameter	Description
r	The RAW value whose length you want LENGTH to return.

Example

Use the LENGTH function to return the length of a RAW value.

SELECT UTL_RAW.LENGTH(UTL_RAW.CAST_TO_RAW('Accounts')) FROM DUAL;

Sample result:

length
--------
8
(1 row)

Use the LENGTH function to return the length of a RAW value that includes multibyte characters.
```
SELECT UTL_RAW.LENGTH(UTL_RAW.CAST_TO_RAW('独孤求败'));
```
Sample result:
```
 length
--------
     12
(1 row)
```

SUBSTR

Description

The SUBSTR function returns a substring of a RAW value.

SUBSTR (r RAW, pos INTEGER, len INTEGER)

Parameters

Parameter	Description
r	The RAW value from which you want to return a substring.
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 returned substring starts from the first byte of the RAW value. If pos is set to a value that is greater than 1, the returned substring starts from the byte specified by pos. For example, if pos is set to 3, the returned substring starts from the third byte of the value. If pos is set to a negative value, the returned substring starts from the byte specified by pos, counted backward from the end of the source value. For example, if pos is set to -3, the returned substring starts from the third byte counted backward from the end of the value.
len	The maximum number of bytes that you want to return.

Example

Use the SUBSTR function to return a substring that starts from the third byte of a RAW value.

SET bytea_output = 'escape'; --- Output BYTEA data in the escape format.
SELECT UTL_RAW.SUBSTR(UTL_RAW.CAST_TO_RAW('Accounts'), 3, 5) FROM DUAL;

Sample result:

 substr
--------  
count
(1 row)

Use the SUBSTR function to return a substring that starts from the fifth byte counted backward from the end of a RAW value.

SET bytea_output = 'escape'; --- Output BYTEA data in the escape format.
SELECT UTL_RAW.SUBSTR(UTL_RAW.CAST_TO_RAW('Accounts'), -5 , 3) FROM DUAL;

Sample result:

 substr
--------
 oun
(1 row)