13 UTL_RAW

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

Summary of UTL_RAW subprograms

---

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

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

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

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

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

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

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

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

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

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

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

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

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.

Syntax

UTL_RAW.CAST_TO_NVARCHAR2 (
   r IN RAW) 
RETURN NVARCHAR2;

Parameters

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

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.

Syntax

UTL_RAW.CAST_TO_VARCHAR2 (
   r IN RAW) 
RETURN VARCHAR2;

Parameters

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

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

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

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

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

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

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

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;

Parameters

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.

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;

Parameters

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.

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

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.