11 UTL_RAW

The UTL_RAW package provides SQL functions for manipulating RAW data types.

This chapter contains the following topics:

Using UTL_RAW
- Overview
- Operational notes
Summary of UTL_RAW subprograms

Using UTL_RAW

Overview
Operational notes

Overview

This package is necessary because normal SQL functions do not operate on RAW values, and PL/SQL does not allow overloading between a RAW and a CHAR data type. UTL_RAW also includes subprograms that convert various COBOL number formats to and from RAW formats.

UTL_RAW is not specific to the database environment and may be used in other environments. For this reason, the prefix UTL has been given to the package, instead of DBMS.

Operational notes

UTL_RAW allows a RAW "record" to be composed of many elements. By using the RAW data type, character set conversion will not be performed, keeping the RAW value in its original format when being transferred through remote procedure calls.

With the RAW functions, you can manipulate binary data that was previously limited to the hextoraw and rawtohex SQL functions.

Functions returning RAW values do so in hexadecimal encoding.

Summary of UTL_RAW subprograms

Table 11-1 UTL_RAW Package Subprograms

Subprogram	Description
BIT_AND function	Performs bitwise logical "and" of two RAW values and returns the resulting RAW.
BIT_COMPLEMENT function	Performs bitwise logical "complement" of a RAW value and returns the resulting RAW.
BIT_OR function	Performs bitwise logical "or" of two RAW values and returns the resulting RAW.
BIT_XOR function	Performs bitwise logical "exclusive or" of two RAW values and returns the resulting RAW.
CAST_FROM_BINARY_DOUBLE function	Returns the RAW binary representation of a BINARY_DOUBLE value.
CAST_FROM_BINARY_FLOAT function	Returns the RAW binary representation of a BINARY_FLOAT value.
CAST_FROM_BINARY_INTEGER function	Returns the RAW binary representation of a BINARY_INTEGER value.
CAST_FROM_NUMBER function	Returns the RAW binary representation of a NUMBER value.
CAST_TO_BINARY_DOUBLE function	Casts the RAW binary representation of a BINARY_DOUBLE value into a BINARY_DOUBLE.
CAST_TO_BINARY_FLOAT function	Casts the RAW binary representation of a BINARY_FLOAT value into a BINARY_FLOAT.
CAST_TO_BINARY_INTEGER function	Casts the RAW binary representation of a BINARY_INTEGER value into a BINARY_INTEGER.
CAST_TO_NUMBER function	Casts the RAW binary representation of a NUMBER value into a NUMBER.
CAST_TO_NVARCHAR2 function	Converts a RAW value represented using `n` data bytes into an NVARCHAR2 value with `n` data bytes.
CAST_TO_RAW function	Converts a VARCHAR2 value represented using `n` data bytes into a RAW value with `n` data bytes.
CAST_TO_VARCHAR2 function	Converts a RAW value represented using `n` data bytes into a VARCHAR2 value with `n` data bytes.
COMPARE function	Compares two RAW values.
CONCAT function	Concatenates up to 12 RAW values into a single RAW.
CONVERT function	Converts a RAW value from one character set to another and returns the resulting RAW.
COPIES function	Copies a RAW value a specified number of times and returns the concatenated RAW value.
LENGTH function	Returns the length in bytes of a RAW value.
OVERLAY function	Overlays the specified portion of a target RAW value with an overlay RAW value, starting from a specified byte position and proceeding for a specified number of bytes.
REVERSE function	Reverses a byte-sequence in a RAW value.
SUBSTR function	Returns a substring of a RAW value for a specified number of bytes from a specified starting position.
TRANSLATE function	Translates the specified bytes from an input RAW value according to the bytes in a specified translation RAW value.
TRANSLITERATE function	Converts the specified bytes from an input RAW value according to the bytes in a specified transliteration RAW value.
XRANGE function	Returns a RAW value containing the succession of one-byte encodings beginning and ending with the specified byte-codes.

Note:

Notes on data types:

The PLS_INTEGER and BINARY_INTEGER data types are identical. This document uses "BINARY_INTEGER" to indicate data types in reference information (such as for table types, record types, subprogram parameters, or subprogram return values), but may use either in discussion and examples.
The INTEGER and NUMBER(38) data types are also identical. This document uses "INTEGER" throughout.

BIT_AND function

This function performs bitwise logical "and" of two supplied RAW values and returns the resulting RAW.

Syntax

UTL_RAW.BIT_AND (
   r1 IN RAW,
   r2 IN RAW) 
RETURN RAW;

Pragmas

