UTL_RAW v15

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

EDB Postgres Advanced Server's implementation of UTL_RAW is a partial implementation when compared to Oracle's version. Only the functions and procedures listed in the table are supported.

CAST_TO_RAW

The CAST_TO_RAW function converts a VARCHAR2 string to a RAW value. The signature is:

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

c

The VARCHAR2 value to convert to RAW.

Example

This example uses the CAST_TO_RAW function to convert 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. The signature is:

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

r

The RAW value to convert to a VARCHAR2 value.

Example

This example uses the CAST_TO_VARCHAR2 function to convert 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

The CONCAT function concatenates multiple RAW values into a single RAW value. The signature is:

CONCAT(<r1> RAW, <r2> RAW, <r3> RAW,…)

The function returns a RAW value. Unlike the Oracle implementation, the EDB Postgres Advanced Server implementation is a variadic function and doesn't place a restriction on the number of values that can be concatenated.

Parameters

r1, r2, r3,…

The RAW values for CONCAT to concatenate.

Example

This example uses the CONCAT function to concatenate 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 result (the concatenated values) is then converted to VARCHAR2 format by the CAST_TO_VARCHAR2 function.

CONVERT

The CONVERT function converts a string from one encoding to another encoding and returns the result as a RAW value. The signature is:

CONVERT(<r> RAW, <to_charset> VARCHAR2, <from_charset> VARCHAR2)

The function returns a RAW value.

Parameters

r

The RAW value to convert.

to_charset

The name of the encoding to which r is converted.

from_charset

The name of the encoding from which r is converted.

Example

This example uses the UTL_RAW.CAST_TO_RAW function to convert a VARCHAR2 string (Accounts) to a raw value. It then converts the value from UTF8 to LATIN7 and then 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

The LENGTH function returns the length of a RAW value. The signature is:

LENGTH(<r> RAW)

The function returns a RAW value.

Parameters

r

The RAW value for LENGTH to evaluate.

Example

This example uses the LENGTH function to return the length of a RAW value:

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

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

This 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

The SUBSTR function returns a substring of a RAW value. The signature is:

SUBSTR (<r> RAW, <pos> INTEGER, <len> INTEGER)

This function returns a RAW value.

Parameters

r

The RAW value from which the substring is returned.

pos

The position of the first byte of the returned substring in the RAW value .

• If pos is 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 3, the substring begins at the third byte of the value.
• If pos is negative, the substring begins at pos bytes from the end of the source value. For example, if pos is -3, the substring begins at the third byte from the end of the value.

len

The maximum number of bytes to return.

Example

This example uses the SUBSTR function to select a substring that begins 3 bytes from the start of a RAW value:

SELECT UTL_RAW.SUBSTR(UTL_RAW.CAST_TO_RAW('Accounts'), 3, 5) FROM DUAL;

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

This example uses the SUBSTR function to select a substring that starts 5 bytes 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)

• On this page
• CAST_TO_RAW
• CAST_TO_VARCHAR2
• CONCAT
• CONVERT
• LENGTH
• SUBSTR