UTL_RAW v11

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

Note

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

Function/ProcedureFunction or ProcedureReturn TypeDescription
CAST_TO_RAW(c IN VARCHAR2)FunctionRAWConverts a VARCHAR2 string to a RAW value.
CAST_TO_VARCHAR2(r IN RAW)FunctionVARCHAR2Converts a RAW value to a VARCHAR2 string.
CONCAT(r1 IN RAW, r2 IN RAW, r3 IN RAW,…)FunctionRAWConcatenate multiple RAW values into a single RAW value.
CONVERT(r IN RAW, to_charset IN VARCHAR2, from_charset IN VARCHAR2)FunctionRAWConverts encoded data from one encoding to another, and returns the result as a RAW value.
LENGTH(r IN RAW)FunctionNUMBERReturns the length of a RAW value.
SUBSTR(r IN RAW, pos IN INTEGER, len IN INTEGER)FunctionRAWReturns a portion of a RAW value.

Advanced Server's implementation of UTL_RAW is a partial implementation when compared to Oracle's version. Only those functions and procedures listed in the table above 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 will return NULL.

Parameters

c

The VARCHAR2 value that will be converted to RAW.

Example

The following 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 will return NULL.

Parameters

r

The RAW value that will be converted to a VARCHAR2 value.

Example

The following 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 Advanced Server implementation is a variadic function, and does not place a restriction on the number of values that can be concatenated.

Parameters

r1, r2, r3,…

The RAW values that CONCAT will concatenate.

Example

The following 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 that will be converted.

to_charset

The name of the encoding to which r will be converted.

from_charset

The name of the encoding from which r will be converted.

Example

The following example uses the UTL_RAW.CAST_TO_RAW function to convert a VARCHAR2 string (Accounts) to a raw value, and then convert 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 that LENGTH will evaluate.

Example

The following 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)

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

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 will be returned.

pos

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

  • 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 that will be returned.

Example

The following 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)

The following 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)