pragma restrict_references(bit_and, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-2 BIT_AND function parameters

Parameter	Description
r1	RAW value to "and" with `r2`.
r2	RAW value to "and" with `r1`.

Return value

Table 11-3 BIT_AND function return value

Return	Description
RAW	Contains the "and" result of `r1` and `r2`.
NULL	If either `r1` or `r2` is NULL.

Usage notes

If r1 and r2 differ in length, the operation is terminated after the last byte of the shorter of the two RAW values, and the unprocessed portion of the longer RAW value is appended to the partial result. The resulting length equals that of the longer of the two input values.

BIT_COMPLEMENT function

This function performs bitwise logical "complement" of the supplied RAW value and returns the resulting RAW. The result length equals the input RAW length.

Syntax

UTL_RAW.BIT_COMPLEMENT (
   r IN RAW) 
  RETURN RAW;

Pragmas

pragma restrict_references(bit_complement, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-4 BIT_COMPLEMENT function parameters

Parameter	Description
r	RAW value for "complement" operation.

Return value

Table 11-5 BIT_COMPLEMENT function return value

Return	Description
RAW	The "complement" of `r`.
NULL	If `r` is NULL.

BIT_OR function

This function performs bitwise logical "or" of two supplied RAW values and returns the resulting RAW.

Syntax

UTL_RAW.BIT_OR (
   r1 IN RAW,
   r2 IN RAW) 
  RETURN RAW;

Pragmas

pragma restrict_references(bit_or, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-6 BIT_OR function parameters

Parameters	Description
r1	RAW value to "or" with `r2`.
r2	RAW value to "or" with `r1`.

Return value

Table 11-7 BIT_OR function return value

Return	Description
RAW	Contains the "or" result of `r1` and `r2`.
NULL	If either `r1` or `r2` is NULL.

Usage notes

BIT_XOR function

This function performs bitwise logical "exclusive or" of two supplied RAW values and returns the resulting RAW.

Syntax

UTL_RAW.BIT_XOR (
   r1 IN RAW,
   r2 IN RAW) 
  RETURN RAW;

Pragmas

pragma restrict_references(bit_xor, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-8 BIT_XOR function parameters

Parameter	Description
r1	RAW value to "xor" with `r2`.
r2	RAW value to "xor" with `r1`.

Return value

Table 11-9 BIT_XOR function return value

Return	Description
RAW	Contains the "xor" result of `r1` and `r2`.
NULL	If either `r1` or `r2` is NULL.

Usage notes

CAST_FROM_BINARY_DOUBLE function

This function returns the RAW binary representation of a BINARY_DOUBLE value.

Syntax

UTL_RAW.CAST_FROM_BINARY_DOUBLE(
   n          IN BINARY_DOUBLE,
   endianess  IN BINARY_INTEGER DEFAULT 1) 
RETURN RAW;

Pragmas

pragma restrict_references(cast_from_binary_double, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-10 CAST_FROM_BINARY_DOUBLE function parameters

Parameter	Description
n	The BINARY_DOUBLE value.
endianess	A BINARY_INTEGER value indicating the endianess. The function recognizes the defined constants `big_endian` (1), `little_endian` (2), and `machine_endian` (3). The default is `big_endian`.

Return value

The RAW binary representation of the BINARY_DOUBLE value, or NULL if the input is NULL.

Usage notes

An eight-byte BINARY_DOUBLE value maps to the IEEE 754 double-precision format as follows:

byte 0: bit 63 ~ bit 56
byte 1: bit 55 ~ bit 48
byte 2: bit 47 ~ bit 40
byte 3: bit 39 ~ bit 32
byte 4: bit 31 ~ bit 24
byte 5: bit 23 ~ bit 16
byte 6: bit 15 ~ bit  8
byte 7: bit  7 ~ bit  0

The parameter endianess specifies how the bytes of the BINARY_DOUBLE value are mapped to the bytes of the RAW value. In the following matrix, rb0 to rb7 refer to the bytes of the RAW and db0 to db7 refer to the bytes of the BINARY_DOUBLE.

rb0 rb1 rb2 rb3 rb4 rb5 rb6 rb7

big_endian db0 db1 db2 db3 db4 db5 db6 db7

little_endian db7 db6 db5 db4 db3 db2 db1 db0
When machine_endian is specified, the eight bytes of the BINARY_DOUBLE argument are copied straight across into the RAW return value. The effect is the same as if the user specified big_endian on a big-endian system or little_endian on a little-endian system.

	rb0	rb1	rb2	rb3	rb4	rb5	rb6	rb7
big_endian	db0	db1	db2	db3	db4	db5	db6	db7
little_endian	db7	db6	db5	db4	db3	db2	db1	db0

CAST_FROM_BINARY_FLOAT function

This function returns the RAW binary representation of a BINARY_FLOAT value.

Syntax

UTL_RAW.CAST_FROM_BINARY_FLOAT(
   n          IN BINARY_FLOAT,
   endianess  IN BINARY_INTEGER DEFAULT 1) 
RETURN RAW;

Pragmas

pragma restrict_references(cast_from_binary_float, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-11 CAST_FROM_BINARY_FLOAT function parameters

Parameter	Description
n	The BINARY_FLOAT value.
endianess	A BINARY_INTEGER value indicating the endianess. The function recognizes the defined constants `big_endian` (1), `little_endian` (2), and `machine_endian` (3). The default is `big_endian`.

Return value

The RAW binary representation of the BINARY_FLOAT value, or NULL if the input is NULL.

Usage notes

A four-byte BINARY_FLOAT value maps to the IEEE 754 single-precision format as follows:

byte 0: bit 31 ~ bit 24
byte 1: bit 23 ~ bit 16
byte 2: bit 15 ~ bit  8
byte 3: bit 7 ~  bit  0

The parameter endianess specifies how the bytes of the BINARY_FLOAT value are mapped to the bytes of the RAW value. In the following matrix, rb0 to rb3 refer to the bytes of the RAW and fb0 to fb3 refer to the bytes of the BINARY_FLOAT.

rb0 rb1 rb2 rb3

big_endian fbo fb1 fb2 fb3

little_endian fb3 fb2 fb1 fb0
When machine_endian is specified, the four bytes of the BINARY_FLOAT argument are copied straight across into the RAW return value. The effect is the same as if the user specified big_endian on a big-endian system or little_endian on a little-endian system.

	rb0	rb1	rb2	rb3
big_endian	fbo	fb1	fb2	fb3
little_endian	fb3	fb2	fb1	fb0

CAST_FROM_BINARY_INTEGER function

This function returns the RAW binary representation of a BINARY_INTEGER value.

Syntax

UTL_RAW.CAST_FROM_BINARY_INTEGER (
   n          IN BINARY_INTEGER
   endianess  IN BINARY_INTEGER DEFAULT 1) 
RETURN RAW;

Pragmas

pragma restrict_references(cast_from_binary_integer, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-12 CAST_FROM_BINARY_INTEGER function parameters

Parameter	Description
n	The BINARY_INTEGER value.
endianess	A BINARY_INTEGER value indicating the endianess. The function recognizes the defined constants `big_endian` (1), `little_endian` (2), and `machine_endian` (3). The default is `big_endian`.

Return value

The RAW binary representation of the BINARY_INTEGER value, or NULL if the input is NULL.

CAST_FROM_NUMBER function

This function returns the RAW binary representation of a NUMBER value.

Syntax

UTL_RAW.CAST_FROM_NUMBER (
   n  IN NUMBER)
 RETURN RAW;

Pragmas

pragma restrict_references(cast_from_number, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-13 CAST_FROM_NUMBER function parameters

Parameter	Description
n	The NUMBER value.

Return value

The RAW binary representation of the NUMBER value, or NULL if the input is NULL.

CAST_TO_BINARY_DOUBLE function

This function casts the RAW binary representation of a BINARY_DOUBLE value into a BINARY_DOUBLE.

Syntax

UTL_RAW.CAST_TO_BINARY_DOUBLE (
   r          IN RAW
   endianess  IN BINARY_INTEGER DEFAULT 1) 
RETURN BINARY_DOUBLE;

Pragmas

pragma restrict_references(cast_to_binary_double, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-14 CAST_TO_BINARY_DOUBLE function parameters

Parameter	Description
r	The RAW binary representation of a BINARY_DOUBLE.
endianess	A BINARY_INTEGER value indicating the endianess. The function recognizes the defined constants `big_endian` (1), `little_endian` (2), and `machine_endian` (3). The default is `big_endian`.

Return value

The BINARY_DOUBLE value, or NULL if the input is NULL.

Usage notes

If the RAW argument is more than eight bytes, only the first eight bytes are used and the rest of the bytes are ignored. If the result is -0, +0 is returned. If the result is NaN, the value BINARY_DOUBLE_NAN is returned.

An eight-byte BINARY_DOUBLE value maps to the IEEE 754 double-precision format as follows:

byte 0: bit 63 ~ bit 56
byte 1: bit 55 ~ bit 48
byte 2: bit 47 ~ bit 40
byte 3: bit 39 ~ bit 32
byte 4: bit 31 ~ bit 24
byte 5: bit 23 ~ bit 16
byte 6: bit 15 ~ bit  8
byte 7: bit  7 ~ bit  0

The parameter endianess specifies how the bytes of the BINARY_DOUBLE value are mapped to the bytes of the RAW value. In the following matrix, rb0 to rb7 refer to the bytes in RAW and db0 to db7 refer to the bytes in BINARY_DOUBLE.

rb0 rb1 rb2 rb3 rb4 rb5 rb6 rb7

big_endian db0 db1 db2 db3 db4 db5 db6 db7

little_endian db7 db6 db5 db4 db3 db2 db1 db0
When machine_endian is specified, the eight bytes of the RAW argument are copied straight across into the BINARY_DOUBLE return value. The effect is the same as if the user specified big_endian on a big-endian system or little_endian on a little-endian system.

	rb0	rb1	rb2	rb3	rb4	rb5	rb6	rb7
big_endian	db0	db1	db2	db3	db4	db5	db6	db7
little_endian	db7	db6	db5	db4	db3	db2	db1	db0

Exceptions

If the RAW argument is less than eight bytes, a VALUE_ERROR exception is raised.

CAST_TO_BINARY_FLOAT function

This function casts the RAW binary representation of a BINARY_FLOAT value into a BINARY_FLOAT.

Syntax

UTL_RAW.CAST_TO_BINARY_FLOAT (
   r          IN RAW
   endianess  IN BINARY_INTEGER DEFAULT 1) 
RETURN BINARY_FLOAT;

Pragmas

pragma restrict_references(cast_to_binary_float, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-15 CAST_TO_BINARY_FLOAT function parameters

Parameter	Description
r	The RAW binary representation of a BINARY_FLOAT.
endianess	A BINARY_INTEGER value indicating the endianess. The function recognizes the defined constants `big_endian` (1), `little_endian` (2), and `machine_endian` (3). The default is `big_endian`.

Return value

The BINARY_FLOAT value, or NULL if the input is NULL.

Usage notes

If the RAW argument is more than four bytes, only the first four bytes are used and the rest of the bytes are ignored. If the result is -0, +0 is returned. If the result is NaN, the value BINARY_FLOAT_NAN is returned.

A four-byte BINARY_FLOAT value maps to the IEEE 754 single-precision format as follows:

byte 0: bit 31 ~ bit 24
byte 1: bit 23 ~ bit 16
byte 2: bit 15 ~ bit  8
byte 3: bit 7 ~  bit  0

The parameter endianess specifies how the bytes of the BINARY_FLOAT value are mapped to the bytes of the RAW value. In the following matrix, rb0 to rb3 refer to the bytes in RAW and fb0 to fb3 refer to the bytes in BINARY_FLOAT.

rb0 rb1 rb2 rb3

big_endian fbo fb1 fb2 fb3

little_endian fb3 fb2 fb1 fb0
When machine_endian is specified, the four bytes of the RAW argument are copied straight across into the BINARY_FLOAT return value. The effect is the same as if the user specified big_endian on a big-endian system or little_endian on a little-endian system.

	rb0	rb1	rb2	rb3
big_endian	fbo	fb1	fb2	fb3
little_endian	fb3	fb2	fb1	fb0

Exceptions

If the RAW argument is less than four bytes, a VALUE_ERROR exception is raised.

CAST_TO_BINARY_INTEGER function

This function casts the RAW binary representation of a BINARY_INTEGER value into a BINARY_INTEGER.

Syntax

UTL_RAW.CAST_TO_BINARY_INTEGER (
   r          IN RAW
   endianess  IN BINARY_INTEGER DEFAULT 1) 
RETURN BINARY_INTEGER;

Pragmas

pragma restrict_references(cast_to_binary_integer, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-16 CAST_TO_BINARY_INTEGER function parameters

Parameter	Description
r	The RAW binary representation of a BINARY_INTEGER.
endianess	A BINARY_INTEGER value indicating the endianess. The function recognizes the defined constants `big_endian` (1), `little_endian` (2), and `machine_endian` (3). The default is `big_endian`.

Return value

The BINARY_INTEGER value, or NULL if the input is NULL.

CAST_TO_NUMBER function

This function casts the RAW binary representation of a NUMBER value into a NUMBER.

Syntax

UTL_RAW.CAST_TO_NUMBER (
   r  IN RAW) 
 RETURN NUMBER;

Pragmas

pragma restrict_references(cast_to_number, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-17 CAST_TO_NUMBER function parameters

Parameter	Description
r	The RAW binary representation of a NUMBER.

Return value

The NUMBER value, or NULL if the input is NULL.

CAST_TO_NVARCHAR2 function

This function converts a RAW value represented using n data bytes into an NVARCHAR2 value with n data bytes.

Note:

When casting to NVARCHAR2, the current Globalization Support character set is used for the characters within that NVARCHAR2 value.

Syntax

UTL_RAW.CAST_TO_NVARCHAR2 (
   r IN RAW) 
RETURN NVARCHAR2;

Pragmas

pragma restrict_references(cast_to_NVARCHAR2, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-18 CAST_TO_NVARCHAR2 function parameters

Parameter	Description
r	RAW value, without leading length field, to be changed to an NVARCHAR2 value.

Return value

Table 11-19 CAST_TO_NVARCHAR2 function return value

Return	Description
NVARCHAR2	Containing the data converted from the input RAW.
NULL	If `r` is NULL.

CAST_TO_RAW function

This function converts a VARCHAR2 value represented using n data bytes into a RAW value with n data bytes. The data is not modified in any way. Only its data type is recast to a RAW data type.

Syntax

UTL_RAW.CAST_TO_RAW (
   c  IN VARCHAR2) 
RETURN RAW;

Pragmas

pragma restrict_references(cast_to_raw, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-20 CAST_TO_RAW function parameters

Parameter	Description
c	VARCHAR2 value to be changed to a RAW value.

Return values

Table 11-21 CAST_TO_RAW function return values

Return	Description
RAW	Containing the data converted from the input VARCHAR2, with the same byte-length as the input value but without a leading length field.
NULL	If `c` is NULL.

CAST_TO_VARCHAR2 function

This function converts a RAW value represented using n data bytes into a VARCHAR2 value with n data bytes.

Note:

When casting to VARCHAR2, the current Globalization Support character set is used for the characters within that VARCHAR2 value.

Syntax

UTL_RAW.CAST_TO_VARCHAR2 (
   r IN RAW) 
RETURN VARCHAR2;

Pragmas

pragma restrict_references(cast_to_VARCHAR2, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-22 CAST_TO_VARCHAR2 function parameters

Parameter	Description
r	RAW value, without leading length field, to be changed to a VARCHAR2 value.

Return value

Table 11-23 CAST_TO_VARCHAR2 function return value

Return	Description
VARCHAR2	Containing the data converted from the input RAW.
NULL	If `r` is NULL.

COMPARE function

This function compares two RAW values. If they differ in length, then the shorter is extended on the right according to the optional pad parameter.

Syntax

UTL_RAW.COMPARE (
   r1  IN RAW,
   r2  IN RAW,
   pad IN RAW DEFAULT NULL) 
  RETURN NUMBER;

Pragmas

pragma restrict_references(compare, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-24 COMPARE function parameters

Parameter	Description
r1	First RAW value to be compared. It can be NULL or zero-length.
r2	Second RAW value to be compared. It can be NULL or zero-length.
pad	Byte to extend whichever of `r1` or `r2` is shorter. This is optional. The default is `x'00'`.

Return value

Table 11-25 COMPARE function return value

Return	Description
NUMBER	Equals 0 if the RAW byte-strings are identical or both NULL, or equals the position (numbered from 1) of the first mismatched byte.

CONCAT function

This function concatenates up to 12 RAW values into a single RAW value. If the concatenated size exceeds 32K, an error is returned.

Syntax

UTL_RAW.CONCAT (  
   r1  IN RAW DEFAULT NULL,
   r2  IN RAW DEFAULT NULL,
   r3  IN RAW DEFAULT NULL,
   r4  IN RAW DEFAULT NULL,
   r5  IN RAW DEFAULT NULL,
   r6  IN RAW DEFAULT NULL,
   r7  IN RAW DEFAULT NULL,
   r8  IN RAW DEFAULT NULL,
   r9  IN RAW DEFAULT NULL,
   r10 IN RAW DEFAULT NULL,
   r11 IN RAW DEFAULT NULL,
   r12 IN RAW DEFAULT NULL) 
  RETURN RAW;

Pragmas

pragma restrict_references(concat, WNDS, RNDS, WNPS, RNPS);

Parameters

Items r1...r12 are the RAW items to concatenate.

Return value

Table 11-26 CONCAT function return values

Return	Description
RAW	Containing the items concatenated.

Exceptions

There is an error if the sum of the lengths of the inputs exceeds the maximum allowable length for a RAW value, which is 32767 bytes.

CONVERT function

This function converts a RAW value from one character set to another and returns the resulting RAW value.

Both character sets must be supported character sets defined to the TimesTen database.

Syntax

UTL_RAW.CONVERT (
   r            IN RAW,
   to_charset   IN VARCHAR2,
   from_charset IN VARCHAR2) 
  RETURN RAW;

Pragmas

pragma restrict_references(convert, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-27 CONVERT function parameters

Parameter	Description
r	RAW byte-string to be converted.
to_charset	Name of Globalization Support character set to which `r` is converted.
from_charset	Name of Globalization Support character set in which `r` is supplied.

Return value

Table 11-28 CONVERT function return value

Return	Description
RAW	Byte-string `r` converted according to the specified character sets.

Exceptions

Table 11-29 CONVERT function exceptions

Error Description

Error	Description
VALUE_ERROR	Occurs under any of the following circumstances: If `r` is missing, NULL, or zero-length. If `from_charset` or `to_charset` is missing, NULL, or zero-length. If `from_charset` or `to_charset` is invalid or unsupported.

VALUE_ERROR

Occurs under any of the following circumstances:

If r is missing, NULL, or zero-length.
If from_charset or to_charset is missing, NULL, or zero-length.
If from_charset or to_charset is invalid or unsupported.

COPIES function

This function returns a specified number of copies of a specified RAW value, concatenated together.

Syntax

UTL_RAW.COPIES (
   r IN RAW,
   n IN NUMBER) 
  RETURN RAW;

Pragmas

pragma restrict_references(copies, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-30 COPIES function parameters

Parameters	Description
r	RAW value to be copied.
n	Number of times to copy the RAW value (must be positive).

Return value

This returns the RAW value copied n times and concatenated.

Exceptions

Table 11-31 COPIES function exceptions

Error Description

Error	Description
VALUE_ERROR	Occurs under any of the following circumstances: If r is missing, NULL, or zero-length. If `n <= 0`. If the length of the result exceeds the maximum allowable length for a RAW value, which is 32767 bytes.

VALUE_ERROR

Occurs under any of the following circumstances:

If r is missing, NULL, or zero-length.
If n <= 0.
If the length of the result exceeds the maximum allowable length for a RAW value, which is 32767 bytes.

LENGTH function

This function returns the length in bytes of a RAW value.

Syntax

UTL_RAW.LENGTH (
   r  IN RAW) 
RETURN NUMBER;

Pragmas

pragma restrict_references(length, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-32 LENGTH function parameters

Parameter	Description
r	The RAW byte-stream to be measured.

Return value

Table 11-33 LENGTH function return value

Return	Description
NUMBER	The length of the RAW value, in bytes.

OVERLAY function

This function overlays the specified portion of a target RAW value with an overlay RAW value, starting from a specified byte position and proceeding for a specified number of bytes.

Syntax

UTL_RAW.OVERLAY (
   overlay_str IN RAW,
   target      IN RAW,
   pos         IN BINARY_INTEGER DEFAULT 1,
   len         IN BINARY_INTEGER DEFAULT NULL,
   pad         IN RAW            DEFAULT NULL) 
  RETURN RAW;

Pragmas

pragma restrict_references(overlay, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-34 OVERLAY function parameters

Parameters	Description
overlay_str	Byte-string used to overlay target.
target	Target byte-string to be overlaid.
pos	Byte position in target at which to start overlay (numbered from 1). This is optional. The default is 1.
len	The number of bytes to overlay. This is optional. The default is the length of `overlay_str`.
pad	Pad byte used when `len` exceeds `overlay_str` length or `pos` exceeds `target` length. This is optional. The default is `x'00'`.

Return value

Table 11-35 OVERLAY function return value

Return	Description
RAW	The target byte value overlaid as specified.

Usage notes

If overlay_str has less than len bytes, then it is extended to len bytes using the pad byte. If overlay_str exceeds len bytes, then the extra bytes in overlay_str are ignored. If len bytes beginning at position pos of target exceeds the length of target, then target is extended to contain the entire length of overlay_str.

If len is specified, it must be greater than or equal to 0. If pos is specified, it must be greater than or equal to 1. If pos exceeds the length of target, then target is padded with pad bytes to position pos, and target is further extended with overlay_str bytes.

Exceptions

Table 11-36 OVERLAY function exceptions

Error Description

Error	Description
VALUE_ERROR	Occurs under any of the following circumstances: If `overlay_str` is NULL or zero-length. If `target` is missing or undefined. If the length of `target` exceeds the maximum length for a RAW value, 32767 bytes. If `len < 0`. If `pos <= 0`.

VALUE_ERROR

Occurs under any of the following circumstances:

If overlay_str is NULL or zero-length.
If target is missing or undefined.
If the length of target exceeds the maximum length for a RAW value, 32767 bytes.
If len < 0.
If pos <= 0.

REVERSE function

This function reverses a RAW byte-sequence from end to end. For example, x'0102F3' would be reversed to x'F30201', and 'xyz' would be reversed to 'zyx'. The result length is the same as the input length.

Syntax

UTL_RAW.REVERSE (
   r IN RAW) 
  RETURN RAW;

Pragmas

pragma restrict_references(reverse, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-37 REVERSE function parameters

Parameter	Description
r	RAW value to reverse.

Return value

Table 11-38 REVERSE function return value

Return	Description
RAW	Containing the reverse of `r`.

Exceptions

Table 11-39 REVERSE function exceptions

Error	Description
VALUE_ERROR	Occurs if `r` is NULL or zero-length.

SUBSTR function

This function returns a substring of a RAW value for a specified number of bytes and starting position.

Syntax

UTL_RAW.SUBSTR (
   r   IN RAW,
   pos IN BINARY_INTEGER,
   len IN BINARY_INTEGER DEFAULT NULL) 
  RETURN RAW;

Pragmas

pragma restrict_references(substr, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-40 SUBSTR function parameters

Parameter	Description
r	The RAW byte-string from which the substring is extracted.
pos	The byte position in `r` at which to begin extraction, either counting forward from the beginning of `r` (positive value) or backward from the end of `r` (negative value).
len	The number of bytes, beginning at `pos` and proceeding toward the end of `r`, to extract from `r`. This is optional. The default is to the end of `r`.

Return value

Table 11-41 SUBSTR function return value

Return	Description
RAW	The RAW substring beginning at `pos` for `len` bytes.
NULL	If the input parameter was NULL.

Usage notes

If pos is positive, SUBSTR counts from the beginning of r to find the first byte. If pos is negative, SUBSTR counts backward from the end of r. The value of pos cannot equal 0.

A specified value of len must be positive. If len is omitted, SUBSTR returns all bytes to the end of r.

Exceptions

Table 11-42 SUBSTR function exceptions

Error Description

Error	Description
VALUE_ERROR	Occurs under any of the following circumstances: If `pos` equals 0 or is greater than the length of `r`. If `len` is less than or equal to 0. If `len` is greater than (length of `r`) minus (`pos-1`).

VALUE_ERROR

Occurs under any of the following circumstances:

If pos equals 0 or is greater than the length of r.
If len is less than or equal to 0.
If len is greater than (length of r) minus (pos-1).

Examples

Example 1

This example counts backward 15 bytes from the end of the input RAW for its starting position, then takes a substring of five bytes starting at that point.

declare
  sr raw(32767); 
  r raw(32767);
begin
  sr       := hextoraw('1236567812125612344434341234567890ABAA1234');
  r := UTL_RAW.SUBSTR(sr, -15, 5);
  dbms_output.put_line('source raw: ' || sr);
  dbms_output.put_line('return raw: ' || r);
end;
/

The result is as follows:

source raw: 1236567812125612344434341234567890ABAA1234
return raw: 5612344434
 
PL/SQL procedure successfully completed.

Here the input and output are presented, for purposes of this discussion, in a way that gives a clearer indication of the functionality:

source raw: 12 36 56 78 12 12 56 12 34 44 34 34 12 34 56 78 90 AB AA 12 34
return raw: 56 12 34 44 34

The substring starts at the 15th byte from the end.

Example 2

This example has the same input RAW and starting point as the preceding example, but because len is not specified the substring is taken from the starting point to the end of the input.

declare
  sr raw(32767); 
  r raw(32767);
begin
  sr       := hextoraw('1236567812125612344434341234567890ABAA1234');
  r := UTL_RAW.SUBSTR(sr, -15);
  dbms_output.put_line('source raw: ' || sr);
  dbms_output.put_line('return raw: ' || r);
end;
/

Here is the result:

source raw: 1236567812125612344434341234567890ABAA1234
return raw: 5612344434341234567890ABAA1234

Here the input and output are presented, for purposes of this discussion, in a way that gives a clearer indication of the functionality:

source raw: 12 36 56 78 12 12 56 12 34 44 34 34 12 34 56 78 90 AB AA 12 34
return raw: 56 12 34 44 34 34 12 34 56 78 90 AB AA 12 34

TRANSLATE function

This function performs a byte-by-byte translation of a RAW value, given an input set of bytes, a set of bytes to search for in the input bytes and translate from, and a set of corresponding bytes to translate to. Whenever a byte in the specified from_set is found in the input RAW value, it is translated to the corresponding byte in the to_set for the output RAW value, or it is simply not included in the output RAW value if there is no corresponding byte in to_set. Any bytes in the input RAW value that do not appear in from_set are simply copied as-is to the output RAW value.

Syntax

UTL_RAW.TRANSLATE (
   r        IN RAW,
   from_set IN RAW,
   to_set   IN RAW) 
  RETURN RAW;

Note:

Be aware that to_set and from_set are reversed in the calling sequence compared to TRANSLITERATE.

Pragmas

pragma restrict_references(translate, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-43 TRANSLATE function parameters

Parameter	Description
r	RAW source byte-string whose bytes are to be translated, as applicable.
from_set	RAW byte-codes that are searched for in `r`. Where found, they are translated in the result.
to_set	RAW byte-codes to translate to. Where a `from_set` byte is found in `r`, it is translated in the result to the corresponding `to_set` byte, as applicable.

Return value

Table 11-44 TRANSLATE function return value

Return	Description
RAW	Translated byte-string.

Usage notes

If to_set is shorter than from_set, the extra from_set bytes have no corresponding translation bytes. Bytes from the input RAW that match any such from_set bytes are not translated or included in the result. They are effectively translated to NULL.
If to_set is longer than from_set, the extra to_set bytes are ignored.
If a byte value is repeated in from_set, the repeated occurrence is ignored.

Note:

Differences from TRANSLITERATE:

The from_set parameter comes before the to_set parameter in the calling sequence.
Bytes from r that appear in from_set but have no corresponding values in to_set are not translated or included in the result.
The resulting RAW value may be shorter than the input RAW value.

Note that TRANSLATE and TRANSLITERATE only differ in functionality when to_set has fewer bytes than from_set.

Exceptions

Table 11-45 TRANSLATE function exceptions

Error	Description
VALUE_ERROR	Occurs if `r`, `from_set`, or `to_set` is NULL or zero-length.

Examples

Example 1

In this example, from_set is x'12AA34' and to_set is x'CD'. Wherever '12' appears in the input RAW it will be replaced by 'CD' in the result. Wherever 'AA' or '34' appears in the input RAW, because there are no corresponding bytes in to_set those bytes will simply not be included in the result (effectively translated to NULL).

You can compare this to "Examples" in the TRANSLITERATE section to see how the functions differ.

declare
  sr raw(32767);     
  from_set raw(32767);
  to_set raw(32767); 
  r raw(32767);
begin
  sr       := hextoraw('1236567812125612344434341234567890ABAA1234');
  from_set := hextoraw('12AA34');
  to_set   := hextoraw('CD');
  dbms_output.put_line('from_set:   ' || from_set);
  dbms_output.put_line('to_set:     ' || to_set);
  r := UTL_RAW.TRANSLATE(sr, from_set, to_set);
  dbms_output.put_line('source raw: ' || sr);
  dbms_output.put_line('return raw: ' || r);
end;
/

The result is as follows:

from_set:   12AA34
to_set:     CD
source raw: 1236567812125612344434341234567890ABAA1234
return raw: CD365678CDCD56CD44CD567890ABCD

PL/SQL procedure successfully completed.

Here the inputs and output are presented, for purposes of this discussion, in a way that gives a clearer indication of the functionality:

from_set:   12  AA 34
to_set:     CD
source raw: 12 365678 12 12 56 12 34 44 34 34 12 34 567890AB AA 12 34
return raw: CD 365678 CD CD 56 CD    44       CD    567890AB    CD

Example 2

In this example, the from_set is x'12AA12' and the to_set is x'CDABEF'. Wherever '12' appears in the input RAW it will be replaced by 'CD' in the result. Wherever 'AA' appears in the input it will be replaced by 'AB' in the result. The second '12' in from_set is ignored, and therefore the corresponding byte in to_set is ignored as well.

declare
  sr raw(32767);     
  from_set raw(32767);
  to_set raw(32767); 
  r raw(32767);
begin
  sr       := hextoraw('1236567812125612344434341234567890ABAA1234');
  from_set := hextoraw('12AA12');
  to_set   := hextoraw('CDABEF');
  dbms_output.put_line('from_set:   ' || from_set);
  dbms_output.put_line('to_set:     ' || to_set);
  r := UTL_RAW.TRANSLATE(sr, from_set, to_set);
  dbms_output.put_line('source raw: ' || sr);
  dbms_output.put_line('return raw: ' || r);
end;
/

The result is as follows. Note this is the same behavior as for TRANSLITERATE with the same input RAW, from_set, and to_set, as shown in "Examples".

from_set:   12AA12
to_set:     CDABEF
source raw: 1236567812125612344434341234567890ABAA1234
return raw: CD365678CDCD56CD34443434CD34567890ABABCD34
 
PL/SQL procedure successfully completed.

TRANSLITERATE function

This function performs a byte-by-byte transliteration of a RAW value, given an input set of bytes, a set of bytes to search for in the input bytes and convert from, and a set of corresponding bytes to convert to. Whenever a byte in the specified from_set is found in the input RAW value, it is converted to the corresponding byte in the to_set for the output RAW value, or it is converted to the specified "padding" byte if there is no corresponding byte in to_set. Any bytes in the input RAW value that do not appear in from_set are simply copied as-is to the output RAW value.

Syntax

UTL_RAW.TRANSLITERATE (
   r        IN RAW,
   to_set   IN RAW DEFAULT NULL,
   from_set IN RAW DEFAULT NULL,
   pad      IN RAW DEFAULT NULL)
  RETURN RAW;

Note:

Be aware that to_set and from_set are reversed in the calling sequence compared to TRANSLATE.

Pragmas

pragma restrict_references(transliterate, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-46 TRANSLITERATE function parameters

Parameter	Description
r	RAW source byte-string whose bytes are to be converted, as applicable.
to_set	RAW byte-codes to convert to. Where a `from_set` byte is found in `r`, it is converted in the result to the corresponding `to_set` byte, as applicable. This defaults to a NULL string effectively extended with `pad` to the length of `from_set`, as necessary.
from_set	RAW byte-codes that are searched for in `r`. Where found, they are converted in the result. The default is `x'00'` through `x'FF'`, which results in all bytes in `r` being converted in the result.
pad	A "padding" byte that is used as the conversion value for any byte in `r` for which there is a matching byte in `from_set` that does not have a corresponding byte in `to_set`. The default is `x'00'`.

Return value

Table 11-47 TRANSLITERATE function return value

Return	Description
RAW	Converted byte-string.

Usage notes

If to_set is shorter than from_set, the extra from_set bytes have no corresponding conversion bytes. Bytes from the input RAW that match any such from_set bytes are converted in the result to the pad byte instead.
If to_set is longer than from_set, the extra to_set bytes are ignored.
If a byte value is repeated in from_set, the repeated occurrence is ignored.

Note:

Differences from TRANSLATE:

The to_set parameter comes before the from_set parameter in the calling sequence.
Bytes from r that appear in from_set but have no corresponding values in to_set are replaced by pad in the result.
The resulting RAW value always has the same length as the input RAW value.

Note that TRANSLATE and TRANSLITERATE only differ in functionality when to_set has fewer bytes than from_set.

Exceptions

Table 11-48 TRANSLITERATE function exceptions

Error	Description
VALUE_ERROR	Occurs if `r` is NULL or zero-length.

Examples

Example 1

In this example, the from_set is x'12AA34' and the to_set is x'CD'. Wherever '12' appears in the input RAW it will be replaced by 'CD' in the result. Wherever 'AA' or '34' appears in the input RAW, because there are no corresponding bytes in to_set those bytes will replaced by the pad byte, which is not specified and therefore defaults to x'00'.

You can compare this to "Examples" in the TRANSLATE section to see how the functions differ.

declare
  sr raw(32767);
  from_set raw(32767);
  to_set raw(32767); 
  r raw(32767);
begin
  sr       := hextoraw('1236567812125612344434341234567890ABAA1234');
  from_set := hextoraw('12AA34');
  to_set   := hextoraw('CD');
  dbms_output.put_line('from_set:   ' || from_set);
  dbms_output.put_line('to_set:     ' || to_set);
  r := UTL_RAW.TRANSLITERATE(sr, to_set, from_set);
  dbms_output.put_line('source raw: ' || sr);
  dbms_output.put_line('return raw: ' || r);
end;
/

The result is as follows:

from_set:   12AA34
to_set:     CD
source raw: 1236567812125612344434341234567890ABAA1234
return raw: CD365678CDCD56CD00440000CD00567890AB00CD00
 
PL/SQL procedure successfully completed.

Here the inputs and output are presented, for purposes of this discussion, in a way that gives a clearer indication of the functionality:

from_set:   12  AA 34
to_set:     CD
source raw: 12 365678 12 12 56 12 34 44 34 34 12 34 567890AB AA 12 34
return raw: CD 365678 CD CD 56 CD 00 44 00 00 CD 00 567890AB 00 CD 00

Example 2

This is the same as the preceding example, except pad is specified to be x'FF'.

declare
  sr raw(32767);     
  from_set raw(32767);
  to_set raw(32767); 
  pad raw(32767);
  r raw(32767);
begin
  sr       := hextoraw('1236567812125612344434341234567890ABAA1234');
  from_set := hextoraw('12AA34');
  to_set   := hextoraw('CD');
  pad      := hextoraw('FF');
  dbms_output.put_line('from_set:   ' || from_set);
  dbms_output.put_line('to_set:     ' || to_set);
  r := UTL_RAW.TRANSLITERATE(sr, to_set, from_set, pad);
  dbms_output.put_line('source raw: ' || sr);
  dbms_output.put_line('return raw: ' || r);
end;
/

The result is as follows. 'AA' and '34' are replaced by 'FF' instead of '00'.

from_set:   12AA34
to_set:     CD
source raw: 1236567812125612344434341234567890ABAA1234
return raw: CD365678CDCD56CDFF44FFFFCDFF567890ABFFCDFF
 
PL/SQL procedure successfully completed.

Example 3

declare
  sr raw(32767);     
  from_set raw(32767);
  to_set raw(32767); 
  r raw(32767);
begin
  sr       := hextoraw('1236567812125612344434341234567890ABAA1234');
  from_set := hextoraw('12AA12');
  to_set   := hextoraw('CDABEF');
  dbms_output.put_line('from_set:   ' || from_set);
  dbms_output.put_line('to_set:     ' || to_set);
  r := UTL_RAW.TRANSLITERATE(sr, to_set, from_set);
  dbms_output.put_line('source raw: ' || sr);
  dbms_output.put_line('return raw: ' || r);
end;
/

The result is as follows. Note this is the same behavior as for TRANSLATE with the same input RAW, from_set, and to_set, as shown in "Examples".

from_set:   12AA12
to_set:     CDABEF
source raw: 1236567812125612344434341234567890ABAA1234
return raw: CD365678CDCD56CD34443434CD34567890ABABCD34
 
PL/SQL procedure successfully completed.

Example 4

In this example, from_set and to_set are not specified.

declare
  sr raw(32767);
  r raw(32767);
begin
  sr       := hextoraw('1236567812125612344434341234567890ABAA1234');
  r := UTL_RAW.TRANSLITERATE(sr);
  dbms_output.put_line('source raw: ' || sr);
  dbms_output.put_line('return raw: ' || r);
end;
/

The result is as follows. According to the from_set and to_set defaults, all bytes are replaced by x'00'.

source raw: 1236567812125612344434341234567890ABAA1234
return raw: 000000000000000000000000000000000000000000
 
PL/SQL procedure successfully completed.

XRANGE function

This function returns a RAW value containing the succession of one-byte encodings beginning and ending with the specified byte-codes. The specified byte-codes must be single-byte RAW values. If the start_byte value is greater than the end_byte value, then the succession of resulting bytes begins with start_byte, wraps through x'FF' back to x'00', then ends at end_byte.

Syntax

UTL_RAW.XRANGE (
   start_byte IN RAW DEFAULT NULL,
   end_byte   IN RAW DEFAULT NULL) 
  RETURN RAW;

Pragmas

pragma restrict_references(xrange, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 11-49 XRANGE function parameters

Parameters	Description
start_byte	Beginning byte-code value for resulting sequence. The default is `x'00'`.
end_byte	Ending byte-code value for resulting sequence. The default is `x'FF'`.

Return value

Table 11-50 XRANGE function return value

Return	Description
RAW	Containing succession of one-byte encodings.

Examples

The following three examples show the results where start_byte is less than end_byte, start_byte is greater than end_byte, and default values are used.

Command> declare
       >    r raw(32767);
       >    s raw(32767);
       >    e raw(32767);
       > begin
       >    s := hextoraw('1');
       >    e := hextoraw('A');
       >    r := utl_raw.xrange(s,e);
       >    dbms_output.put_line(r);
       > end;
       > /
0102030405060708090A
 
PL/SQL procedure successfully completed.
 
Command> declare
       >    r raw(32767);
       >    s raw(32767);
       >    e raw(32767);
       > begin
       >    s := hextoraw('EE');
       >    e := hextoraw('A');
       >    r := utl_raw.xrange(s,e);
       >    dbms_output.put_line(r);
       > end;
       > /
EEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF000102030405060708090A
 
PL/SQL procedure successfully completed.
 
Command> declare
       >    r raw(32767);
       > begin
       >    r := utl_raw.xrange();
       >    dbms_output.put_line(r);
       > end;
       > /
000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F2021222324252627
28292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F404142434445464748494A4B4C4D4E4F
505152535455565758595A5B5C5D5E5F606162636465666768696A6B6C6D6E6F7071727374757677
78797A7B7C7D7E7F808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9F
A0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7
C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDFE0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF
F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF
 
PL/SQL procedure successfully completed.