1. PL/SQL Packages Reference
  2. UTL_RAW

13 UTL_RAW

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

This chapter contains the following topics:

Using UTL_RAW

Overview

This package is necessary because 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 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. When the RAW data type is used, character set conversion is not 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.

UTL_RAW Subprograms

Table 13-1 summarizes the UTL_RAW subprograms, followed by a full description of each subprogram.

Table 13-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 XOR ("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

Casts a RAW value into an NVARCHAR2 value.

CAST_TO_RAW Function

Casts a VARCHAR2 value into a RAW value.

CAST_TO_VARCHAR2 Function

Casts a RAW value into a VARCHAR2 value.

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:

  • 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;

Parameters

Table 13-2 BIT_AND Function Parameters

Parameter Description

r1

First RAW value for AND operation

r2

Second RAW value for AND operation

Return Value

Result of the AND operation, or NULL if either input value 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;

Parameters

Table 13-3 BIT_COMPLEMENT Function Parameters

Parameter Description

r

RAW value for COMPLEMENT operation

Return Value

Result of the COMPLEMENT operation, or NULL if the input value 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;

Parameters

Table 13-4 BIT_OR Function Parameters

Parameters Description

r1

First RAW value for OR operation

r2

Second RAW value for OR operation

Return Value

Result of the OR operation, or NULL if either input value 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_XOR Function

This function performs bitwise logical XOR ("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;

Parameters

Table 13-5 BIT_XOR Function Parameters

Parameter Description

r1

First RAW value for XOR operation

r2

Second RAW value for XOR operation

Return Value

Result of the XOR operation, or NULL if either input value 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.

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;

Parameters

Table 13-6 CAST_FROM_BINARY_DOUBLE Function Parameters

Parameter Description

n

The BINARY_DOUBLE value

endianess

BINARY_INTEGER value indicating the endianess

The function recognizes the defined constants big_endian, little_endian, and machine_endian. The default is big_endian.

Return Value

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

  • 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.

    Endianess 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.

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;

Parameters

Table 13-7 CAST_FROM_BINARY_FLOAT Function Parameters

Parameter Description

n

The BINARY_FLOAT value

endianess

BINARY_INTEGER value indicating the endianess

The function recognizes the defined constants big_endian, little_endian, and machine_endian. The default is big_endian.

Return Value

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.

    Endianess 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.

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;

Parameters

Table 13-8 CAST_FROM_BINARY_INTEGER Function Parameters

Parameter Description

n

The BINARY_INTEGER value

endianess

BINARY_INTEGER value indicating the endianess

The function recognizes the defined constants big_endian, little_endian, and machine_endian. The default is big_endian.

Return Value

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;

Parameters

Table 13-9 CAST_FROM_NUMBER Function Parameters

Parameter Description

n

The NUMBER value

Return Value

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 value.

Syntax

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

Parameters

Table 13-10 CAST_TO_BINARY_DOUBLE Function Parameters

Parameter Description

r

RAW binary representation of a BINARY_DOUBLE value

endianess

BINARY_INTEGER value indicating the endianess

The function recognizes the defined constants big_endian, little_endian, and machine_endian. 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.

    Endianess 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.

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 value.

Syntax

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

Parameters

Table 13-11 CAST_TO_BINARY_FLOAT Function Parameters

Parameter Description

r

RAW binary representation of a BINARY_FLOAT value

endianess

BINARY_INTEGER value indicating the endianess

The function recognizes the defined constants big_endian, little_endian, and machine_endian. 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.

    Endianess 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.

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 value.

Syntax

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

Parameters

Table 13-12 CAST_TO_BINARY_INTEGER Function Parameters

Parameter Description

r

RAW binary representation of a BINARY_INTEGER value

endianess

BINARY_INTEGER value indicating the endianess

The function recognizes the defined constants big_endian, little_endian, and machine_endian. 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 value.

Syntax

UTL_RAW.CAST_TO_NUMBER (
   r  IN RAW) 
 RETURN NUMBER;

Parameters

Table 13-13 CAST_TO_NUMBER Function Parameters

Parameter Description

r

RAW binary representation of a NUMBER value

Return Value

The NUMBER value, or NULL if the input is NULL

CAST_TO_NVARCHAR2 Function

This function casts a RAW value represented using some number of data bytes into an NVARCHAR2 value with that number of 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;

Parameters

Table 13-14 CAST_TO_NVARCHAR2 Function Parameters

Parameter Description

r

RAW value, without leading length field, to be changed to an NVARCHAR2 value

Return Value

Data converted from the input RAW value, or NULL if the input is NULL

CAST_TO_RAW Function

This function casts a VARCHAR2 value represented using some number of data bytes into a RAW value with that number of data bytes. The data itself is not modified in any way, but its data type is recast to a RAW data type.

Syntax

UTL_RAW.CAST_TO_RAW (
   c  IN VARCHAR2) 
RETURN RAW;

Parameters

Table 13-15 CAST_TO_RAW Function Parameters

Parameter Description

c

VARCHAR2 value to be changed to a RAW value

Return Values

Data converted from the input VARCHAR2 value, with the same byte-length as the input value but without a leading length field, or NULL if the input is NULL

CAST_TO_VARCHAR2 Function

This function casts a RAW value represented using some number of data bytes into a VARCHAR2 value with that number of 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;

Parameters

Table 13-16 CAST_TO_VARCHAR2 Function Parameters

Parameter Description

r

RAW value, without leading length field, to be changed to a VARCHAR2 value

Return Value

Data converted from the input RAW value, or NULL if the input 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;

Parameters

Table 13-17 COMPARE Function Parameters

Parameter Description

r1

First RAW value to be compared

Note: The value can be NULL or zero-length.

r2

Second RAW value to be compared

Note: The value can be NULL or zero-length.

pad

Byte to extend whichever of the input values is shorter (default x'00')

Return Value

A NUMBER value that equals the position number (numbered from 1) of the first mismatched byte when comparing the two input values, or 0 if the input values are identical or both NULL

CONCAT Function

This function concatenates up to 12 RAW values into a single RAW value. If the concatenated size exceeds 32 KB, 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;

Parameters

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

Return Value

RAW value consisting of the concatenated input values

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 database.

Syntax

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

Parameters

Table 13-18 CONVERT Function Parameters

Parameter Description

r

RAW byte-string to be converted

to_charset

Name of Globalization Support character set to which the input value is converted

from_charset

Name of Globalization Support character set from which the input value is converted

Return Value

Converted byte-string according to the specified character set

Exceptions

VALUE_ERROR occurs under any of the following circumstances:

  • The input byte-string is missing, NULL, or zero-length.

  • The from_charset or to_charset parameter is missing, NULL, or zero-length.

  • The from_charset or to_charset parameter is invalid or unsupported.

COPIES Function

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

Syntax

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

Parameters

Table 13-19 COPIES Function Parameters

Parameters Description

r

RAW value to be copied

n

Number of times to copy the RAW value

Note: This must be a positive value.

Return Value

RAW value copied the specified number of times and concatenated

Exceptions

VALUE_ERROR occurs under any of the following circumstances:

  • The value to be copied is missing, NULL, or zero-length.

  • The number of times to copy the value is less than or equal to 0.

  • 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;

Parameters

Table 13-20 LENGTH Function Parameters

Parameter Description

r

RAW byte-stream to be measured

Return Value

NUMBER value indicating 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, 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;

Parameters

Table 13-21 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 (default 1)

len

Number of bytes to overlay (default: length of overlay_str)

pad

Pad byte used when len exceeds overlay_str length or pos exceeds target length (default x'00')

Return Value

RAW 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 exceed 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

VALUE_ERROR occurs under any of the following circumstances:

  • The overlay_str is NULL or zero-length.

  • The target is missing or undefined.

  • The length of target exceeds the maximum length for a RAW value, 32767 bytes.

  • The len is less than 0.

  • The pos is less than or equal to 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;

Parameters

Table 13-22 REVERSE Function Parameters

Parameter Description

r

RAW value to reverse

Return Value

RAW value that is the reverse of the input value

Exceptions

VALUE_ERROR occurs if the input value 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;

Parameters

Table 13-23 SUBSTR Function Parameters

Parameter Description

r

RAW byte-string from which the substring is extracted

pos

Byte position at which to begin extraction, either counting forward from the beginning of the input byte-string (positive value) or backward from the end (negative value)

len

Number of bytes, beginning at pos and proceeding toward the end of the byte string, to extract (default: to the end of the RAW byte-string)

Return Value

RAW substring beginning at position pos for len bytes, or NULL if the input is NULL

Usage Notes

If pos is positive, SUBSTR counts from the beginning of the RAW byte-string to find the first byte. If pos is negative, SUBSTR counts backward from the end of the RAW byte-string. 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 the RAW byte-string.

Exceptions

VALUE_ERROR occurs under any of the following circumstances:

  • The pos equals 0 or is greater than the length of r.

  • The len is less than or equal to 0.

  • The len is greater than (length of r) minus (pos-1).

Examples

Example 1: This example, run in ttIsql, counts backward 15 bytes from the end of the input RAW value 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, run in ttIsql, has the same input RAW value 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 and translate from in the input bytes, 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.

Parameters

Table 13-24 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 the source byte-string

Where found, they are translated in the result.

to_set

RAW byte-codes to translate to

Where a from_set byte is found in the source byte-string, it is translated in the result to the corresponding to_set byte, as applicable.

Return Value

RAW value with the 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 value 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 the source byte-string 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

VALUE_ERROR occurs if the source byte string, from_set, or to_set is NULL or zero-length.

Examples

Example 1: In this example, run in ttIsql, from_set is x'12AA34' and to_set is x'CD'. Wherever '12' appears in the input RAW value it is replaced by 'CD' in the result. Wherever 'AA' or '34' appears in the input RAW value, because there are no corresponding bytes in to_set, those bytes are not 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.

The inputs and output are presented in the following, 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, run in ttIsql, the from_set is x'12AA12' and the to_set is x'CDABEF'. Wherever '12' appears in the input RAW it is replaced by 'CD' in the result. Wherever 'AA' appears in the input it is 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 in the TRANSLITERATE section. 

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 and convert from in the input bytes, 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 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.

Parameters

Table 13-25 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 the source byte-string, 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 the source byte-string

Where found, they are converted in the result. The default is x'00' through x'FF', which results in all bytes in the source byte string being converted in the result.

pad

A "padding" byte used as the conversion value for any byte in the source byte-string for which there is a matching byte in from_set that does not have a corresponding byte in to_set (default x'00')

Return Value

RAW value with the 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 value 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 the source byte-string 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 differ in functionality only when to_set has fewer bytes than from_set.

Exceptions

VALUE_ERROR occurs if the source byte-string is NULL or zero-length.

Examples

Example 1: In this example, run in ttIsql, the from_set is x'12AA34' and the to_set is x'CD'. Wherever '12' appears in the input RAW value it is replaced by 'CD' in the result. Wherever 'AA' or '34' appears in the input RAW value, because there are no corresponding bytes in to_set, those bytes are 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.

The inputs and output are presented in the following, 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 example, run in ttIsql, 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: In this example, run in ttIsql, the from_set is x'12AA12' and the to_set is x'CDABEF'. Wherever '12' appears in the input RAW value it is replaced by 'CD' in the result. Wherever 'AA' appears in the input it is 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.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 in the TRANSLATE section. 

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

Example 4: In this example, run in ttIsql, 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, 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;

Parameters

Table 13-26 XRANGE Function Parameters

Parameters Description

start_byte

Beginning byte-code value for resulting sequence (default x'00')

end_byte

Ending byte-code value for resulting sequence (default x'FF')

Return Value

RAW value containing the succession of one-byte encodings

Examples

The following three examples, run in ttIsql, 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.